Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tonysantangelo
Frequent Visitor

Slicer with AND Logic

By default when you select multiple values in a slicer, Power BI filters to rows that match the condition of any of the selected values (OR logic). I'm looking to tweak the slicer such that all of the selected values must be true (AND logic). Example:

tonysantangelo_0-1666387423090.png

With OR logic, if I select red and blue, all rows will remain. The desired behavior with AND logic is that only rows 1-4 will remain because IDs 1 and 2 are the only IDs with colors of red and blue.  

 

After reading this example post and several similar ones, I'm almost certain you can do this with a DAX measure, but my beginner DAX skills are failing me. Can you guys assist me with writing this DAX measure? What I think are the high level steps:

1. Count the number of values selected in the slicer

2. Count the number of rows for each ID when filtered to the selected values in the slicer

3. If they are equal, return 1

4. Add a filter where the measure value = 1 or is not blank.

 

Desired outcome when the slicer has red and blue selected:

tonysantangelo_1-1666387693209.png

Here are some DAX snippets I have so far, referencing the above steps. Step 1:

 

var selectedColors=VALUES(TableName[Color]) 
var numberSelectedColors=COUNTROWS(selectedColors)

 

Step 2:

 

var selectedColorsById= 
COUNTROWS( 
    DISTINCT( 
        SELECTCOLUMNS( 
            FILTER( TableName, TableName[Color] in selectedColors ), 
            "Color", 
            TableName[Color] 
        ) 
    ) 
)

 

Where I'm stuck is somewhere between steps 2 and 3 and dealing with my table granularity. The concept of "context" is very jumbled to me. Snippets 1 and 2 work by themselves, such as in a card visual, but when I make that snippet a measure on my table, it evaluates to 1 no matter how many slicer values I have selected.

tonysantangelo_3-1666388478816.png

 

The confusing part to me is that I need this measure to use "filter context" in that the measure needs to respond to my slicer. I also need row context to some degree to evaluate row by row, but even then I'm really looking at multiple rows to figure out how many rows have the same Id.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

So this is marked as a solution, yet solution is not present in a reply, and provided link points to a non-existent file.

Wouldn't it be better to post an actual solution instead?

amitchandak
Super User
Super User

@tonysantangelo , check if this can help

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.