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.
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.
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.
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