Forum Discussion
DAX Help
Hi, I have 2 tables as shown below: I am looking to calculate a measure for number of events attended by each contact before the 1st purchase made, i.e distinctcount(eventname) where [First...
Assuming the table on the left is Table1 and the table on the right is Table2...
This measure belongs in Table1. You will use the ContactID from Table1 in your visual.
Try this:
# Events Before First Purchase = // Get the current ContactID based on the filter context VAR Current_ContactID = SELECTEDVALUE(Table1[ContactID]) // Get the FirstPurchaseDate for that ContactID VAR PurchaseDate = CALCULATE( MAX(Table1[Purchase Date]) ,Table1[ContactID] = Current_Contact) // Get ALL the records in Table2 for the given ContactID // that happened before the purchase date VAR EventsAttended_ALL = FILTER( Table2 ,Table2[ContactID] = Current_ContactID && Table2[EventDate] < PurchaseDate ) //Get the unique list of Table2[EventName] based on EventsAttended_ALL VAR EventsAttended_DISTINCT = CALCULATETABLE( VALUES(Table2[EventName]) ,EventsAttended_ALL ) RETURN COUNTROWS(EventsAttended_DISTINCT)
Assuming the table on the left is Table1 and the table on the right is Table2...
This measure belongs in Table1. You will use the ContactID from Table1 in your visual.
Try this:
# Events Before First Purchase = // Get the current ContactID based on the filter context VAR Current_ContactID = SELECTEDVALUE(Table1[ContactID]) // Get the FirstPurchaseDate for that ContactID VAR PurchaseDate = CALCULATE( MAX(Table1[Purchase Date]) ,Table1[ContactID] = Current_Contact) // Get ALL the records in Table2 for the given ContactID // that happened before the purchase date VAR EventsAttended_ALL = FILTER( Table2 ,Table2[ContactID] = Current_ContactID && Table2[EventDate] < PurchaseDate ) //Get the unique list of Table2[EventName] based on EventsAttended_ALL VAR EventsAttended_DISTINCT = CALCULATETABLE( VALUES(Table2[EventName]) ,EventsAttended_ALL ) RETURN COUNTROWS(EventsAttended_DISTINCT)