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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
pasit
Frequent Visitor

undefined

Hi,
 
I'm working with Power BI report and I have problem to remove blank rows from matrix .. I have product hierarchy that is inconsistent. 
 
Hierarchy goes for example so that
 
- Main product
          - Component
          - Sub product 1
                    - Component 3
          - Sub product 2
                    - Component 1
                              - Sub component 1
                                        - Sub component 6
                   - Component 2
 
And so on .. there can be 7 level in hierarchy
In the matrix are 10 different measures, like sales, last year sales, coming sales, gross margin ... 
 
I'm not able to provide sample data more closely, but is there any easy way to hide blank rows from the matrix because although measures does not return any calculated data hierarchy is always shown to the lowest level .. and if I try to filter data from some empty hierarchy level, it removes all rows from the matrix that level even there would be some data ... this is very confusing. The requirement is to use matrix, but is there any other way to show this kind of hierarchcal data .. treemap is not good choise
 
Pasi
1 REPLY 1
Sahir_Maharaj
Super User
Super User

Hello @pasit,

 

1. Create a new calculated measure that checks if any of the measures at the lowest level of your hierarchy has a value.

 

ShowRow = IF(ISBLANK([Sales]), BLANK(), 1)

 

2. Add the new calculated measure to your matrix alongside your other measures.

3. Select the matrix visual, go to the "Format" pane, and navigate to the "Conditional formatting" section. Choose "Background color" under "Font color" and select the "Field value" option. In the "Based on field" dropdown, choose the calculated measure you created in step 1 (ShowRow). Set the formatting options for the blank rows, such as setting the font color to match the background color or choosing a different background color to make the row appear hidden.

 

Should you need further assistance please don't hesitate to reach out to me.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors