Using COALESCE breaks joins

[Anonymous]

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.

Simple example :

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!

[CNENFRNL]

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.

1. 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])
2. filters propagate along the direction of relationship
3. a measure evaluates under the stacked effects of all possible filters
4. 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.

[arjunislearning]

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]

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.

[v-rzhou-msft · Accepted solution]

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.

[SpartaBI]

@Anonymous try this:

ImpQty = COALESCE(CALCULATE(SUM('Import'[Qty])), 0)

[Anonymous]

Thank you for the suggestion but I got the same result 😞

I attached the pbix in the thread

https://filetransfer.io/manage-package/CZeGEQC0

[SpartaBI]

@Anonymous you know what, before solving this with maybe a not necessary DAX complex. Why do you have table country and table Capital as two different tables?
If you have a 1:1 relationship between 2 tables this is 99% means they need to be in 1 table. Is there a reason you didn't do that? If not, this is a very important rule to follow.
Also, I wasn't able to download the file you shared. I can hande with the tabled you put in the first message, but first just let me know my question about the 1:1 split you did. 

[Anonymous]

Actually, it's because the original model is far more complex and my first idea was to keep the tables separated in order to keep the data in their respective table : my data is about employee ; I have data regarding formation, rh managment, etc.

It's true that some of them have a 1:1 relationship, I didn't think it could be an issue.

I also didn't knew 1:1 relationship was something to avoid!

Moreover, I also have others 1:N relationships and I'm not sure I'll not have the same issue if I use COALESCE in this case.

I'll have a look at my example and see if I have the same issue with 1:N relationship tables.

[SpartaBI]

@Anonymous it's ok, it's not neccesay the reason, but you need to merge this situations to 1 table unless there are security constraints or maybe one in direct query and the other in import. 1;m is what you want to have, but 1;1 is just mean it should be the same table. Good model= less complex dax to achieve the same result

[SpartaBI]

@Anonymous haha ok, I just tried a quick win. Will take your tables and reproduce in a PBIX and comeback with an answer