Remove rows from a matrix visual

[dandreev]

Hello,
I have a matrix with nested rows and 5 columns. 
I need to remove or hide rows that are 0 or empty, but reapear when there is a value in any of the columns other then 0.

Thank you for your help!

[Kedar_Pande · Accepted solution]

@dandreev 

Create a Filter Measure:

ShowRow = 
IF (
NOT (
ISBLANK(SUM('YourTable'[Actual])) &&
ISBLANK(SUM('YourTable'[13M AVG])) &&
ISBLANK(SUM('YourTable'[Variance AVG])) &&
ISBLANK(SUM('YourTable'[Budgets])) &&
ISBLANK(SUM('YourTable'[Budget Variance])) &&
SUM('YourTable'[Actual]) = 0 &&
SUM('YourTable'[13M AVG]) = 0 &&
SUM('YourTable'[Variance AVG]) = 0 &&
SUM('YourTable'[Budgets]) = 0 &&
SUM('YourTable'[Budget Variance]) = 0
),
1,
0
)

Set the filter to show only where ShowRow equals 1.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[dandreev]

Thank you both for the solution! I twicked it a bit and it works. However, there is another column that shows percentage of type towards the total counts and when I apply the filter it breaks the calculation and it goeas to 100%. It has to do soething with filter context, but still could not figure it out.

[Kedar_Pande · Accepted solution]

@dandreev 

Create a Filter Measure:

ShowRow = 
IF (
NOT (
ISBLANK(SUM('YourTable'[Actual])) &&
ISBLANK(SUM('YourTable'[13M AVG])) &&
ISBLANK(SUM('YourTable'[Variance AVG])) &&
ISBLANK(SUM('YourTable'[Budgets])) &&
ISBLANK(SUM('YourTable'[Budget Variance])) &&
SUM('YourTable'[Actual]) = 0 &&
SUM('YourTable'[13M AVG]) = 0 &&
SUM('YourTable'[Variance AVG]) = 0 &&
SUM('YourTable'[Budgets]) = 0 &&
SUM('YourTable'[Budget Variance]) = 0
),
1,
0
)

Set the filter to show only where ShowRow equals 1.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[dandreev]

I forgot to add one more scenario. When Actual and Budget columns have values, for instance both have 2 and Actual Budget Variance column is equial to 0.

[Anonymous · Accepted solution]

Hi @dandreev ,

You can add the appropriate conditions to Kedar_Pande's:

ShowRow = 
SWITCH(
    TRUE(),
    NOT (
        ISBLANK(SUM('YourTable'[Actual])) &&
        ISBLANK(SUM('YourTable'[13M AVG])) &&
        ISBLANK(SUM('YourTable'[Variance AVG])) &&
        ISBLANK(SUM('YourTable'[Budgets])) &&
        ISBLANK(SUM('YourTable'[Budget Variance])) &&
        SUM('YourTable'[Actual]) = 0 &&
        SUM('YourTable'[13M AVG]) = 0 &&
        SUM('YourTable'[Variance AVG]) = 0 &&
        SUM('YourTable'[Budgets]) = 0 &&
        SUM('YourTable'[Budget Variance]) = 0
    ), 1,
NOT (
        ISBLANK(SUM('YourTable'[Actual])) &&
        ISBLANK(SUM('YourTable'[Budgets])) &&
        ISBLANK(SUM('YourTable'[Budget Variance])) &&
        SUM('YourTable'[Actual]) <> 0 &&
        SUM('YourTable'[Budgets]) <> 0 &&
        SUM('YourTable'[Budget Variance]) = 0
    ),1,
0

    
)

Still filtering for columns with a value of 1 in the filter.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.