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

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

Accepted Solutions
Super User
Super User

Re: Finding the most recent event attended

@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

Re: Finding the most recent event attended

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!

6 REPLIES 6
Drors Member
Member

Re: Finding the most recent event attended

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

Super User
Super User

Re: Finding the most recent event attended

@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

Super User
Super User

Re: Finding the most recent event attended

Super User
Super User

Re: Finding the most recent event attended

Re: Finding the most recent event attended

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!

Community Support Team
Community Support Team

Re: Finding the most recent event attended

@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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 28 members 1,074 guests
Please welcome our newest community members: