Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |