Creating a dynamic filter in Matrix table

Hi jainmohit_0710

I completely understand the strict limitations of working within a locked semantic model where you cannot create disconnected tables or calculated tables.

When you place System Name on the columns of a Matrix visual, the Power BI rendering engine automatically suppresses any column that is excluded by the slicer's filter context. This is why you cannot keep "System 2" and "System 3" visible when "System 1" is selected. Since a disconnected table is out of the question, we must pivot your approach from using a Matrix with dynamic columns to using a Table visual with explicit DAX measures.

Instead of relying on a column to generate your matrix headers, we will create individual measures for each system. This allows us to strictly control the filter context for the columns, while allowing the slicer to naturally filter the Report ID rows.

1. Use your standard Table 2'[System Name] column for the drop-down slicer.

2. Create a separate measure for each System you want to display. The trick here is to use REMOVEFILTERS() (or ALL()) to bypass the slicer's restriction specifically for the column's calculation, while retaining the row-level filter on the Report ID.

System 1 Flag =

VAR SystemCount =

CALCULATE (

COUNTROWS ( 'Table 1' ),
'Table 2'[System Name] = "System 1",
REMOVEFILTERS ( 'Table 2'[System Name] )

)

RETURN
IF ( SystemCount > 0, 1, 0 )

System 2 Flag =

VAR SystemCount =

CALCULATE (

COUNTROWS ( 'Table 1' ),
'Table 2'[System Name] = "System 2",
REMOVEFILTERS ( 'Table 2'[System Name] )

)

RETURN
IF ( SystemCount > 0, 1, 0 )

System 3 Flag =

VAR SystemCount =

CALCULATE (

COUNTROWS ( 'Table 1' ),
'Table 2'[System Name] = "System 3",
REMOVEFILTERS ( 'Table 2'[System Name] )

)

RETURN
IF ( SystemCount > 0, 1, 0 )

3. Add a standard Table visual (not a Matrix) to your canvas.

4. Add Table 1'[Report ID] to the Columns/Values well.

5. Drag your new measures ([System 1 Flag], [System 2 Flag], [System 3 Flag]) into the visual.

If this solves your problem, please mark this as solution and give me a kudos.