Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Order | Restaurant | Meal | Item |
1 | A | Breakfast | Eggs |
2 | A | Breakfast | Eggs |
3 | A | Breakfast | Pancakes |
4 | A | Dinner | Steak |
5 | A | Dinner | Salad |
6 | A | Dinner | Steak |
7 | A | Lunch | Salad |
8 | A | Lunch | Salmon |
9 | B | Breakfast | Toast |
10 | B | Breakfast | Toast |
11 | B | Breakfast | Juice |
12 | B | Lunch | Salad |
13 | B | Lunch | Salad |
I'm trying to find the most frequently ordered item on the menu for a specific meal for a restaurant(eg. For restaurant A, meal Breakfast, Eggs is the most frequently ordered item)
I've been trying to use a mix of different posts such as this one and the following:
Hey @BriefStop ,
here you will find a pbix that contains a solution (have a lookt at the page Restaurants, Meals, and Items): https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EYqyDRiNiQpMkiZWZI5V5ocB5Bx0S...
I created this DAX measure:
most Frequent Item =
// IF( HASONEVALUE( 'Table (2)'[Restaurant] ) && HASONEVALUE('Table (2)'[Meal] )
// ,
var currentRestaurant = SELECTEDVALUE( 'Table (2)'[Restaurant] )
var currentMeal = SELECTEDVALUE( 'Table (2)'[Meal] )
var t =
SUMMARIZE(
ALLSELECTED( 'Table (2)' )
, 'Table (2)'[Restaurant]
, 'Table (2)'[Meal]
, 'Table (2)'[Item]
)
return
CONCATENATEX(
TOPN(
1
, ADDCOLUMNS(
FILTER(
t
, [Restaurant] = currentRestaurant && [Meal] = currentMeal
)
, "noOfItems"
, var r = [Restaurant]
var m = [Meal]
return
CALCULATE(
COUNT( 'Table (2)'[Item] )
, 'Table (2)'[Restaurant] = r
, 'Table (2)'[Meal] = m
)
)
, [noOfItems]
, DESC
, [Meal]
, ASC
)
, [Item]
, ", "
, [Item]
)
This allows to create this table visual:
Hopefully, this provides what you are looking for.
Regards,
Tom
User | Count |
---|---|
144 | |
71 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |