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

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.