CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

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

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.

Upvotes: 1

Views: 3471

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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 )

Upvotes: 1

Related Questions