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.

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

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

  • arjunislearning's avatar
    arjunislearning
    Frequent Visitor
    2 years ago

    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.