Forum Discussion

Shelley-Baynton's avatar
Shelley-Baynton
Helper I
4 years ago
Solved

DAX - IF & MATCH STATEMENT [COMPARE TWO DATASETS AND RETURN VALUE IF VALUE IS MATCHED]

Hello,   I need some help on clashing two sets of data, they are in different formats (see below).  What I need to do is return a value if Data Set 2 is on Date Set 1 and vice versa. I'm ver...
  • BA_Pete's avatar
    BA_Pete
    4 years ago

     

    Cool.

    So you could create a new custom column in Dataset1 called 'itemList' (or similar) which is just:

    = {[From]..[To]}

    Then expand this column to new rows.

     

    Once you've done this, you can do a FULL OUTER merge on:

    Dataset1 [ID], [itemList] = Dataset2 [ID], [From]

    Expand DS2 [ID] and [From] keeping the original table name in the new column names.

     

    You'll end up with a table that has complete rows where the tables match, and null values on either side where the tables don't match.

     

    Hope this makes sense.

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
4 years ago

 

Cool.

So you could create a new custom column in Dataset1 called 'itemList' (or similar) which is just:

= {[From]..[To]}

Then expand this column to new rows.

 

Once you've done this, you can do a FULL OUTER merge on:

Dataset1 [ID], [itemList] = Dataset2 [ID], [From]

Expand DS2 [ID] and [From] keeping the original table name in the new column names.

 

You'll end up with a table that has complete rows where the tables match, and null values on either side where the tables don't match.

 

Hope this makes sense.

 

Pete

Shelley-Baynton's avatar
Shelley-Baynton
Helper I
4 years ago

Works perfectly! Thank you 😀