Forum Discussion

Shelley-Baynton's avatar
Shelley-Baynton
Icon for Helper I rankHelper 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 very new to DAX so not sure where to look, I think it would be an IF AND MATCH statement but not sure if there is a better way to get the results I need.

 

Thanks in advance 😁

  • 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

5 Replies

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper 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