Slicer with AND Logic

[tonysantangelo]

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:

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:

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.

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[DmitryKo]

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]

@tonysantangelo , check if this can help

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