Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PANDAmonium
Resolver III
Resolver III

Slicer Selections Functioning as AND Operator

Hi All,

 

I was wondering if there's a way to make slicer selections function with AND operators instead of OR operators, maybe with the help of a DAX calculation or something. I know you can do this on the filters pane with advanced filtering, but it limits you to just two selections.

 

So to come up with a simplified and not so boring example, lets say I have a slicer for ingredients. Then I choose eggs, flour, and milk in my slicer. I wouldn't want to see recipes that use any one ingredient like omelettes. I'd want to see receipes that use all three ingredients.

 

Should be easy as cake, right? Thanks in advance!

1 ACCEPTED SOLUTION

Thanks, but I couldn't get your measure to work. It was still selecting recipes with only one of the selected ingredient. Maybe it's because of the way I have it modeled. Basically it has like a Recipe and a 'Recepie Ingredients' table with a bi-directional filter which I should have mentioning earlier.

 

After messing around with the measure for awhile, this is what ended up working for me.

**ShowRecipe = 
VAR __IncludedIngredients = VALUES ( 'Dataset Datasources'[Database] )
VAR __SelectedIngredients = CALCULATETABLE(VALUES ( 'Dataset Datasources'[Database] ), REMOVEFILTERS('Dataset'))

RETURN
IF(COUNTROWS(__IncludedIngredients) = COUNTROWS(__SelectedIngredients), 1, 0)

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

You can filter a table visual containing recipes to do this.

 

Define a measure like

 

ShowRecipe =
VAR SelectedIngredients = VALUES ( Recipes[Ingredient] )
VAR RecipeIngredients =
    CALCULATETABLE (
        VALUES ( Recipes[Ingredient] ),
        ALLEXCEPT ( Recipes, Recipes[Recipe] )
    )
RETURN
    IF ( ISEMPTY ( EXCEPT ( SelectedIngredients, RecipeIngredients ) ), 1, 0 )

 

Then use this measure as a filter on the visual setting ShowRecipe is 1.

 

What this does is take the set of selected ingredients except for the ingredients in the recipe. If there aren't any selected ingredients not included in the recipe, then the set is empty and the measure returns 1, otherwise 0.

Thanks, but I couldn't get your measure to work. It was still selecting recipes with only one of the selected ingredient. Maybe it's because of the way I have it modeled. Basically it has like a Recipe and a 'Recepie Ingredients' table with a bi-directional filter which I should have mentioning earlier.

 

After messing around with the measure for awhile, this is what ended up working for me.

**ShowRecipe = 
VAR __IncludedIngredients = VALUES ( 'Dataset Datasources'[Database] )
VAR __SelectedIngredients = CALCULATETABLE(VALUES ( 'Dataset Datasources'[Database] ), REMOVEFILTERS('Dataset'))

RETURN
IF(COUNTROWS(__IncludedIngredients) = COUNTROWS(__SelectedIngredients), 1, 0)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.