cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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:

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.

``````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.