How to collapse a matrix and have values appear blank

[StefArroyo]

Hello!

I am wondering if there is a way to collapse rows in a matrix and have all of the values instead of an aggregation, appear blank or cleared? 

[Anonymous · Accepted solution]

Hi @StefArroyo   I was actually wanting the same thing and found a solution for this!

Try the following measure and add this as a value in your matrix:

I based this measure formula off of @v-jingzhang  image but you can change it to suit your needs.

Measure = IF (ISINSCOPE(Table[Product]),SELECTEDVALUE(Table[First Color]), BLANK()).

[Anonymous]

Hello, I was able to create this measure and add it as a value in my matrix, but how do I get it to apply to my original values that I was wanting to hide? It currently just shows as an extra column next to my other values in the matrix visualization.

[v-jingzhang]

Hi @Anonymous 

You need to use @Anonymous 's measure to replace your original value in the matrix. Is your original value a measure or a column field? If it's a measure, you can add this measure. 

New Measure = IF ( ISINSCOPE ( 'Table'[second hierarchy] ), [Original Measure], BLANK () )

If the original value is a column, apply the correct aggregation type to the column in the new measure. I use MAX for example in below DAX. 

New Measure =
IF (
ISINSCOPE ( 'Table'[second hierarchy] ),
MAX ( 'Table'[Original column] ),
BLANK ()
)

Best regards,

Jing

[StefArroyo]

@amitchandak 

So, this is just a random example, but is there a way to essentially make all of the values of the columns blank while the rows are collapsed?

[v-jingzhang]

Hi @StefArroyo , you can format the subtotals' font color consistent with the background color to hide them.

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@StefArroyo , I am not sure what you want to show here. I doubt you can collapse the last level. means one level would be expanded.

What you want to achieve here.

In case you need to hide show, you can use bookmarks

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive