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

[PierrreVelraeds]

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

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

[v-jingzhang]

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. 

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. 

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

[PierrreVelraeds]

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.

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.

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?

[Whitewater100]

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!

[PierrreVelraeds]

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.