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.
Link to sample pbix
Go to Solution.
USe this MEASURE in EVENTATTENDANCE table to get the LatestEvent Name
VAR LatestEventDate =
MAX ( EventAttendance[eventDate] )
VAR LatestEventID =
FIRSTNONBLANK ( EventAttendance[EventID], 1 ),
Event[EventDate] = LatestEventDate
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
Your revised file attached here
By the way, you may help accept the useful solution. Your contribution is highly appreciated.
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.