Power BI Dax - Merging 2 Data Tables With IF Condition

[Xavianna]

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.

[Anonymous]

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]

@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]

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.

[AntrikshSharma]

@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]

@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]

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

[Xavianna]

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 · Accepted solution]

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]

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]

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]

Thank you so much 🙂

[Xavianna]

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]

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