How to dynamically filter rows using a parameter table?

Hi asparagus1_,

Thank you for reaching out to Microsoft Fabric Community.

Thank you bhanu_gautam and wardy912 for the prompt response.

Based on the requirement, you can still use the unpivot approach even if you only have a parameter table and no separate KPI table in your model.

In Power Query, unpivot your KPI columns so the table has the format (Dimension, KPI, Value) using the KPI names from your parameter table so that it is fully dynamic. And when you add new KPIs to the parameter table, they are automatically included without changing the DAX.

After unpivoting, create a measure for example like below:

ShowRow = VAR SelectedKPIs = VALUES( Parameter[KPI] )

VAR NonZeroCount =

    CALCULATE(

        COUNTROWS(YourUnpivotedTable),

        TREATAS(SelectedKPIs, YourUnpivotedTable[KPI]),

        YourUnpivotedTable[Value] <> 0

    )

RETURN IF(NonZeroCount > 0, 1, 0)

This measure will return 1 for any row where at least one of the selected KPIs in the parameter table has a non-zero value.

And use ShowRow = 1 as a visual level filter. This works for any KPI selection and scales to any number of KPIs without listing combinations in DAX.

Thanks and regards,

Anjan Kumar Chippa