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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Show items with no data only for first hierarchy row level

Hi,

 

Can't seem to find a good solution to this problem. I have a matrix with three levels of rows. For my test I have called it level1, level2 and level3. 

 

These can be found from two different dimension tables. What I'm curious about is if it is possible to show all level1 items, event when there is no data with related values to it?

I have tried to use the "Show items with no data" function, but this gives me all rows for every hierarchy level, which I do not want. 

To better show my issue I'm adding two pictures of my model and my matrix tables, one that includes all my level1 items, but noe values, which is how all are included, then I add level3 items and corresponding value, then some level1 items dissapear naturally because they are empty. 

 

PBISea_0-1622211370419.png

PBISea_1-1622211397796.png

 

Any suggestions on how to include all level1 items?

 

I have attached my sample file here: https://www.udrop.com/5yhX/showfirsthierarchy.pbix

 

Thanks!

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

One possible workaround is to force level 1 to be non-blank by adding zero at that level.

 

SumVal = SUM ( 'Fact'[Value] ) + IF ( ISINSCOPE ( dim2[level2] ), BLANK(), 0 )

 

This means that if we are at level 2 (or below), then we keep it blank but use zero for the higher levels (level 1).

 

AlexisOlson_0-1622214882950.png

 

If you don't like the zeros instead of blank, you can change the measure display format to show nothing or something else for a zero result.

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

One possible workaround is to force level 1 to be non-blank by adding zero at that level.

 

SumVal = SUM ( 'Fact'[Value] ) + IF ( ISINSCOPE ( dim2[level2] ), BLANK(), 0 )

 

This means that if we are at level 2 (or below), then we keep it blank but use zero for the higher levels (level 1).

 

AlexisOlson_0-1622214882950.png

 

If you don't like the zeros instead of blank, you can change the measure display format to show nothing or something else for a zero result.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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