Forum Discussion
DAX Formula for Cross Table Filtering / Date Logic
I am trying to figure out how to write a DAX solution that requires 2 tables being filtering by Date and Status, respectively.
I have 2 tables that are too big to Merge within the Query Editor, and I need a dynamic solution to apply to historic data.
I am looking to find the most recent transaction line per customer in the prevous 90 days in one table, and find the relative status of that transaction from the other table, and only include certain status'.
The tables are linked by a SALEID = OBJID
Pass Table
Sale Table
In the below example, I am looking for a DISTINCTCOUNT of CUSTOMER where the CREATED is in the previous 90 days and the status is 1 OR 2.
I would expect the below test data to return 2, Customer B & C (SALEIDs 1 & 3)
Thanks,
J
Yeah. You might want to define it first as a variable though.
Count90 = VAR DynamicDate = [DynamicDateMeasure] RETURN CALCULATE ( DISTINCTCOUNT ( 'Pass'[Customer] ), Sale[Created] > DynamicDate - 90, 'Pass'[Status] IN { 1, 2 } )How about this then?
CountLast90 = VAR DynamicDate = TODAY () - 90 VAR StatusVals = { 1, 2 } VAR AddCreatedCol = ADDCOLUMNS ( 'Pass', "Created", LOOKUPVALUE ( Sale[Created], Sale[ObjID], 'Pass'[SaleID] ) ) VAR PassFiltered = FILTER ( AddCreatedCol, [Created] > DynamicDate ) VAR AddLastCreated = ADDCOLUMNS ( PassFiltered, "LastCreated", MAXX ( FILTER ( PassFiltered, [Customer] = EARLIER ( [Customer] ) ), [Created] ) ) RETURN COUNTROWS ( SUMMARIZE ( FILTER ( AddLastCreated, [Created] = [LastCreated] && [Status] IN StatusVals ), [Customer] ) )
13 Replies
How about this?
Count90 = CALCULATE ( DISTINCTCOUNT ( 'Pass'[Customer] ), Sale[Created] > TODAY () - 90, 'Pass'[Status] IN { 1, 2 } )Thanks for getting back to me Alexis, you're very good, if I wanted this to work for a dynamic date, would I just put my [Date] field where "TODAY()" is?
Yeah. You might want to define it first as a variable though.
Count90 = VAR DynamicDate = [DynamicDateMeasure] RETURN CALCULATE ( DISTINCTCOUNT ( 'Pass'[Customer] ), Sale[Created] > DynamicDate - 90, 'Pass'[Status] IN { 1, 2 } )
Can you give an example that demonstrates what you want the result to be? Your provided samples don't have any matches and only include a single distinct created date and status ID.
Hey Alexis,
Apologies, I'm looking for;
A DISTINCTCOUNT of customers who's latest dated transaction line within the previous 90 days has a particular status.
Thanks
You're much more likely to get an answer if you provide useful examples to work with.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
