DAX - Return values that contain ALL of the values selected in a multi-select slicer

[joyhackett]

Hi,

I'd like to be able to select from a multi-select list and return the values that include all of my selections. 

I have a table like this:

PRODUCT	INGREDIENTID_CONCAT
Smoothie AC	A,C
Smoothie AB	A,B
Smoothie ACD	A,C,D

I also have a table like this:

PRODUCT	INGREDIENTID
Smoothie AC	A
Smoothie AC	C
Smoothie AB	A
Smoothie AB	B
Smoothie ACD	A
Smoothie ACD	C
Smoothie ACD	D

and a dimension like this:

INGREDIENTID	INGREDIENT
A	Apples
B	Bananas
C	Cucumbers
D	Dairy

When the user selects Apples and Cucumbers from the ingredient slicer, I want to return the products that have BOTH A and C:

Smoothie AC

A,C

If anyone has a solution, please let me know!

Thanks in advance 🙂 Joy

[bolfri · Accepted solution]

Hi,

I've used only table 2 (fact_table) and 3 (dim_ingredient)

Measure:

Checker = 
var smoothie_ingredients = CALCULATE(COUNTROWS(fact_table),ALL(dim_ingredient))
var selected_ingredients = COUNTROWS(dim_ingredient)
var matched_ingredients = COUNTROWS(fact_table)
return if(and(smoothie_ingredients=smoothie_ingredients,smoothie_ingredients=matched_ingredients),1,0)

Preq:

- user need to select all ingredients in smoothie (so we need to know how many ingredients smoothie has)

- user can't select additional ingredient (so we need to know how many is selected and how many is machted)

If numer of ingredients are equal to selected ingredients and all maches then 1 else 0. 🙂 I've just filtered product with 1.

[joyhackett]

This is great! And super clean! 

Thank you so much 🙂

[bolfri]

Sorry, I found a bug in my own solution.

Before:

return if(and(smoothie_ingredients=smoothie_ingredients,smoothie_ingredients=matched_ingredients),1,0)

 

After:

return if(and(smoothie_ingredients=selected_ingredients,smoothie_ingredients=matched_ingredients),1,0)

 

Now logic is correct and it will work perfectly 😉

Checker = 
var smoothie_ingredients = CALCULATE(COUNTROWS(fact_table),ALL(dim_ingredient))
var selected_ingredients = COUNTROWS(dim_ingredient)
var matched_ingredients = COUNTROWS(fact_table)
return if(and(smoothie_ingredients=selected_ingredients,smoothie_ingredients=matched_ingredients),1,0)

[joyhackett]

Haha - Yes, I noticed that too. Actually, when  I c/p your measure and changed out the table/column names, I inadvertetly fixed it. 🙂 Thanks again!