cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PierrreVelraeds
Frequent Visitor

Showing 0 instead of nothing (null), adding COALESCE breaks filtering (hierarchy)

I have a Measure in a matrix showing a mix of actual values and (null) values. Reason is that not for all lines the metric exists. 

Actual Open Purchase Cost = SUM('Fact PurchaseTransactionValues'[ActualOpenPurchaseCost])

The matrix also has a hiearchy: Customer Transation - Customer Transaction Line

PierrreVelraeds_0-1642176794886.png

 

Desire is to replace (null) with 0. So I added COALESCE to the defintion in a new measure

Actual Open Purchase Cost 2 = COALESCE(SUM('Fact PurchaseTransactionValues'[ActualOpenPurchaseCost]); 0)
 
But when I add this new measure to the matrix the relation between the fact table and the used dimensions seems lost: I get all lines under eacht Customer Transaction, not only the lines for that Customer Transaction
PierrreVelraeds_1-1642176819169.png

 

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @PierrreVelraeds 

 

It would be better to select both Product and Description columns from Dim table into the matrix as Rows fields. Then you will not have this problem. 

22011902.jpg

 

The cause is the cross-filter direction of the relationship between Dim table and Fact table. In below image, you have a single cross-filter direction relationship from Dim Prod to Fact table. This means that Dim Prod table can filter Fact table, but Fact table cannot filter Dim Prod table. When you put ProductName from Fact table into the matrix, and put Description from Dim table into it at a lower hiearchy level than ProductName, ProductName is not able to filter Description. As a result, it display all rows. 

22011903.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang,

 

I tried changing the cross-filter direction from single->both in the model but that does not help. Am I doing something wrong?

We have a datawarehouse which is set up with Fact and Dimension tables in which dimensions are coupled via star schema or snowflake. The fact tables only contain metrics and surrogate keys to the dimensions. Adding chracteristics of dimensions to the fact table is no option.

In the example I added a Dim ProductGroup which is also coupled to the fact table. This represents my situation better.
The hiearchy is OK untill the measure with coalesce function or + 0 is added.

PierrreVelraeds_0-1643021381696.pngPierrreVelraeds_2-1643021505092.png

 

It seems that using coalesce or +0 creates a value for every possible combination disregarding the combinations in the data or relations in the model. Somehow is logical as there is always a value, but I am wondering what the use case is for this behaviour.

 

Workaround for this is to create an additional measure that determines if all values used are blank. This meaure can then be used to filter out the combinations where all values are blank. But this is can make maintenance harder and time consuming. E.g. when adding or removing a measure to a visual this extra measure needs to be updated too.

PierrreVelraeds_0-1643029481318.png

 

See Example issue NULL and COALESCE 2.pbix for used examples and measures.

 

Is there a simpler solution that only replaces the blanks with 0?

PierrreVelraeds_3-1643028939276.png

Whitewater100
Super User
Super User

Hello:

I think you can just add + 0 to the end of your measure and you will get a 0 vs. null.

I hope this helps!

Hi,

Unfornunately that gives the same result.

I tried to reproduce in a simple example: Example issue NULL and COALESCE.pbix 

It looks that when the hiearchy in de matrix has fields from more than two other (Dim) tables the hierachy shows alle values instead of values filtered by the hierarchy.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors