CODEWITHSUNDEEP
powerbidax
Leah Stortvedt
Leah Stortvedt

Reputation: 1

Filtering Calculate in DAX with 2 tables

I am trying to get the below DAX calculation to work. Right now I'm getting an error on the last filter because it references a different table. What is the best way to work around this?

CALCULATE(SUM(transactions[AdvancedMD Units]), transactions[Billing Code] IN {"97153", "97154", "97155", "97156", "97151", "97152"}, transactions[date_of_service] >= 'auth records'[start_date])

I tried to use filtering on the card itself in PowerBI, but since each client has a different start date, that didn't work.

A sample would be LS has 4 units of 97151 on 05/26/2024, 6 units of 97153 on 6/03/2024. The authorization started on 6/01/2024. I only want the 6 units after that date to show up in the Sum.

Upvotes: 0

Views: 55

Answers (1)

Nick A
Nick A

Reputation: 1124

Try using this measure:

SumUnits = 
 VAR currentClient = MAXX(Transactions, [ClientID]) //Gets the current Client's ID
 RETURN CALCULATE(
    SUM(Transactions[AdvancedMD Units]), //Sums the Units
    FILTER(Transactions, 
        VAR clientStartDate = LOOKUPVALUE('Auth Records'[Start_Date], 'Auth Records'[ClientID], currentClient) //No relationship between Auth Records and Transactions, LOOKUPVALUE is used instead of RELATE
        RETURN Transactions[Date_of_Service] >= clientStartDate && //Compares the Client Start Date to Transaction Start Date
        Transactions[Billing Code] IN {"97153", "97154", "97155", "97156", "97151", "97152"} //Original measure provided
        )
    )

The measure takes the client id from the Transaction table and searches the Auth Records for the Start Date using LOOKUPVALUE(). Then uses that value to filter the Transaction Date_of_Service column.

Sample Data used:

Auth Records

ClientID Start_Date
LS 2024-06-01
AB 2024-05-15

Transaction

TransactionID ClientID Billing Code AdvancedMD Units Date_of_Service
1 LS 97151 4 2024-05-26
2 LS 97153 6 2024-06-03
3 LS 97154 5 2024-06-15
4 AB 97155 3 2024-06-20

Upvotes: 0

Related Questions