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

[Shelley-Baynton]

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]

Hi @Shelley-Baynton ,

Does this have to be done in DAX, or can it be done in Power Query?

Pete

[Shelley-Baynton]

Hi @BA_Pete 

Yeah it doesn't need to be DAX it could be in Power Query 

[BA_Pete · Accepted solution]

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]

Works perfectly! Thank you 😀

[Shelley-Baynton]

Okay. That makes sense. Let me give it a try and see what happens.

Thanks 🙂