DAX Determine most popular area for user orders

[malangj]

I have the tables below:

Tables & Relations

I'm trying to calculate a users' most popular ReastaurantArea, based on their last 5 orders. 

I'm here. It feels like I'm close, but it's been hours of no luck. I might be making this way to complicated.

User's Favourite order area =
          VAR last5areas = SELECTCOLUMNS( TOPN(5,SELECTCOLUMNS(Orders,"OrderID",Orders[OrderID],"Area",RELATED(Restaurants[RestaurantArea])),[OrderID],DESC), "Area",[Area])

RETURN
     FIRSTNONBLANK(
          TOPN (1,
             DISTINCT(last5areas),
                  RANKX( last5areas , COUNTAX(last5areas,[Area])),ASC),1)

pbix file here: https://transfernow.net/969o10c22bfm

[v-cherch-msft · Accepted solution]

Hi @malangj

Then you may use RANKX Function to create a rank column first. Second,modify the measure OrderCount as below:

1. Create a column for Orders

OrderRank =
RANKX (
    FILTER ( Orders, Orders[UserID] = EARLIER ( Orders[UserID] ) ),
    Orders[OrderID],
    ,
    DESC
)

2. Modify measure OrderCount

OrderCount =
COUNTX (
    FILTER ( Orders, [OrderRank] <= 5 ),
    RELATED ( Restaurants[RestaurantArea] )
)

Regards,

Cherie

[malangj]

@v-cherch-msft Thanks a lot Cherie, that works 100%.

Is there any way to do it without a calculated column? That way I can filter my Orders table and the results will update correctly.

[malangj]

With the help of Rob Collies blog, I'm progressing and can now calculate the overall favourite order area for each user:

OrderCount = counta(Orders[OrderID])

User's Favourite order area2 = IF(NOT(ISBLANK([OrderCount])),
IFERROR(
CALCULATE(
VALUES(Restaurants[RestaurantArea]),
TOPN(1,
VALUES(Restaurants[RestaurantArea]),
[OrderCount])),
"Multiple Products"))

Now for the next step, doing this for the last 5 orders