Forum Discussion

Anonymous's avatar
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's avatar
    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
SpartaBI
Community Champion
4 years ago

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