Dynamic filtering Matrix Columns (and Rows)

[Robert_G]

I have a table "MyTable": Each row contains a row and column label, as well as a role to which this row applies. The row and column labels should be displayed in a matrix visual.

The problem: If I manually filter the visual by role, the display is correct.

I want to implement the filter settings dynamically using a switch measure that returns 0 = hide/1 = show. dependig how many Emps / Roles are selectes via a slicer

RoleSelector = 
var Sales1_count = CALCULATE(COUNT(SalesMembers[Name]),SalesMembers[Role]="Sales1")
var Sales2_count = CALCULATE(COUNT(SalesMembers[Name]),SalesMembers[Role]="Sales2")

var result =
        switch(TRUE()
                ,Sales1_count>=1 && Sales1_count>=1, CALCULATE(max(MyTable[Role])) in {"Sales1-cumulative","Sales2-cumulative"}
                )
return
if(result ,1,0)

Emp-Table

SalesMembers = 
DATATABLE(
     "Name",STRING
    ,"Role",STRING
    ,{
         {"Alice","Sales1"}
        ,{"Bob","Sales1"}
        ,{"Jim","Sales2"}
        ,{"Joe","Sales2"}
    }
)

However, this doesn't work correctly; columns that should actually be filtered out are also displayed in the matrix.

Any Idea how to fix that?

[v-veshwara-msft]

Hi @Robert_G ,
Thanks for posting in Microsoft Fabric Community.

As pointed out by @OwenAuger , when using a measure as a visual-level filter in a matrix, it only affects the rows, not the columns.

To reproduce your scenario and validate the workaround, I used the same table and slicer setup you've shared.

Used this Measure:

RoleSelector = 
VAR SelectedRoles = VALUES(SalesMembers[Role])
RETURN
IF (
    SELECTEDVALUE(MyTable[Role]) IN 
    UNION(
        SELECTCOLUMNS(SelectedRoles, "Role", SalesMembers[Role]),
        SELECTCOLUMNS(SelectedRoles, "Role", SalesMembers[Role] & "-cumulative")
    ),
    1,
    0
)

Instead of applying the measure as a filter directly on the visual, wrapped the metric in a measure that returns blank when the condition isn't met:

Filtered Measure = 
IF ( [RoleSelector] = 1, 1, BLANK() )

This ensures the matrix only shows values where the selected base roles and their corresponding cumulative roles are relevant. Columns with all blank values are automatically hidden by the matrix.

Here’s the result after selecting all employees from the slicer:

As you can see, only the appropriate combinations appear in the matrix based on the selected roles.

Thnaks to @OwenAuger for highlighting the matrix behavior, which helped in validating this approach.

Hope this helps. Please reach out for further assistance.

Please consider marking the helpful reply as Accepted Solution and giving kudos to assist others with similar issues.

[Robert_G]

Thanks for your reply & help!

[OwenAuger]

Hi @Robert_G 

A "quirk" when applying a visual-level filter defined by a measure to a Matrix visual is that the granularity of the filter is the row fields only, and column fields are ignored.

In other words, a visual-level filter based on a measure filters rows but not columns, which isn't too helpful given your requirements.

See this article: Applying a measure filter in Power BI - SQLBI

Assuming you will include a measure in the Matrix, I would suggest either:

• Writing the measure so that it is nonblank only when RoleSelector = 1. Something like:
  Filtered Measure = IF ( [RoleSelector], <Original Measure> )
  (this will work whether RoleSelector returns True/False or 1/0)
• Doing the same thing but with a calculation group with calculation item expression:
  IF ( [RoleSelector], SELECTEDMEASURE ( ) )

Would this work in your case?

[Robert_G · Accepted solution]

Thanks OwenAuger,

your solution principally works, as long then Original Measure does not return Blank. Otherwise the intended columns to be shown disapear. 

Meanwile I have found a 3rd Party Matrix-Visual (ProfitBase), than shows the intended behaviour, showing only the columns that are needed & even shows rows and clumns when "show items with no data" is applied.