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:

     


     
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Xavianna ,

 

Based on your additional description.

First, You can create a relationship by Supplier name and then create a table like "Summary".

And I create the DAX expression, you may check the result below:

 

 

 

Measure =
VAR _ISTURE =
    CALCULATE (
        SUMX ( 'Table', 'Table'[Resubmitted Spend] ),
        FILTER ( 'Table', 'Table'[Resubmitted Spend] > 0 )
    )
VAR _ISNOT =
    CALCULATE (
        SUMX ( 'Table', 'Table'[Original Spend] ),
        FILTER ( 'Table', ISBLANK ( 'Table'[Resubmitted Spend] ) )
    )
RETURN
    _ISNOT + _ISTURE

 

Perhaps this will work, and if you have any questions, please provide me with more information to make sure we can better solve the problem for you!

 

An attachment for your reference. Hope it helps.

 

Best regards,


Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

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

Anonymous Thank you so much for your help with this.

 

I applied the measure you recommended and it works with some (highlighted green) but not others (highlighted yellow). Any idea why this is please?

 

Thanks again in advance :).

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Xavianna 

     

    I wonder if there may be other undisplayed data in your table, could you please replace SUMX with MAXX in the expression, if this fails to solve the problem, could you please provide me with a simple sample (please note that you are masking sensitive information)

     

    Best regards,

    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.