Power BI Matrix: Filter rows where all columns evaluate to zero

Question

I have a matrix, which has around 6-7 fields plotted as rows (for grouping, expand collapse). The matrix has 10+ columns.

In value, I have a single measure, which returns value based on the column it is plotted for.

i.e.

CALCULATE (
        SWITCH (
             SelectedValue,
            "Column1", [measure1],
            "Column2", [measure2],
            "Column3", [measure3],
            "Column4", [measure4],    
            .
            .
            .
            "column20", [measure20]
        )
)

Everything works perfectly here. Now we have a measure, which sums all measures used in the switch case above. This measure is used as a visual level filter for the matrix, so we can remove rows where all values are zero.

This works as well, however, it takes a heavy toll on performance. Thus, I am looking for a more efficient and less performance-intensive approach to filter rows in a matrix where all values are zero.

Alexis Olson Alexis Olson · Accepted Answer · 2019-08-28T15:38:56

One possibility would be to transform 0 values into blanks, which Power BI will hide if the whole column/row is blank.

Measure = 
VAR Output =
CALCULATE (
        SWITCH (
             SelectedValue,
            "Column1", [measure1],
            "Column2", [measure2],
            "Column3", [measure3],
            "Column4", [measure4],    
            .
            .
            .
            "column20", [measure20]
        )
)
RETURN
   IF ( Output = 0, BLANK(), Output )

Power BI Matrix: Filter rows where all columns evaluate to zero

1 Answers