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.
Im having difficulty creating a specific report, that shows the most recent event a contact attended.
The model is very simple, contacts table, events table and AttendedEvents table that links them.
In the simplified example (see attached pbix), the final table should have 7 rows, one for each contact, showing the date of the most recent event they attended.
Appreciate some pointers in working this out. its starting to consume a lot of time now and im not making any progress.
Thank you for any help.
Solved! Go to Solution.
USe this MEASURE in EVENTATTENDANCE table to get the LatestEvent Name
LatestEventName = VAR LatestEventDate = MAX ( EventAttendance[eventDate] ) VAR LatestEventID = CALCULATE ( FIRSTNONBLANK ( EventAttendance[EventID], 1 ), Event[EventDate] = LatestEventDate ) RETURN CALCULATE ( FIRSTNONBLANK ( Event[EventName], 1 ), Event[EventID] = LatestEventID )
For lastest date, simply drag the EVENTDATE from EVENTATTENDANCE table and choose LATEST
thanks guys! massive help.
I ended up solving it in a round about way, but i may use one of the above ways if they end up more performant.
My solution was:
new calc column to find the rank of each row in the fact table (eventAttaendance)
rowRank = countrows( filter(EventAttendance, EventAttendance[eventDate] >= EARLIER(EventAttendance[eventDate]) && EventAttendance[ContactID] = EARLIER(EventAttendance[ContactID])))
Using this, i then used a simple measure:
maxDate = calculate(max(EventAttendance[eventDate]), filter(EventAttendance, EventAttendance[rowRank]=1))
II'll test out both solutions on the larger dataset to see performance.
Thanks for the help all!
You can try group by contact and in the aggregation use MAX date
USe this MEASURE in EVENTATTENDANCE table to get the LatestEvent Name
LatestEventName = VAR LatestEventDate = MAX ( EventAttendance[eventDate] ) VAR LatestEventID = CALCULATE ( FIRSTNONBLANK ( EventAttendance[EventID], 1 ), Event[EventDate] = LatestEventDate ) RETURN CALCULATE ( FIRSTNONBLANK ( Event[EventName], 1 ), Event[EventID] = LatestEventID )
For lastest date, simply drag the EVENTDATE from EVENTATTENDANCE table and choose LATEST
thanks guys! massive help.
I ended up solving it in a round about way, but i may use one of the above ways if they end up more performant.
My solution was:
new calc column to find the rank of each row in the fact table (eventAttaendance)
rowRank = countrows( filter(EventAttendance, EventAttendance[eventDate] >= EARLIER(EventAttendance[eventDate]) && EventAttendance[ContactID] = EARLIER(EventAttendance[ContactID])))
Using this, i then used a simple measure:
maxDate = calculate(max(EventAttendance[eventDate]), filter(EventAttendance, EventAttendance[rowRank]=1))
II'll test out both solutions on the larger dataset to see performance.
Thanks for the help all!
By the way, you may help accept the useful solution. Your contribution is highly appreciated.
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 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |