Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pbi_dot_edu
Frequent Visitor

Removing Blank Rows from a Matrix Without Removing Group

Hello all,

 

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

pbi_dot_edu_0-1729607446028.png

 

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 

1 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. 😊

ghoshabhijeet_1-1729633715729.png

 


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 👍

 

View solution in original post

6 REPLIES 6
ghoshabhijeet
Solution Supplier
Solution Supplier

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

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 

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.

Ashish_Mathur_0-1729643194494.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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. 😊

ghoshabhijeet_1-1729633715729.png

 


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 👍

 

@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.

@pbi_dot_edu  You're most welcome. 😊🙏

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.