Removing Blank Rows from a Matrix Without Removing Group

[pbi_dot_edu]

Hello all,

I have a matrix visual that currently looks like the following:

Metrics 1-5 are separate measures and I have selected "Switch values to rows".

The issue I am having is that some Categories have values for all 5 metrics (Cat 2) and other Categories only have values for 2 (Cat 1). I would like Metrics 1, 2, and 4 to disappear (rather than showing blank values) for the Categories that have no data for those metrics. So Category 2 should have 5 rows, and Category 1 should have two rows.

"Show rows with no data" is off. That does not fix the issue.

I have tried applying a visual level filter for "Metric 1 is not blank". However, this caused the entire Category 1 to disappear.

Any help would be greatly appreciated.

Sample Data 

[ghoshabhijeet]

@pbi_dot_edu  Thanks for sharing the query. Could you please share the file with some sample data? Thanks!

[pbi_dot_edu]

Hi @ghoshabhijeet ,

I am not able to upload a PBIX file but I have tried to export a link. Can you tell me if it works for you?

Sample Data 

[Ashish_Mathur]

Hi,

You do not need those individual measures.  Just drag the measure field to the row labels (below Category) and drag this measure

Measure = SUM('Query1'[Value])

Delete the measures that you wrote.

Hope this helps.

[ghoshabhijeet · Accepted solution]

@pbi_dot_edu  Thank you for sharing the sample data. I have solved the issue and here is the link to the updated Sample data pbix file. Hope this helps. 😊

URL: https://drive.google.com/file/d/1C6p8GmjUP1gBhjofKNtYn_a3MdD6k-M4/view?usp=sharing

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

[pbi_dot_edu]

@ghoshabhijeet I cannot thank you enough!! This solution worked exactly as expected. Truly appreciative of you taking the time to help me; I never would have thought to use that code.

[ghoshabhijeet]

@pbi_dot_edu  You're most welcome. 😊🙏