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 issues (Table 2). Some suppliers may not have to resubmit their data if there were no issues highlighted.

I have been asked to merge Table 1 and Table 2 where if Table 2 is empty (meant the supplier hasn't resubmitted their data), we are to use the data from Table 1 and I would like the Total Spend to sum up correctly.

 

Example data below:

Table 1

 

Table 2

 

Summary

 

I tried IF(ISBLANK(SUM(ResubmissionSpend)), SUM(Spend), SUM(ResubmissionSpend)) but the sum for total spend didn't sum up correctly to £15000, it was showing £9000 for some reason.

 

  • 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:

     


     

13 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Xavianna Since both tables are the same table you should perform the MERGE in Power Query, there is no point in loading both tables.

     

    Table 1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1RwVNJRMjYwUIrVQYg5AcVM0cScgWKGOgZooi4g3RiiriC1aGJuQDEjiMpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Supplier Name" = _t, Spend = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Supplier Name", type text}, {"Spend", Currency.Type}})
in
    ChangedType

     

    Table 2:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1RwVNJRMtYxMDBQitVBiDoBRU0xRJ2BoigCLugCrkABQwx9bmBlsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Supplier Name" = _t, Spend = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Supplier Name", type text}, {"Spend", Currency.Type}})
in
    ChangedType

     

    Table to load in the model:

    let
    MergeTables = Table.NestedJoin (  Table1, {"Supplier Name"}, Table2, {"Supplier Name"}, "Table2", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn ( MergeTables, "Table2", {"Spend"}, {"Spend.1"}),
    Rename = Table.RenameColumns ( Expand,{{"Spend", "Original Spend"}, {"Spend.1", "Resubmitted Spend"}}),
    Correction = Table.AddColumn ( Rename, "Total Spend", each if [Resubmitted Spend] = null then [Original Spend] else [Resubmitted Spend], Currency.Type )
in
    Correction
    • Xavianna's avatar
      Xavianna
      Icon for Helper I rankHelper I
      3 years ago

      AntrikshSharma Thank you so much for your response.

       

      Ideally, both tables have the same supplier names, unfortunately, there's a 3rd table used to unify both tables to connect to each other in a relationship. I didn't realise that this information is required as it does make it more complex.

      What can you advise in this situation please? Thanks!

      • AntrikshSharma's avatar
        AntrikshSharma
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Why do you have to connect these 2 tables? They should be a single table. The third table is Dimension?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 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
      3 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
        3 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.