Not sure if this is possible in Power BI, I have a table with Dish and Ingredients. I want to create a filter on what ingredients are available so that I can then make the Dish.
If I filter on [Flour] I want to only see [Apple Pie] and [Pizza]. If I filter on [Tomato Sauce] I want to see [Pasta] and [Pizza].
| Dish | Ingredients |
|---|---|
| Apple pie | apples, sugar, flour, eggs |
| Pasta | spaghetti, tomato sauce |
| Pizza | flour, tomato sauce, cheese |
I thought of creating a fact table as below:
| Dish | Ingredients |
|---|---|
| Apple pie | apples |
| Apple pie | sugar |
| Apple pie | slour |
| Apple pie | eggs |
| Pasta | spaghetti |
| Pasta | tomato sauce |
| Pizza | flour |
| Pizza | tomato sauce |
| Pasta | cheese |
And a dim table as below and linking it to the fact table, but it is not returning the expected result. If I select tomato sauce and spaghetti, I only want to see Pasta, but I also see Pizza.
| Ingredients |
|---|
| apples |
| sugar |
| flour |
| eggs |
| spaghetti |
| tomato sauce |
| cheese |
2
By default, Power BI slicers apply OR logic to your visuals, and not AND logic.
To change that, you need to create a DAX measure:
IsDishFiltered =
IF (ISFILTERED(Ingredients[Ingredients]),
IF (
ISFILTERED ( Ingredients[Ingredients] )
&& MAX ( Dishes[Ingredients] )
IN ALLSELECTED ( Ingredients[Ingredients] )
&& COUNTROWS ( Ingredients )
<= CALCULATE (
COUNT ( Dishes[Ingredients] ),
FILTER (
ALLSELECTED ( Dishes ),
[Ingredients]
IN ALLSELECTED ( Ingredients[Ingredients] )
&& [Dish] = MAX ( 'Dishes'[Dish] )
)
),
1
),
1)
If you don’t want your dishes to show up until you have filtered the ingredients, remove the first and last rows.
Finally, apply this measure as a filter in the visual where you want to display the dishes.