Forum Discussion
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.
Table Country :
| ID | Name |
| A | Country A |
| B | Country B |
| C | Country C |
| D | Country D |
Table Capital :
| ID | Capital |
| A | Capital of A |
| B | Capital of B |
| C | Capital of C |
Table Import :
| ID | Product | Quantity |
| A | X | 50 |
| A | Y | 160 |
| A | Z | 70 |
| C | X | 250 |
| C | Z | 10 |
Here is the model generated by PowerBI :
Here is the a table in the report with ID, Capital, Name and Qty (which is correct) :
I'd like to have default values when there is no data (not necessary zero but I'll use zero in this example), my first idea was to create a measure with COALESCE :
ImpQty = COALESCE(SUM('Import'[Qty]), 0)
But if I add this measure to the table above, I end up with a cartesian product with the Capital table :
Setting the relation between Import and Country as double-sided doesn't change this.
I presume there is a simple solution but couldnt' find it... Any idea please?
Thanks!
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
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.
I never thought DAX was easy, on the contrary 😕
As Capital doesn't have as many lines as Country, should the relationship be *:1 ? (I can have no Capital for one Country). I must admit that PowerBI suggested the 1:1 and I didn't think about it.
I did try that and it actually removes the rows with blank Capital but I still have 3 rows par Country.
I think I understand *why* I have this result (even if I use "breaks joins" in the title which is not exactly what's done).
But I don't know *how* to deal with this.
I could use a measure like this one :
ImpQty = IF(COUNTROWS(Capital)=0, BLANK(), COALESCE(SUM('Import'[Qty]), 0))It will return BLANK when the SUM is BLANK "because" of a missing Capital but in a more complex table, it will be a nightmare to setup. And Country D will not be displayed as it has no capital.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow! what an explanation CNENFRNL !
Could you please share more such articles or tips to make my understanding onthis nature of DAX processing.
I consider myself as a sql developer trying to understand DAX , who fails most of the time.
Anonymous try this:
ImpQty = COALESCE(CALCULATE(SUM('Import'[Qty])), 0)Thank you for the suggestion but I got the same result 😞
I attached the pbix in the thread
Anonymous haha ok, I just tried a quick win. Will take your tables and reproduce in a PBIX and comeback with an answer
