cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

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


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
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


View solution in original post

@wilson_smyth

 

 

5002.png

Regards,
Zubair


@wilson_smyth

 

Your revised file attached here

Regards,
Zubair


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

@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
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors