Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Using COALESCE breaks joins

Hi, I've a stupid problem: when I try to use COALESCE on a mesure in order to force a value when there is no data, I end up with a cartesian product when there are multiple tables linked. Sim...

Need Help

Anonymous
4 years ago
Hi Anonymous ,

I think your table visual should be expanded by relationship. I suggest you to try to use virtual table in your code.

New Measure:

ImpQty = VAR _SUMMARIZE = ADDCOLUMNS ( Country, "Capacity", RELATED ( Capital[Capital] ), "Qty", CALCULATE ( SUM ( 'Import'[Qty] ) ) + 0 ) RETURN SUMX ( _SUMMARIZE, [Qty] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
test_coalesce.pbix37 KB

CNENFRNL

Community Champion

4 years ago

DAX is simple, but NOT easy. Even a seemly-easiest measure like yours involves some intricacies under the hood. In your case, no joins were broken at all. It's just the instrinsic mechanism of filter mechanism.

whatever column you put in the viz, it acts as a filter; (a side note: the blank cell of Capital[Capital] kicks in due to Referential Integrety Violation in relation to relationship Country[ID] 1:1 Capital[ID])
filters propagate along the direction of relationship
a measure evaluates under the stacked effects of all possible filters
SUM/MAX/MIN(_table[Column]) ... are sytanctic sugars for SUMX/MAXX/MINX(_table,_table[Column])

after all these complex preceding steps, it finally arrives to evaluate SUMX('Import','Import'[Qty]). When filtered 'Import' is empty, the measure evaluates to empty and it's removed from the viz automatically by the engine.

But you use COALESCE to return 0 by force.