Creating a dynamic filter in Matrix table

[jainmohit_0710]

Hi All

I need a support on creating a filter in Matrix table so that it will show entire data related to that specific selection.

We are wroking in Semantic model we are not able to create any table or dynamic contents. Below is our existing Matrix table report structure:

Report ID in Column from table 1 , System name from table 2 and Count of report ID from Table 1

Report ID            System 1               System 2       System 3

1                            1                           0                    1

2                            1 

3                             0                         1                     1

Currently we have normal drop down filter on system name, now requirment is to create filter in such a way when some select system 1 as filter it will also show other system asscoiated to it Like Below:

Report ID            System 1               System 2       System 3

1                            1                           0                    1

2                            1 

Could some one guide how this can be achieved or any other way if Matrix table is not the correct solution.

[Praful_Potphode]

Hi @jainmohit_0710 ,

i tried replicating filtering logic using measure filtering in visual level filter.

PFA sample pbix.

Please give kudos or mark it as solution once confirmed.
Thanks and Regards,

Praful

[mizan2390]

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.

[v-shchada-msft]

Hi @jainmohit_0710,

Could you please confirm whether the issue has been resolved or if you are still facing challenges with the Matrix filtering behavior?

If the issue still persists, could you please share a small sample dataset along with the expected output after selecting a specific system in the slicer, as requested earlier by @pcoley? This will help better understand the exact requirement and suggest the most suitable approach.

Thanks and Regards.

[v-shchada-msft]

Hi @jainmohit_0710,

I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[pcoley]

As i understand you need to have a slicer and when user selects System 1 in that slicer, show only Report IDs that are associated with System 1, but keep all System columns visible in the matrix.

I suggest:

1. Create a Disconnected Slicer Table (via DAX)

Since you mentioned you cannot create physical tables easily, create this as a calculated table in the semantic model:

System Slicer = DISTINCT ( 'Table 2'[System Name] )

• This table should NOT have any relationship to your other tables.

2. Create the Row Filter Measure

Report Has Selected System = 
VAR SelectedSystems = VALUES ( 'System Slicer'[System Name] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table 2' ),
        'Table 2'[System Name] IN SelectedSystems,
        ALLEXCEPT ( 'Table 1', 'Table 1'[Report ID] )
    ) + 0

3. Apply to the Matrix Visual

1. Select your Matrix visual.
2. In the Filters pane → Filters on this visual.
3. Drag the measure Report Has Selected System into the filters.
4. Set the filter to is 1.

Now when the user selects one or more systems in the slicer, the matrix will:

• Filter the rows (Report IDs) to only those associated with the selected system(s)
• Keep all columns (System 1, System 2, System 3, etc.) visible

---

Slicer Setup

• Use the System Slicer table in a Dropdown or List slicer.
• Allow multi-select if needed.

---

Alternative (If you cannot create the calculated table)

You can use the original 'Table 2'[System Name] in the slicer, but then you must use Edit interactions to stop the slicer from filtering the matrix directly, and rely only on the measure above. However, the disconnected table approach is much cleaner.

[jainmohit_0710]

@pcoley 

This seems to be solution I might be looking for, but could you please help me creating same without any table as we are not allowed to do so. That will be great help I think that might required chnages in report has selected source system.

[v-shchada-msft]

Hello @jainmohit_0710,
Thank you for posting your query in the Microsoft Fabric Community Forum.

If a disconnected slicer table cannot be created, using System Name as both slicer and Matrix columns is not recommended, as Power BI will filter the column axis and may collapse system columns.

If you are not able to create a separate table in your environment, as a workaround Systems can be represented as individual measures instead of columns, with Report ID in rows and each measure evaluating system membership from the ReportSystemMapping table (returning 1/0).

This approach works for small or static datasets only, as it is not scalable and requires manual maintenance for each new system.

Overall, the disconnected slicer pattern remains the best practice for scalable and reliable models.

please Refer below pbix file.

Thank you.

[MFelix]

Hi @jainmohit_0710 ,

If you have a filter based on the system name, and that is the filter applied this should pick up the two rows that refer the system 1 name.

Can you give a better explanation on how your model is setup and the relationships between the tables and how the visual is setup? If possible can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[jainmohit_0710]

@MFelix Thanks ...Explanation given above...Apologies due to confidentiality we are not allowed to share actual PBIX

[MFelix]

Hi @jainmohit_0710 ,

I understand the privacy question can you please just let me know how you have your model setup? Do you have a single table? Is there any relatonships? Were does each column you have on the visualization are coming from?

If you are able to do a smal mockup would be great if not please try and give some insights on the model setup.

[jainmohit_0710]

@pcoley @MFelix 

Our model is having multiple tables but we need to use only two tables Details and Source system. We are creating Matrix table where we are dragging :

Row - Report ID from Details table

Columns - System name from Source System table

 Value - Count of report ID

As advised above we have created a new table in semantic model which is calculating distinc value from Source system table

System Slicer = DISTINCT ( Source_system[System Name] )

We have created a measure 

Report has Selected System =
Var SelectedSystem = 
  Values = 'System slicer'[system name]
Return
If(
  NOT ISFILTERED ('System slicer'[system name]),
  1,
  CALCULATE (
     COUNTROWS('Details'),
     TREATAS (SelectedSystem,
     'Source system'[system name]
      )
   ) > 0
)

Post that we have dragged the meaure on section filter on visual is 1.

There is no error in measure or matrix table. But when we filter on any specific source system like 1 it had 50 rows then it should show only 50n rows plus other source system associated with those report ID.

Please guide any other way to achieve this solution other than Matrix table will also condisered till it giving results.

Regards

Mohit Jain 

[pcoley]

@jainmohit_0710 
I do not have the complete understanding of your need. please be more precise about your need and please explain further what is the issue here: ...But when we filter on any specific source system like 1 it had 50 rows then it should show only 50n rows plus other source system associated with those report ID.