Forum Discussion
Using COALESCE breaks joins
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.
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.