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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mterry
Helper V
Helper V

Merging with criteria

I have a table with financial information that has multiple dates in it, and another table with conversion rates by month that I would like to merge to the financial table. The problem is that I would like to use some conditions for merging: if there is a project end date in the finance table, then I'd like to use that month for the conversion, otherwise I'd like to use the most recent conversion rate available. What's the most efficient way to do that that will update with data refreshes? Thanks in advance. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@mterry , In such case we create a calculated column

new column in table 1
=
var _date = maxx(filter(Table2, table2[Date] <= table1[Date]), Table2[Date])
return
maxx(filter(Table2, table2[Date] =_date), Table1[Exchange Rate])

 

we can add more condition like from currency , to currency

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@mterry , In such case we create a calculated column

new column in table 1
=
var _date = maxx(filter(Table2, table2[Date] <= table1[Date]), Table2[Date])
return
maxx(filter(Table2, table2[Date] =_date), Table1[Exchange Rate])

 

we can add more condition like from currency , to currency

Ok thanks for the reply, I'll try that. Would creating a calculated column make more sense than a DAX measure to achieve the same thing?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.