Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Remove rows from a matrix visual

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...
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Anonymous 

    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

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous ,

 

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.