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

     

     

Anonymous's avatar
Anonymous
Not applicable
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.