How to add an AND filter visual

[Jeffrey9]

I have a bridge table, let's say type of houses. And the fact table contains project registrations. Every project registration can contain multiple types of houses.

My filter visual is a dropdown list and I can select multiple values, but when I select two different types of houses, it shows all records containing one of the two types of houses. I want to keep the records having both types only.

Example: I select apartments and tiny house. As a result I want to keep the projects having both apartments AND tiny houses.

I tried to Google but I can't find the answer. Can anyone help? It is very much appreciated!

[Anonymous · Accepted solution]

Hi @Jeffrey9 ,

Here I suggest you to create an unrelate DimHouse table for slicer.

My Sample:

DimHouse:

DimHouse = VALUES('Table'[House])

Measure:

Filter Measure = 
VAR _SELECTVALUE =
    VALUES ( DimHouse[House] )
VAR _COUNT1 =
    COUNTROWS ( _SELECTVALUE )
VAR _COUNT2 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Projects ] ),
            'Table'[House] IN _SELECTVALUE
        )
    )
RETURN
    IF ( _COUNT1 = _COUNT2, 1, 0 )

Add this measure into the visual level filter field of table visual and set it to show items when value = 1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[AlexisOlson]

Here are a couple of good articles about this:

https://www.sqlbi.com/articles/apply-and-logic-to-multiple-selection-in-dax-slicer/

https://radacad.com/slicer-with-and-condition-in-power-bi

It's also been covered many times by this community:

https://community.powerbi.com/t5/Desktop/Slicer-AND-logic/td-p/1397510

https://community.powerbi.com/t5/Desktop/Slicer-with-quot-AND-quot-logic/td-p/592018

https://community.powerbi.com/t5/Desktop/AND-logic-for-slicers/td-p/1206232

https://community.powerbi.com/t5/Desktop/Changing-slicer-logic-to-AND/m-p/885065

https://community.powerbi.com/t5/Desktop/Workaround-for-a-slicer-with-AND-logic/td-p/1484952

https://community.powerbi.com/t5/Desktop/Slicer-with-AND-Logic/m-p/2856353

https://community.powerbi.com/t5/Community-Blog/Apply-AND-logic-in-the-slicer/ba-p/2881583