Filtering a table by the same field multiple times

[ttpcap]

I have a table for Restaurants that is structured as follows:

Column 1: Restaurant Name - there 80,000 restaurants all with unique names

Column 2: Menu #: I've tracked the first 10 menu items in the order in which they appear on the restaurant's menu, numbered 1-10

Column 3: Menu Item - I have a list of 300 different menu items, listed in the same row as the Menu # in which they appear

Column 4: Item Type - there are about 10 different types of menu items (Sandwhich, Salad, Side, etc)

Column 5: Profit/Loss - each restaurant is either profitable (P) or loss making (L)

A sample of the table looks like this:

Restaurant Name	Menu #	Menu Item	Item Type	Profit/Loss
A	1	Pizza	Main	L
A	2	Italian Sub	Sandwich	L
A	3	Hamburger	Sandwich	L
A	4	Cheeseburger	Sandwich	L
A	5	Chicken Salad	Salad	L
A	6	Beef Soup	Soup	L
A	7	Hot Dog	Sandwich	L
A	8	Fries	Side	L
A	9	Chips	Side	L
A	10	Sodas	Beverage	L
A	1	Chicken Fingers	Man	P
B	2	Italian Sub	Sandwich	P
B	3	Mixed Salad	Salad	P
B	4	Fruit	Side	P
B	5	Hamburger	Sandwich	P
B	6	Hot Dog	Sandwich	P
B	7	Pizza	Main	P
B	8	Salmon	Main	P
B	9	Tuna Salad	Salad	P
B	10	Falafel	Main	P

I can do a simple filter to by Profit/Loss to be able to count the number of restaurants that offer a single menu item (e.g. Pizza) and filter to see if they are profitable or not, thereby giving me a % of restaurants offering Pizza that generate a Profit.  But I can't figure out how to sort the table by multiple menu items at the same time. e.g. How do I build a simple filter or slicer to show the total number of restaurants that offer both Pizza AND Salad and then then filter that by whether they are profitable or not? What about Pizza AND Salad AND Hot Dogs? I would like to be able to do this dynamically so that I can rapidly pick and choose the combinations that I want to check Profitability. Secondarily, I would like to filter the data by Menu Type and Menu #, i.e. what % of Restaurants are profitable when they offer a Sandwhich as Menu #1 and a Salad as Menu #2 vs. those that offer a Main as Menu #1 and a Main as Menu #2, etc etc. 

Is there any simple way to do this without using a bunch of static DAX expressions by having a dyamic filter or slicer on my page?

Thanks!

[v-juanli-msft · Accepted solution]

Hi @ttpcap 

1.

build a simple filter or slicer to show the total number of restaurants that offer both Pizza AND Salad and then then filter that by whether they are profitable or not?

->

Create new tables and create relationships

Item = VALUES('Table'[Menu Item])

Profit/Loss = VALUES('Table'[Profit/Loss])

2.

what % of Restaurants are profitable when they offer a Sandwhich as Menu #1 and a Salad as Menu #2 vs. those that offer a Main as Menu #1 and a Main as Menu #2, etc etc. 

for example:  % of  Restaurants are profitable when they offer a Main as Menu #1 and a Sandwhich as Menu #2

Create two new tables, which doesn't connect to any other table,

Add columns from the two tables into slicers,

then create measures

selected1 = IF(SELECTEDVALUE('Type1'[Item Type])=MAX('Table'[Item Type]),MAX('Table'[Menu #]))

selected2 = IF(SELECTEDVALUE('Type2'[Item Type])=MAX('Table'[Item Type]),MAX('Table'[Menu #]))

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[estaurant Name] ),
    FILTER (
        'Table',
        (
            (
                'Table'[Item Type] = SELECTEDVALUE ( 'Type1'[Item Type] )
                    && [selected1] = 1
            )
                || (
                    'Table'[Item Type] = SELECTEDVALUE ( 'Type2'[Item Type] )
                        && [selected2] = 2
                )
        )
            && 'Table'[Profit/Loss] = "P"
    )
)
    / CALCULATE ( DISTINCTCOUNT ( 'Table'[estaurant Name] ), ALLSELECTED ( 'Table' ) )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ttpcap]

Just to clarify, the Profit (P) or Loss (L) applies only to the Restaurant. The table I printed had a typo for the first Menu Item for Restauant B. i.e. a Restauarant is either P or L for all menu #s, menu items, and menu types at that particular restaurant.