Unique Values of Subset of Data using slicer

[razraz2020]

Looking to get unique values of sliced data compared to the entire data set.  Example below, 2 columns Restaurant & Menu.  If I had a slicer on Restaurant, I'd want to know what are the unique items.  So if McDonald's was selected, I would only want to see 'Fillet of Fish'.   If Wendy's was selected, I would see 'Chicken Sandwich' & 'Salad'.  How do accomplish what seems to be a very simple task.  

Restaurant	Menu
McDonald's	Cheesburger
McDonald's	Fillet of Fish
McDonald's	Hamburger
Burger King	Cheesburger
Burger King	Hamburger
Wendy's	Hamburger
Wendy's	Cheesburger
Wendy's	Chicken Sandwich
Wendy's	Salad

[camargos88]

Hi @razraz2020 ,

Check this file: Download PBIX 

I just created a calculated column to count the distinct values in the table and filtered the table visual.

Ricardo

[razraz2020]

Hi Ricardo,  @camargos88 

Thank you for the quick reply.  This solution answers the question as I phrased it, so kudos to you.  What I left out of my poorly written and over simplified example was that a Restaurant could have duplicate menu items.  Assume McDonalds had 'Fillet of Fish' multiple times or Wendy's had duplicate 'Chicken Sandwich'.   Although those are not unique to the Restaurant, they are unique to the rest of the data and would want the same results that you posted.  Thoughts?

[camargos88]

Hi @razraz2020 ,

If you have duplicates for the same restaurante, we can remove it, does it work ?

If yes, I've updated the file.

Ricardo

[razraz2020]

@camargos88   I grabbed the file again to see what you had done but doesn't appear to be updated.   I might not follow the logic and will probably make more sense when I see what you did to the pbix but wouldn't removing duplicates make everything unique?

[camargos88]

@razraz2020 ,

I just saved it again.

No, we can remove duplicates based on restaurant and menu...so we will have just 1 combination.

Ricardo

[razraz2020]

@camargos88   I still don't see this in the pbix, but I understand what you are saying and that would work.  Could I expand on this   since this is so simple (for you :-).  If there was a third column 'Person' who ate the menu item, how could I show the unique results.  Example Data:

Restaurant	Menu	Person
McDonald's	Cheesburger	Mike
McDonald's	Fillet of Fish	Rob
McDonald's	Fillet of Fish	Mike
McDonald's	Hamburger	Jane
Burger King	Cheesburger	Bill
Burger King	Hamburger	Joe
Wendy's	Hamburger	Sally
Wendy's	Cheesburger	Nancy
Wendy's	Chicken Sandwich	Fran
Wendy's	Chicken Sandwich	Jim
Wendy's	Salad	Jack

If I select McDonald's I would want the unique Menu items of the dataset 

McDonald's	Fillet of Fish	Rob
McDonald's	Fillet of Fish	Mike

or if I select Wendy's:

Wendy's	Chicken Sandwich	Fran
Wendy's	Chicken Sandwich	Jim
Wendy's	Salad	Jack

Thanks for dealing with my nonsense!

[camargos88 · Accepted solution]

@razraz2020 ,

I've updated the file: Download PBIX 

Ricardo

[camargos88]

@razraz2020 ,

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[az38]

hi @razraz2020 

how do you plan to define what exactly Menu should be displayed for each restaraunt?

Why Fillet of Fish, not cheese for Mc?