Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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)
Hi @sv12
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |