Forum Discussion

zervino's avatar
zervino
Icon for Helper I rankHelper I
2 years ago
Solved

Matrix visual, don't show rows with empty name

I have created a matrix visual and currently looks like this:

 

The source data has 3 columns: Team, Block, Person and then values for years.

My issue is that not all rows in the source data have a Team, Block or Person values.

Some have team; team and block; team, block and person; and others have none.

 

Is there a way to explode the matrix to show rows containing non-empty names in the column?

 

So something like this:

 

 

(Also removing the blank row at the top).

 

Right now I need to explode/collapse row by row to make it look nice, but that's not functional to publish for other users.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi zervino ,

     

    Thanks for the reply from ajohnso2 , please allow me to provide another insight: 

     

    According to your description, I created these data.

     

    1. Create a measure to determine whether the three-level names are all empty. If they are all empty, return 999, otherwise return the value to be displayed before.

    MEASURE = 
IF (
    MAX ( 'Table'[Team] ) = BLANK ()
        && MAX ( 'Table'[Block] ) = BLANK ()
        && MAX ( 'Table'[Person] ) = BLANK (),
    999,
    SUM ( 'Table'[value] )
)

     

    2. Filter the results where the measure is not 999 in the filter, and put the measure into the matrix values ​​position.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

3 Replies

  • ajohnso2's avatar
    ajohnso2
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    you can add a filter to the visual in the filter pane on LOB is not blank?

    • zervino's avatar
      zervino
      Icon for Helper I rankHelper I
      2 years ago

      The matrix visual has 3 levels: Team, Block, Person.

       

      Applying that filter works for the rows that have no name in any of the levels.

      But I cannot use that kind of filter for cases for rows that have Team name but no Block.

      In my example, Team C has no Block, so if I apply the filter by Block as well, all Team C would disappear from the visual.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi zervino ,

     

    Thanks for the reply from ajohnso2 , please allow me to provide another insight: 

     

    According to your description, I created these data.

     

    1. Create a measure to determine whether the three-level names are all empty. If they are all empty, return 999, otherwise return the value to be displayed before.

    MEASURE = 
IF (
    MAX ( 'Table'[Team] ) = BLANK ()
        && MAX ( 'Table'[Block] ) = BLANK ()
        && MAX ( 'Table'[Person] ) = BLANK (),
    999,
    SUM ( 'Table'[value] )
)

     

    2. Filter the results where the measure is not 999 in the filter, and put the measure into the matrix values ​​position.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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