Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Filter matrix based on slicer values across multiple columns

Hi everyone, I’m working with a dataset where I need to apply multiple slicers using AND logic to filter rows in a matrix visual. Here’s the setup: The dataset has the columns: Group1, Group2, Ca...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

    Based on your description, you can refer to the following solution.

    Sample data.

    1.Create two new table.

     

    Parameter = GENERATESERIES(0, 10, 0.1)
    Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

     

    There is no relationship among the tables.

    2.Create the following measure.

     

    Measure2 =
VAR _filter =
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Group1]
            IN VALUES ( 'Table'[Group1] )
                && [Group2]
                    IN VALUES ( 'Table'[Group2] )
                        && [Project] IN VALUES ( 'Table'[Project] )
    )
VAR _count1 =
    COUNTROWS (
        FILTER (
            _filter,
            'Table'[Category] = "Apartment - GSF"
                && 'Table'[Value-GSF] >= MIN ( Parameter[Value] )
                && 'Table'[Value-GSF] <= MAX ( Parameter[Value] )
        )
    )
VAR _count2 =
    COUNTROWS (
        FILTER (
            _filter,
            'Table'[Category] = "Original Pricing Date"
                && 'Table'[Value-OPD] >= MIN ( 'Calendar'[Date] )
                && 'Table'[Value-OPD] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Category] ) = "Apartment - GSF"
            || SELECTEDVALUE ( 'Table'[Category] ) = "Original Pricing Date",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', _count1 > 0 && _count2 > 0 )
        ),
        CALCULATE ( SUM ( 'Table'[Value] ) )
    )

     

    3.Create two slicers, put the paramater of the paramater table to one slicer, and put the date field of the calendar table to the other. then create the matrix, and put the following field to the matrix.

    Output

     

    Best Regards!

    Yolo Zhu

    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
1 year ago

Hi Anonymous 

Based on your description, you can refer to the following solution.

Sample data.

1.Create two new table.

 

Parameter = GENERATESERIES(0, 10, 0.1)
Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

 

There is no relationship among the tables.

2.Create the following measure.

 

Measure2 =
VAR _filter =
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Group1]
            IN VALUES ( 'Table'[Group1] )
                && [Group2]
                    IN VALUES ( 'Table'[Group2] )
                        && [Project] IN VALUES ( 'Table'[Project] )
    )
VAR _count1 =
    COUNTROWS (
        FILTER (
            _filter,
            'Table'[Category] = "Apartment - GSF"
                && 'Table'[Value-GSF] >= MIN ( Parameter[Value] )
                && 'Table'[Value-GSF] <= MAX ( Parameter[Value] )
        )
    )
VAR _count2 =
    COUNTROWS (
        FILTER (
            _filter,
            'Table'[Category] = "Original Pricing Date"
                && 'Table'[Value-OPD] >= MIN ( 'Calendar'[Date] )
                && 'Table'[Value-OPD] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Category] ) = "Apartment - GSF"
            || SELECTEDVALUE ( 'Table'[Category] ) = "Original Pricing Date",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', _count1 > 0 && _count2 > 0 )
        ),
        CALCULATE ( SUM ( 'Table'[Value] ) )
    )

 

3.Create two slicers, put the paramater of the paramater table to one slicer, and put the date field of the calendar table to the other. then create the matrix, and put the following field to the matrix.

Output

 

Best Regards!

Yolo Zhu

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
    1 year ago

    Hey Yolo Zhu, thank you so much for your solution!

    I only had to add one line in order to be able to get the project to drop off the matrix as well (I also changed the calendar parameter to be another decimal value parameter because I was having some issues with the varying date types in the same column, but this is close enough).

    Here's the final code:

    MeasureNEW =
VAR _filter =
    FILTER (
        ALLSELECTED ( 'NumericData' ),
        [Group1] IN VALUES ( 'NumericData'[Group1] )
            && [Group2] IN VALUES ( 'NumericData'[Group2] )
            && [Attribute] IN VALUES ( 'NumericData'[Attribute] )
    )
VAR _count1 =
    COUNTROWS (
        FILTER (
            _filter,
            'NumericData'[Category] = "Apartment - GSF"
                && 'NumericData'[Value - GSF] >= MIN ( Parameter[Value] )
                && 'NumericData'[Value - GSF] <= MAX ( Parameter[Value] )
        )
    )
VAR _count2 =
    COUNTROWS (
        FILTER (
            _filter,
            'NumericData'[Category] = "Apartment - RSF"
                && 'NumericData'[Value - RSF] >= MIN ( 'RSF'[Value] )
                && 'NumericData'[Value - RSF] <= MAX ( 'RSF'[Value] )
        )
    )
RETURN
    IF (
        _count1 > 0 && _count2 > 0,
        CALCULATE (
            SUM ( 'NumericData'[Value] )
        ),
        BLANK()  -- Hides the project column when the conditions are not met
    )