Change your slicer easily from OR to AND logic in Power BI

In Power BI, slicers generally have OR logic implemented. This I normally also exactly what the user wants to happen. If I select the years 2021 and 2022 in a slicer, then I would like to see the values in the result which are either related to the year 2021 or to the year 2022.

OR logic not always the correct one

Sometimes, however, there are situations in which this is not the desired behavior. Let’s say we want to identify customers who bought “Product A” while also buying “Product C”. In such a case, I would like to select both products in the slicer. As a result, only those customers who have purchased both products should be displayed in a table.
For such a case I need an AND logic for my slicer.

In principle, the logic of the slicer cannot be changed. But I can create an AND logic with a measure and then either return only the desired result or filter a visual with the measure.

Example Scenario – Which Lord of the Rings character got which weapons

As today’s example, let’s take our characters from Lord of the Rings. We would like to know which character is using which weapon. Each character can have multiple weapons and we would like to display this using an AND filter.

Here is the overview of which character has which weapon:

Our data model is also kept quite simple for this scenario. We have the 2 dimensional tables ‘Person’ and ‘Weapon’ and the fact table ‘Fact’:

As a first step, we create a measure and save as a variable the values selected in the slicer with the ALLSELECTED function, as well as the number of elements selected in the slicer.

Filter AND Weapon = 
-- Get the chosen weapons from the slicer
VAR vSlicerSelection = ALLSELECTED ( Weapon[Weapon] ) 
-- Count the amount of chosen weapons fromt the slicer
VAR vAmountSelected = COUNTROWS ( vSlicerSelection )

Since we want to have the weapons per person, we use the VALUES function to list each person and add to the virtual table the number of weapons with the ADDCOLUMNS function:

-- Calculate the amount of weapons per person
VAR vWeaponsPerCharacter =
    ADDCOLUMNS (
        VALUES ( Person[Name] ),
        "@NumberWeapons",
            CALCULATE (
                DISTINCTCOUNT ( 'Fact'[WeaponID] ),
                KEEPFILTERS ( vSlicerSelection ),
                ALL ( Weapon )
            )
    

After that, we just need to find out the amount of weapons for the current person in the filter context:

-- Return the number of weapons per person
VAR vNumberWeapons = MAXX ( vWeaponsPerCharacter, [@NumberWeapons] )

As a final step, we need to compare this with the number of options selected in the slicer. If 2 weapons are selected in the slicer and the person in the current filter context owns 2 weapons, it means that he owns both selected weapons. Accordingly, the person should be displayed.

For this case I will return a 1. If the person has less weapons a 0.

-- If the number of the current person is equal to the number of selected items or if no selection is done
-- then return 1, otherwise return 0
VAR vResult =
    IF (
        vNumberWeapons = vAmountSelected || vAmountSelected = COUNTROWS ( ALL ( Weapon[Weapon] ) ),
        1,
        0
    )

Finally, these single blocks simply need to be combined into a measure:

Filter AND Weapon = 
-- Get the chosen weapons from the slicer
VAR vSlicerSelection = ALLSELECTED ( Weapon[Weapon] ) 
-- Count the amount of chosen weapons fromt the slicer
VAR vAmountSelected = COUNTROWS ( vSlicerSelection )
-- Calculate the amount of weapons per person
VAR vWeaponsPerCharacter =
    ADDCOLUMNS (
        VALUES ( Person[Name] ),
        "@NumberWeapons",
            CALCULATE (
                DISTINCTCOUNT ( 'Fact'[WeaponID] ),
                KEEPFILTERS ( vSlicerSelection ),
                ALL ( Weapon )
            )
    )
-- Return the number of weapons per person
VAR vNumberWeapons = MAXX ( vWeaponsPerCharacter, [@NumberWeapons] )
-- If the number of the current person is equal to the number of selected items or if no selection is done
-- then return 1, otherwise return 0
VAR vResult =
    IF (
        vNumberWeapons = vAmountSelected || vAmountSelected = COUNTROWS ( ALL ( Weapon[Weapon] ) ),
        1,
        0
    )
-- return the result
RETURN
    vResult

We can use this measure to filter the corresponding visual. This way we change the behavior from an OR to an AND slicer:

The demo file can be downloaded here: