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 [FirstPurchaseDate] > [Event date] at the row level context.
I tried using the "Calculate" function, but I am not able to use the dates from 2 different tables in the filter section.
I am new to writing script in DAX. I greatly appreciate any help.
Thanks
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)
3 Replies
Hi Anonymous
Try this:
1. Create a 1-many unidirectional relationship between PurchasesTable and EventsTable through ContactID.
2. Place PurchasesTable[ContactID] in the rows of a matrix visual.
3. Create this measure and place it in values of the matrix
NumEvents = CALCULATE ( DISTINCTCOUNT ( EventsTable[EventName] ), FILTER ( ALL ( EventsTable[EventDate] ), EventsTable[EventDate] < SELECTEDVALUE ( PurchasesTable[FirstPurchaseDate] ) ) )AlB Thanks for the response.
I have tried the suggested solution and the following error is being thrown:
Any thoughts on that?
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)
