Achieving Filtering in Dynamic Power BI Matrix

[manish-tripathi]

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?

[timalbers · Accepted solution]

Hi @manish-tripathi 

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.

Cheers 

Tim

[manish-tripathi · Accepted solution]

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 

[timalbers · Accepted solution]

Hi @manish-tripathi 

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.

Cheers 

Tim