Forum Discussion

Xavianna's avatar
Xavianna
Icon for Helper I rankHelper I
2 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
    2 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
2 years ago

We tend to receive data from different sources and they have named the company names slightly different. For example Microsoft can be called Microsoft, Microsoft Inc, Microsoft LTD, Microsoft Limited, etc. So the 3rd table is used to unify these names from the different tables and this is the table we use to show the final data.

jmlesteves's avatar
jmlesteves
Frequent Visitor
2 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
    2 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
      2 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
        2 years ago

        Thank you so much 🙂