Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Check if an individual had a previous event

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.

1 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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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
Super User
Super User

Hi @Anonymous ,

 

Just create a column as follows:

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

 

prevBook.png

 

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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?

Hi @Anonymous ,

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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?

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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!

HI @JamesLindsay101 ,

 

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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
Not applicable

That's fantastic @Pragati11 - thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.