cancel
Showing results for
Did you mean:
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

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

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

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 28 members 1,074 guests
Recent signins: