Forum Discussion

Jeffrey9's avatar
Jeffrey9
Regular Visitor
3 years ago
Solved

How to add an AND filter visual

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!

 

 

 

 

 

 

 

 

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

3 Replies