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.

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

When I tried IF(ISBLANK(SUM(ResubmissionSpend)), SUM(Spend), SUM(ResubmissionSpend)) which is the formula we would use in Excel when the data is in 1 table but the sum for total spend didn't sum up correctly.

Any idea why this is please? Thanks.


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

    AntrikshSharma Will the DAX you provided previously work across 3 tables please? If not, what could I do to make it work please? Thanks.