Dynamic Table Filtering via Matrix with Measure-Based Rows and What-If Parameter in Power BI

[Rutvik05]

I’m trying to filter a table dynamically in Power BI based on a matrix visual. The matrix is built using a custom table (LikelihoodConsequenceMatrix) that displays combinations of Likelihood (calculated dynamically as a measure using a What-If parameter) and Consequence (a column from the main table).

The issue is that since Likelihood is a measure, it cannot participate in direct relationships with the main table. As a workaround, I created the custom table to enable the matrix visual, but I’m struggling to make it filter the original table dynamically when selecting values in the matrix.

I need help with:

1. Enabling the matrix to filter visuals and the original table dynamically.

2. Simulating relationships or filtering with the dynamic Likelihood measure.

3. Ensuring the solution updates in response to changes in the What-If parameter.

[Akash_Varuna]

Hi Please do try these
Create a LikelihoodConsequenceMatrix using CROSSJOIN to combine the What-If Likelihood parameter and Consequence values from the main table.

Create a measure IsRowSelected that checks if the matrix-selected Likelihood and Consequence match rows in the main table.

Apply the IsRowSelected = 1 measure as a filter on the main table visual to dynamically show matching rows.

Ensure the matrix visual filters other visuals by enabling cross-filtering in the report settings.
If the post helps please do give a kudos and accept this as solution
Thanks in Advance

[DataNinja777 · Accepted solution]

Hi @Rutvik05 ,

To dynamically filter your original table in Power BI based on selections made in a matrix visual, even when Likelihood is calculated as a measure using a What-If parameter, you can achieve this by simulating relationships and leveraging calculated measures. Since Likelihood cannot directly participate in relationships, you need a custom table, LikelihoodConsequenceMatrix, containing all possible combinations of Likelihood (driven by the What-If parameter) and Consequence (a column from your main table). This table serves as the source for your matrix visual, allowing you to visualize the combinations.

To capture user selections in the matrix, you can create a measure like this:

SelectedCombination = 
CONCATENATEX(
    VALUES(LikelihoodConsequenceMatrix[Likelihood]) & " | " & VALUES(LikelihoodConsequenceMatrix[Consequence]),
    ", "
)

This measure captures the selected combinations dynamically from the matrix. Next, to filter the original table dynamically based on the selections, you can use a calculated measure to simulate a relationship. The measure below filters rows in the main table by mapping the selected values from LikelihoodConsequenceMatrix:

FilteredRows = 
CALCULATE(
    COUNTROWS(MainTable), 
    TREATAS(
        VALUES(LikelihoodConsequenceMatrix[Consequence]), 
        MainTable[Consequence]
    ),
    TREATAS(
        VALUES(LikelihoodConsequenceMatrix[Likelihood]), 
        MainTable[Likelihood]
    )
)

The TREATAS function allows you to project values from the custom table into your main table's columns, effectively applying a dynamic filter. Ensure that the LikelihoodConsequenceMatrix table dynamically updates based on the What-If parameter. You can use a measure to reflect the current Likelihood value dynamically in the matrix, such as:

DynamicLikelihood = 
SELECTEDVALUE(LikelihoodParameter[Likelihood])

This ensures the matrix visual reacts to changes in the What-If parameter, and the dynamic filtering logic remains intact. By testing this setup, your matrix visual should update correctly, and the original table will reflect filtered results based on the selected combinations. These steps ensure seamless interaction between visuals, dynamically filtering the data while respecting the dynamic Likelihood values from the What-If parameter.

Best regards,