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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
conniedevina
Helper I
Helper I

Help to get 0 number for blank with Hierarchy Table

Hi All,

 

I have problem with displaying blank values as 0.

My measure is using 

 

Actual M = SUM(Actual[Actual])+0

 

It showing 0 but it's makes my table become wrong and showing like below, somehow my level 2 is become repeated.

conniedevina_0-1634029730931.png

 

My Relationship is looks like this, while my Lv2 taken from Table and Lv1 from CoA, the Measure is taken from Actual

conniedevina_1-1634029905490.png

 

I attach the Power BI file here, any help will be very appreciated!

https://drive.google.com/file/d/1W-I3gm00Dyyv-Ak-ZWBW9cFrGGdzUGBf/view?usp=sharing

1 ACCEPTED SOLUTION

Hey @conniedevina ,

 

in general your data model has room for improvement. Try to focus on one fact table and multiple dimensional tables. Maybe this article will help you:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

 

In your case the "G"-row is just shown because you explicitly selected to show blank items. If you go to the settings of Lvl2 and disable "Show items with no data", then the empty row will not be shown: 

 

selimovd_1-1634047395134.png

 

And this is how it will look like:

selimovd_0-1634047374543.png

 

The first behavior from the DAX engine was totally correct. If there is no value (=blank), then the row is usually hidden. When you replace that blank with a zero, all rows with no values will be shown. But in your case this happened just because of the data model and the setting to show empty rows.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

View solution in original post

5 REPLIES 5
selimovd
Super User
Super User

Hey @conniedevina ,

 

what exactly do you want to archive?

Your measure shows blanks as zero, as you asked for.

 
Best regards
Denis
 

Hi @selimovd 

 

The right structure as the picture is the right side.

While the left side somehow the relationship is messed up and my Cost and Sales showing all A to G.

 

The expected I just want to put 0 in this blank one.

conniedevina_0-1634030506788.png

 

But somehow when I use the measure +0 my table become messed up like this, and actually in the relationship there's no A,B,C,D,E under my Cost. Because my measure +0 somehow it showing A,B,C,D,E under my Cost.

conniedevina_1-1634030547592.png

 

It would be nice if you check the .pbix file for the relationship.

Hope this help to explain my issues.

 

Thank you!

Hey @conniedevina ,

 

in general your data model has room for improvement. Try to focus on one fact table and multiple dimensional tables. Maybe this article will help you:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

 

In your case the "G"-row is just shown because you explicitly selected to show blank items. If you go to the settings of Lvl2 and disable "Show items with no data", then the empty row will not be shown: 

 

selimovd_1-1634047395134.png

 

And this is how it will look like:

selimovd_0-1634047374543.png

 

The first behavior from the DAX engine was totally correct. If there is no value (=blank), then the row is usually hidden. When you replace that blank with a zero, all rows with no values will be shown. But in your case this happened just because of the data model and the setting to show empty rows.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

Hi @selimovd 

 

Thank you so much for your explanation, now we change the data structure and now it's showing ok. I understand with the DAX Behaviour. 

 

Thank you!

Hello @conniedevina how did you solve this problem? have a simular sitiation 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.