cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Filtering a table by the same field multiple times

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 NameMenu #Menu ItemItem TypeProfit/Loss
A1PizzaMainL
A2Italian SubSandwichL
A3HamburgerSandwichL
A4CheeseburgerSandwichL
A5Chicken SaladSaladL
A6Beef SoupSoupL
A7Hot DogSandwichL
A8FriesSideL
A9ChipsSideL
A10SodasBeverageL
A1Chicken FingersManP
B2Italian SubSandwichP
B3Mixed SaladSaladP
B4FruitSideP
B5HamburgerSandwichP
B6Hot DogSandwichP
B7PizzaMainP
B8SalmonMainP
B9Tuna SaladSaladP
B10FalafelMainP

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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])

 

Capture7.JPG

Capture8.JPG

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

Capture10.JPG

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.

View solution in original post

2 REPLIES 2
Highlighted
New Member

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. 

Highlighted
Community Support
Community Support

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])

 

Capture7.JPG

Capture8.JPG

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

Capture10.JPG

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors