Skip to main content
cancel
Showing results for 
Search instead 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

Reply
joyhackett
Helper II
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:

PRODUCTINGREDIENTID_CONCAT
Smoothie ACA,C
Smoothie ABA,B
Smoothie ACDA,C,D


I also have a table like this:

PRODUCTINGREDIENTID
Smoothie ACA
Smoothie ACC
Smoothie ABA
Smoothie ABB
Smoothie ACDA
Smoothie ACDC
Smoothie ACDD

 

and a dimension like this:

INGREDIENTIDINGREDIENT
AApples
BBananas
CCucumbers
DDairy

 

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

Smoothie ACA,C

 

If anyone has a solution, please let me know!

Thanks in advance 🙂 Joy

1 ACCEPTED SOLUTION
bolfri
Super User
Super User

Hi,

 

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

bolfri_0-1673478127006.png

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!




View solution in original post

4 REPLIES 4
bolfri
Super User
Super User

Hi,

 

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

bolfri_0-1673478127006.png

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!




This is great! And super clean! 

Thank you so much 🙂

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!




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!

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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