Help to get 0 number for blank with Hierarchy Table

[conniedevina]

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.

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

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

[selimovd]

Hey @conniedevina ,

what exactly do you want to archive?

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

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[conniedevina]

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.

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.

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

Hope this help to explain my issues.

Thank you!

[selimovd · 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: 

And this is how it will look like:

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[conniedevina]

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!

[yakovlol]

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