cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

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

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

1 ACCEPTED SOLUTION
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper II

This is great! And super clean!

Thank you so much 🙂

Super User

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)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper II

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!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors