cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Find a customers next booking

Hi,

I've got something I'm struggling with, what I'm wanting to find out, for each record is where a guest booked to go next, so we can make some predictions, based on where the majority of guests book, i.e. for all the guests who've booked to go to France, 30% then make their next booking to Spain, etc

I have data in a table with a unique ID for each guest, the date of their booking, and the country they went to & then I've added the Expected Outcome. So Guest 1 first booked to France and their next booking was to the USA

 Customer ID Book Date Country Expected Outcome 1 01/01/2000 France USA 2 01/02/2000 Germany Portugal 3 01/03/2000 Spain Null 4 01/04/2000 Italy Greece 1 01/05/2000 USA Null 2 01/06/2000 Portugal Null 5 01/07/2000 Italy Null 4 01/08/2000 Greece Null

Any suggestions on how to best achieve this would be gratefully received, the file is already large so hoping for an efficient Dax formula to do the heavy lifting as opposed to a calculated column, which the file is struggling to implement.

Thanks

Laurence

1 ACCEPTED SOLUTION
Community Support

According the data you provided,test to create the below column:

``rank = RANKX(FILTER(data,data[ContactId]=EARLIER(data[ContactId])),format(data[BookDate],"YYYYMMDD")+data[BookId],,ASC,Dense)``
``return = CALCULATE(MAX(data[MetaCountry]),FILTER(ALL(data),data[ContactId]=EARLIER(data[ContactId])&&data[rank]=EARLIER(data[rank])+1))``

Output result:

Best Regards

Lucien

9 REPLIES 9
Community Support

According the data you provided,test to create the below column:

``rank = RANKX(FILTER(data,data[ContactId]=EARLIER(data[ContactId])),format(data[BookDate],"YYYYMMDD")+data[BookId],,ASC,Dense)``
``return = CALCULATE(MAX(data[MetaCountry]),FILTER(ALL(data),data[ContactId]=EARLIER(data[ContactId])&&data[rank]=EARLIER(data[rank])+1))``

Output result:

Best Regards

Lucien

Frequent Visitor

I've used a combination of the book date & book id to create a unique id. I'm still getting the same error. Here is the dataset I've used:

Kitty

Solution Sage

Hi @LaurenceSD ,

Please try this measure in DAX

Next Country =

var _maxdate =

CALCULATE(
MAX(TravelList[Book Date]),
ALLEXCEPT(TravelList, TravelList[Customer ID])
)

var _country =
CALCULATE(
MAX(TravelList[Country]),
FILTER(AllEXCEPT(TravelList,TravelList[Customer ID]),
TravelList[Book Date] = _maxdate)
)

Return
if(SELECTEDVALUE(TravelList[Country]) = _country, "null", _country)

This gives us the desired result

Kind regards,

Rohit

Frequent Visitor

Hi,

I made an addition to the dataset in your original file which has resulted in the same error. I think the issue is that a guest could technically make 2 bookings with the same Book Date.

Unfortunately, I seem to be unable to upload a .pbix file on this forum, so here is the entry and hopefully you can replicate the error:
(Customer ID: 1 / Book Date: 05/01/2000 / Country: Tunisia)

Kitty

Super User

Hi,

Thank you for the clarification.

if the customerID: 1 booked two countries (USA, Tunisia) on the same date (5th Jan. 2000), which country do you want to show? And what is the logic behind the selection of the country if there are two or more than two countries for the same customerID on the same booking date? Once I can understand the logic, I can try to come up with fixed solution. I purposely created those to show error if there are more than two countries, but sorry that I did not mentioned.

For your information, one of the ways to show error message if there are two or more countries is that, create one-column-one-row TABLE by using VALUES function. And then, if the outcome is not one-row table, the measure shows error.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Hi,

Thank you for this! So there is a BookId column which would be the decider for when the book dates are the same. I've had a go at resolving this today, but still no success. Here is an example:

So the logic needs to be that for ContactId 616428, since there are duplicate Book Dates, the BookId 4619737 should come before 4620136. Hopefully that helps. Thank you in advance.

Super User

Hi,

Please check the below attached pbix file.

I created one for creating a measure and another for creating a calculated column.

``````Next booking country measure: =
VAR currentid =
MAX ( Data[Customer ID] )
VAR currentdate =
MAX ( Data[Book Date] )
VAR nextbookdate =
CALCULATE (
MIN ( Data[Book Date] ),
FILTER (
ALL ( Data ),
Data[Customer ID] = currentid
&& Data[Book Date] > currentdate
)
)
VAR nextbookcountry =
CALCULATETABLE (
VALUES ( Data[Country] ),
FILTER (
ALL ( Data ),
Data[Customer ID] = currentid
&& Data[Book Date] = nextbookdate
)
)
RETURN
IF (
HASONEVALUE ( Data[Customer ID] ),
IF ( ISBLANK ( nextbookcountry ), "Null", nextbookcountry )
)``````

``````Next Booking Country CC =
VAR nextbookingdate =
MINX (
FILTER (
Data,
Data[Customer ID] = EARLIER ( Data[Customer ID] )
&& Data[Book Date] > EARLIER ( Data[Book Date] )
),
Data[Book Date]
)
VAR nextcountry =
SUMMARIZE (
FILTER (
Data,
Data[Customer ID] = EARLIER ( Data[Customer ID] )
&& Data[Book Date] = nextbookingdate
),
Data[Country]
)
RETURN
IF ( ISBLANK ( nextcountry ), "Null", nextcountry )``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Hi @Jihwan_Kim ,

Thank you for looking into this for us! I've tried using the measure you suggested, but I'm getting the following error:

If you have any ideas as to why this is happening, that would be very helpful.

Thanks,

Kitty

Super User

Hi,

Please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution. If the measure works in my sample data model but it does not work in your data model, I have no idea but I need to check what is the difference between mine and yours.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.