Forum Discussion
Achieving Filtering in Dynamic Power BI Matrix
I'm working with a dynamic matrix in Power BI, where the rows can change based on one field parameter (e.g., from audience to brand), and the values can change based on another field parameter (e.g., from bounces to clicks). The columns represent different weeks.
My goal is to filter the matrix to display only rows where the total across all weeks is greater than zero. However, when I select a different field (e.g., changing from bounces to clicks), the filter also changes to match the selected metric. I want the filter to remain consistent and filter out all rows where the column total is zero, regardless of the selected metric. How can I achieve this?
try to create a new measure like this:qualifier = VAR selected_measure = SWITCH( SELECTEDVALUE(field_parameter_table[Fields]), "'mesaure_table'[Clicks]", [Clicks], "'mesaure_table'[Bounces]", [Bounces], ... ) RETURN IF( selected_measure = 0, FALSE(), TRUE() )You will have to declare every measure you are using in your field parameter. The variable "selected_measure" will return the corresponding measure based on the current selection. If this measure is 0, the "qualifier" measure will return false, otherwise true. You can use this measure as a report filter and filter by qualifier = true.
Do not forget to replace the table and field names with the correct ones from your model.
CheersTim
Thank you timalbers the solution worked with a small modificaton
qualifier = VAR selected_measure = SWITCH( SELECTEDVALUE(field_parameter_table[Fields]), "'_Helper>Metric List'[Bounce % 2]", [Bounce % 2], "'_Helper>Metric List'[Bounce]", [Bounce], "'_Helper>Metric List'[Clicks]", [Clicks], ) RETURN selected_measure
2 Replies
try to create a new measure like this:qualifier = VAR selected_measure = SWITCH( SELECTEDVALUE(field_parameter_table[Fields]), "'mesaure_table'[Clicks]", [Clicks], "'mesaure_table'[Bounces]", [Bounces], ... ) RETURN IF( selected_measure = 0, FALSE(), TRUE() )You will have to declare every measure you are using in your field parameter. The variable "selected_measure" will return the corresponding measure based on the current selection. If this measure is 0, the "qualifier" measure will return false, otherwise true. You can use this measure as a report filter and filter by qualifier = true.
Do not forget to replace the table and field names with the correct ones from your model.
CheersTim
Thank you timalbers the solution worked with a small modificaton
qualifier = VAR selected_measure = SWITCH( SELECTEDVALUE(field_parameter_table[Fields]), "'_Helper>Metric List'[Bounce % 2]", [Bounce % 2], "'_Helper>Metric List'[Bounce]", [Bounce], "'_Helper>Metric List'[Clicks]", [Clicks], ) RETURN selected_measure