Forum Discussion
How to dynamically filter rows using a parameter table?
Hi everyone,
I'm working on a Power BI report where users can select one or more KPIs from a parameter table (e.g., KPI1, KPI2, KPI3). I want to filter out rows from my data table where all selected KPIs are equal to 0.
Here’s a simplified example of my data:
Dimension KPI1 KPI2 KPI3
| A | 0 | 0 | 0 |
| B | 10 | 0 | 5 |
| C | 0 | 0 | 1 |
| D | 0 | 0 | 0 |
If the user selects KPI1 and KPI3, I want to exclude rows A and D, because both selected KPIs are 0 in those rows.
My current approach (pseudo-code):
I tried using a combination of SWITCH() and CONCATENATEX() to build dynamic conditions based on selected KPIs:
Then I use something like:
This is just pseudo-code to illustrate the logic.
The problem:
This approach doesn’t scale well when there are many KPIs and combinations. Also, SELECTEDVALUE() doesn’t work with parameter tables when multiple values are selected, so I can't easily build a dynamic condition.
Question:
Is there a better way to dynamically filter rows where all selected KPIs are equal to 0, based on a parameter table - without manually listing all combinations?
Thanks in advance for any ideas or suggestions!
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
8 Replies
asparagus1_ To dynamically filter rows in Power BI where all selected KPIs are equal to 0, you can use DAX to create a measure that checks the selected KPIs and filters the data accordingly. So this exactly what your are trying to do , it looks good
As I mentioned, I know this solution works, but it doesn’t scale well, which is important to me.
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
Hi asparagus1_
With scalability in mind, you could consider unpivotting the table as follows:
Select the dimension column, then from the 'Transform' ribbon in power query, select 'Unpivot Other Columns'
The result will look like this
Rename the column 'Attribute' to 'KPI', close and apply power query.
Add the following measure
ShowRow = VAR SelectedKPIs = VALUES(KPITable[KPI]) VAR NonZeroCount = CALCULATE( COUNTROWS(KPITable), KPITable[KPI] IN SelectedKPIs, KPITable[Value] <> 0 ) RETURN IF(NonZeroCount > 0, 1, 0)This will show 1 when there is a value present across the original tables rows
Use this as a visual level filter, showrow = 1.
This solution will be dynamic, allowing full scalability no matter how many KPIs you have!
I hope this helps, please give a thumbs up and mark as solved if it does, thanks!
Thank you for your help! Unfortunately, this solution won’t work because I don’t have a KPI table, I have parameters table.
