cancel
Showing results for 
Search instead for 
Did you mean: 
yingyinr

Apply AND logic in the slicer

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:

yingyinr_0-1667439158838.png

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.

yingyinr_1-1667439211249.png

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:

yingyinr_2-1667439277453.png

 

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.yingyinr_3-1667439308071.png

yingyinr_4-1667439333584.png

 

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

yingyinr_5-1667439374626.png

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.

yingyinr_6-1667439477726.png

 

5. Here’re the results:

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

yingyinr_7-1667439509405.png

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

yingyinr_8-1667439531964.png

 

Hope this article helps everyone with similar questions.  

 

Author: Stephen Tao

Reviewer: Ula Huang, Kerry Wang

Comments

@yingyinr 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!

Polls
What is your favorite Power BI feature release for November 2022?