Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wilson_smyth
Post Patron
Post Patron

Finding the most recent event attended

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

2 ACCEPTED SOLUTIONS

@wilson_smyth

 

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


Regards
Zubair

Please try my custom visuals

View solution in original post

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!

View solution in original post

6 REPLIES 6
Drors
Resolver III
Resolver III

You can try group by contact and in the aggregation use MAX date

@wilson_smyth

 

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


Regards
Zubair

Please try my custom visuals

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!

@wilson_smyth,

 

By the way, you may help accept the useful solution. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.