Forum Discussion
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!
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)
2 Replies
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)
