Forum Discussion

Xavianna's avatar
Xavianna
Icon for Helper I rankHelper I
3 years ago
Solved

Power BI Dax - Merging 2 Data Tables With IF Condition

Hello all,   Thank you in advance for helping with this query.   I was given a set of data (Table 1) and then the suppliers were asked to resubmit their data due to discrepancies/data quality iss...
  • jmlesteves's avatar
    jmlesteves
    3 years ago

    Hi,
    If I understand well your model you have a 3rd table - I'll call it Table3 - that, as a dimension, has an M:1 relationship with Table1 and Table2, like in the figure:

    In this case, try something like this:

    Spend Or Resubmission =
    SUMX(
        VALUES( Table3[Supplier Name] ),
        VAR _Spend = CALCULATE( SUM( Table1[Spend]) )
        VAR _SpenResubmission = CALCULATE( SUM( Table2[Spend] ) )
        RETURN
        COALESCE( _SpenResubmission, _Spend )
    )
    and you will get this:

     


     
jmlesteves's avatar
jmlesteves
Frequent Visitor
3 years ago

Hi,
If I understand well your model you have a 3rd table - I'll call it Table3 - that, as a dimension, has an M:1 relationship with Table1 and Table2, like in the figure:

In this case, try something like this:

Spend Or Resubmission =
SUMX(
    VALUES( Table3[Supplier Name] ),
    VAR _Spend = CALCULATE( SUM( Table1[Spend]) )
    VAR _SpenResubmission = CALCULATE( SUM( Table2[Spend] ) )
    RETURN
    COALESCE( _SpenResubmission, _Spend )
)
and you will get this:

 


 
Xavianna's avatar
Xavianna
Icon for Helper I rankHelper I
3 years ago

Oh my word!! That is exactly what I have been trying to do. I was about to create a sample PBIX to separate the 3 tables. Thank you so very much 🙂

 

Could you please explain what COALESCE does in this instance? Thanks 🙂

  • jmlesteves's avatar
    jmlesteves
    Frequent Visitor
    3 years ago

    Hi,
    DAX Guide has the perfect explanation and examples of the COALESCE function(and for all other DAX functions as well):
    https://dax.guide/coalesce/ 

    Regards

    • Xavianna's avatar
      Xavianna
      Icon for Helper I rankHelper I
      3 years ago

      Thank you so much 🙂