cancel
Showing results for
Did you mean:
Highlighted
Member

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

2 ACCEPTED SOLUTIONS

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

Member

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

## Re: Finding the most recent event attended

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

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

Member

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

## Re: Finding the most recent event attended

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.

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 50 members 980 guests
Recent signins: