Hide empty rows in Matrix

[Shamatix]

Hi there,

So my question is fairly simple.

I have added a Row to a Matrix that does not always contain a value, however it still shows up?

How can I hide it, if it's empty/blank/null?

Best regards

[v-jingzhang]

Hi @Shamatix 

I'm curious about whether Allison's method work for you. I had this problem before but I thought a higher blank hierarchy row is not able to be hidden when its child rows have values. So I didn't find solutions then. This method seems to use non-blank lower hierarchy values to replace higer blank values. If one blank row has multiple child values, will it have problem?

Best Regards,
Community Support Team _ Jing

[AllisonKennedy]

@Shamatix  You can add a filter to 'filters on this visual' for the same field that is in the rows of the matrix to exclude blanks values. 

Is that what you're asking?

[Shamatix]

Hi, 

Thanks for your answer, but not exactly.

If I do that it removes all the rows above/below (both green/red below gets removed if I do your suggestion)

I want to keep the green rows and only remove the red.

[AllisonKennedy]

@Shamatix  You can't keep the red without removing the green below it - unless you move the green below it into a category. Are you using multiple tables? You could replace the blank with another value, but if the blank comes from relationships you'll need to append a new row to the dim table: https://excelwithallison.blogspot.com/2020/07/what-does-this-blank-mean.html 

[Shamatix]

If I do not add the "row" I get this result which is how I want it to be if the rows im trying to add now is empty:

However when I add the row and it's empty I get the below result instead?

I hope that made more sense

[AllisonKennedy]

@Shamatix  When you say if you do not add the row, I assume you are meaning that you are adding a field to the 'Rows' section of the matrix visual. Is this field coming from the same table as the field below it in the 'Rows' section? Does this field have blank values sometimes? 

You may be able to look into using the PATH function or doing a pivot or duplicate of your data to transform the tables to cheat the system, but the default behaviour won't allow it.

Can you share a sample report or describe/screenshot your data model please?

[Shamatix]

So I made you some sample data.

The data it self is close but not 1:1 of my data.

It can however reproduce my exact issue.

Red line is the "empty" line I want gone, however I of course dont want "company1" gone, just it to be moved "up"

 

I have uploaded the sample above on this link https://we.tl/t-tjMsIGbZR6

Unsure where else I can upload .pbix files

[AllisonKennedy]

@Shamatix  You could use OneDrive or DropBox to share sample data as well. 

You could create a new calculated column that is similar to this. Create the columns in the table that is on the Many side of the relationship. In the examples below, the columns should be created in the BLUE table:

Field1 Calculated =

RELATED(GreenTable[Field2])

Field2 Calculated =

RELATED(GreenTable[Field2])

Field3 Calculated = 

IF(ISBLANK(BlueTable[Field3]), BlueTable[Field4])

Field4 Calculated = 

IF(ISBLANK(BlueTable[Field4]), RELATED(BlueTable[Field5]))

Then you'll need to adjust your measure if you don't want to drill down to the detail level for those values, so you'll need to add an IF condition into your measure values too...

Not sure if there is a more efficient/elegant way to acheive this

[Shamatix]

Yes by row I mean adding a column to the "Rows" section of the Matrix.

3 of the 4 "rows" are from the same table.

I have tried to illustrate which 3 are from the same table and which arent in the below picture.

So the 3x green rows comes from the same table, the blue section is a row from another table.