Finding the most recent event attended

[wilson_smyth]

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

[Zubair_Muhammad · Accepted solution]

@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

[Drors]

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

[Zubair_Muhammad · Accepted solution]

@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

[Zubair_Muhammad]

@wilson_smyth

[Zubair_Muhammad]

@wilson_smyth

Your revised file attached here

[wilson_smyth · Accepted solution]

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!

[v-chuncz-msft]

@wilson_smyth,

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