Filter matrix based on slicer values across multiple columns

[Anonymous]

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, Category, Project, Value, Value - GSF, Value - OPD
  • Value - GSF is null except for where Category = 'Apartment - GSF' and the data type is decimal
  • Value - OPD is null except for where Category = 'Original Pricing Date' and the data type is date
• I have two slicers: one for Apartment - GSF (a numeric value) and one for Original Pricing Date (a date value).
  • They use the columns Value - GSF and Value - OPD
• The matrix visual has values from the Value column, columns that represent different projects (Project A, Project B) using the Project column, and the rows are grouped by Group1, Group2, and Category.
• The goal is for both slicers to apply an AND condition: if a project’s Apartment - GSF is within the selected range AND the Original Pricing Date is within the selected date range, only then should the project be visible in the matrix.

I need a solution where both slicers work together to filter the matrix based on this AND logic, while still showing data from other categories.
Issue: Using the slicers does not filter the dataset just by the rows where Category = 'Apartment - GSF' and Category = 'Original Pricing Date', rather it filters by all values of the matrix. It works when creating two matrices, one where Category is Apartment - GSF and one where Category is Original Pricing Date; however, this is not the desired output because I need to the data from the other categories too and all in one matrix. I’ve tried creating measures to apply this logic, but Power BI requires aggregation functions (like MAX or MIN) in measures, which breaks the row-level filtering I need. I need to check if the values for each project match the slicer ranges without using aggregation functions, but I haven’t been able to get this working.

Any guidance on how to achieve this would be much appreciated!

[Anonymous · Accepted solution]

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]

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
    )