Check if an individual had a previous event

[Anonymous]

I have a data set that shows the bookings by individuals over a period of several years. I need to check for each booking whether that individual has had a previous booking. My dax skills are still at the middle of the learning journey, so after quite a bit of fiddling and googling I've admitted this has me stumped.

My table has a column for individual identifiers (email) and a column for the start date/time of the booking.

[amitchandak]

@Anonymous , create a Rank date for person id in ascending order .nowyou know all the booking order

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns -- this one should help in this case
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Anonymous]

Thank-you @amitchandak - I've got most of the way there I think!

I followed your guide and ended up with the following:

NumberOfBookings =
RANKX (
FILTER('Table', 'Table'[Email] = EARLIER('Table'[Email])),
'Table'[StartDate],,ASC)

Can you suggest how I would use this to end up with a Y/N for each row for whether that individual has a prior booking?

[Pragati11]

Hi @Anonymous ,

Just create a column as follows:

I have considered a sample data with 2 columns: Person ID and Date.

In my case I have considered an identifier as an ID value. In your case you can replace it with your Email ID field and your Date column with your booking date column.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Wohoo! That's got me most of the way there! It's recognising the ones that have more than one exam - is there a way to have it return 'no previous booking' for that individuals first one?

[Pragati11]

Hi @Anonymous ,

Sorry didn't understand what you are trying to say?

Thanks,

Pragati

[Anonymous]

Thanks @Pragati11 - I'll clarify.

In the example table you gave there are two bookings for PersonID = 1 (one on the 1st of January and one on the 2nd of March). Using your formula I can recognise that Person 1 had more than one booking, but not which event was the first. So I'd like it to return a value of "No Previous Booking" for the booking on the 1st of January for that person, but "Previous Booking Exists" for the booking on the 2nd of March. Does that make sense?

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Just modify the DAX as follows and you will get your result:

Is Previous Booking = IF(CALCULATE(COUNT(CustomerID[Date]), FILTER(ALLSELECTED(CustomerID), CustomerID[Person ID] = EARLIER(CustomerID[Person ID]) && CustomerID[Date] <= EARLIER(CustomerID[Date]))) > 1, "Previous Booking Exists", "No Previous Booking")

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[JamesLindsay101]

hey Pragati, I'm working on manipluating the above solution so it says duplicate event for all but the original/1st row of the booking, do you have any suggestions on how I'd update this formula?

I understand this may not be possible so feel free to suggest an alternative route.

Many thanks!

[Pragati11]

HI @JamesLindsay101 ,

You will have to provide me some context around with example on what you are trying to achieve here.

[JamesLindsay101]

Hi Pragati,

I have a table of absences, where each row contains the employee ID, department, team etc., then the absence start date, and absence end date. Each absence booking also has a unique external code. The tricky part comes in when the absence is longer than one day, our system will replicate said absence row for each day of the absence.

e.g. If an absence booking for an individual goes from 07/12/22 - 09/12/22, the absence row will have two other duplicate rows as the absence is 3 days long.

Currently I'm cleaning the data by using the remove duplicates rows (using the external code for each individual booking) function in excel, then uploading into power BI.

My aim though is to develop a calculated column which identifies any duplicate rows, however doesn't identify the original/1st copy of the row

[Anonymous]

That's fantastic @Pragati11 - thank you!!