Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply

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.

ShelleyBaynton_0-1658843153328.png

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 😁

1 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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

Hi @Shelley-Baynton ,

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @BA_Pete 

 

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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Works perfectly! Thank you 😀

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

 

Thanks 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors