lookupvalue function with 2 columns?

[unclejemima]

I've got a table called trandata what has a column called CurrencyDate, and CurrencyCode

There is another tabled called CurRates what has a RateDate and CurrencyCode, and the value I want called SellRate

The CurRates table has unique values in it for each CurrencyCode (values 1-5) and then the Date (RateDate) to give the sellrate (example 1.45)

I'm trying to make a calculated column in trandata called CurrencyRate using the lookupvalue function to retreuve the CurRates[SellRate]...but because I'm trying to match two columns I'm not sure how to write the DAX.

I'm trying to lookup the trandata[CurrencyDate] and trandata[CurrencyCode], reference the CurRates[RateDate] and CurRates[CurrecnyCode] to then display the CurRates[SellRate] in Trandata calculated column CurrencyRate.

Can anyone elighten me on a formula for this?

Thanks!

[Anonymous · Accepted solution]

Could you create a concatinated column in your CurRates table that brings together the RateDate and CurCode?  Then when you do your Lookup, make the lookup value a concatination of CurrencyDate and CurrencyCode?

You might need to check that they format to create the same string, but it should work.

[unclejemima]

I was thinking this too...but because its a whole number and date value I thought it might give an odd result.  I'll try it out 🙂

Thanks!!