Apply AND logic in the slicer

[v-yiruan-msft]

Scenario:

In this article I am going to introduce how to apply AND logic in the slicer. First, I would like to share with you about the background about the situation. 

Common situation:

Sample data:

Above is a student's skill grade table. Normally, we create a slicer filter English and French, and we’ll get Bob, James and Nancy.

It is found that James is also shown in the table, because the slicer applied OR logic.

Expected Result:   

However, we want to filter for students who have both French and English, and the expected result should be as follows:

Here is the solution to apply AND logic in the slicer.

Solution: 

1. Create a disconnected table by DAX or entering data. Here I created a calculated table by DAX.

2. Create a slicer from the new table. And create a table visual from the main table.

3. Create a measure for the visual-level filters. If you want to learn more about it, please read about add a filter to a report in Power BI.

Meascure =
IF (
    ISFILTERED ( 'Skill Table'[Skill] )
        && MAX ( 'Table'[Skill] )
            IN ALLSELECTED ( 'Skill Table'[Skill] )
                && COUNTROWS ( 'Skill Table' )
                    <= CALCULATE (
                        COUNT ( 'Table'[Skill] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            [Skill]
                                IN ALLSELECTED ( 'Skill Table'[Skill] )
                                    && [Name] = MAX ( 'Table'[Name] )
                        )
                    ),
    1
)

The whole function is an IF conditional statement, if the above conditions are met, it returns 1:

• ISFILTERED()

The ISFILTERED() function is to determine whether the slicer is filtered. If you do not add a function, the visual will return all results when the slicer is not selected.

• MAX ( 'Table'[Skill] ) IN ALLSELECTED ( 'Skill Table'[Skill] )

The MAX ( 'Table'[Skill] ) is to get the current row of [Skill]. So the purpose of this formula is to compare the current row of [Skill] with the slicer selection.

• CALCULATE (COUNT ( 'Table'[Skill] ),FILTER (ALLSELECTED ( 'Table' ),[Skill] IN ALLSELECTED ( 'Skill Table'[Skill] )&& [Name] = MAX ( 'Table'[Name] )))

The formula is to count skills which is grouped by name, it also requires that skills are selected by slicer.

4. Add the measure into the filters, and set up show items when the value is 1.

5. Here’re the results:

When English and French are selected, Bob and Nancy are returned.

When English, French and Chinese are selected, only Bob is returned.

Hope this article helps everyone with similar questions.  

Author: Stephen Tao

Reviewer: Ula Huang, Kerry Wang

Apply AND logic in the slicer.pbix

[Greg_Deckler]

@v-yiruan-msft I like it, I did something similar back in the day. Patient Cohort (AND Slicer) - Microsoft Power BI Community. 

Cohort = 
VAR tmpTable1 = 
  GENERATE(
    DISTINCT(Diagnosis[Patient]),
    EXCEPT(
      DISTINCT(Diagnosis[Diagnosis]),
      CALCULATETABLE(DISTINCT(Diagnosis[Diagnosis]))
    )
  )
VAR tmpTable2 = SUMMARIZE(tmpTable1,Diagnosis[Patient])
VAR tmpTable3 = EXCEPT(DISTINCT(Diagnosis[Patient]),tmpTable2)
RETURN 
CONCATENATEX(tmpTable3,[Patient],",")

Amazing how different the solutions look!

[mkcongrove1]

Thank you very much for this!

[fz1]

This is great, thanks for posting! I had a follow up quesion though - if you were to add visuals to the page, how would you tie it all in together? Like how would you do a simple pie chart to visualize this? I tried putting in the same filter (measure = 1) but doesn't look like its working. 

[Anonymous]

Hi. This worked great. Thanks.
How could it be adapted so that if no values were selected in the slicer, it defaults to showing ALL items?