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