DAX

Question

Hi All,&nbsp;I have attached 2 images below.&nbsp;They are both in the same table, just different regions. The problem is, for the region EMEA, I need to apply exchange rates.&nbsp; Hence I have used the formula below:&nbsp;R+ Order Value = IFERROR(LOOKUPVALUE('R+ Orders'[Material Value in-week],'R+ Orders'[PartPlantbyYearWeekNum],'Part File'[PartPlantbyYearweekNum])/'Part File'[Exchange Rates],BLANK())&nbsp;There is some problem with the formula because, there is no exchange rate for NA and hence R+ Order value column goes blank.&nbsp;&nbsp;I need to correct the formula in a way, where ther exchange rate is applied to EMEA and NA should not have blank values.How can I correct the formula above?&nbsp;Any help would be appreciated!&nbsp;Thank you!&nbsp;

goncalogeraldes · Answer

Hello there&nbsp;Anonymous&nbsp;! If I understood your problem correctly, I think solution may work:&nbsp;R+ Order Value = 
IF(
'R+ Orders'[PartPlantbyYearWeekNum] in { "EMEA", "NA"},
DIVIDE( 'Part File'[PartPlantbyYearweekNum] , 'Part File'[Exchange Rates] ),
BLANK()
)&nbsp;&nbsp;Hope this answer solves your problem! If you need any additional help please tag me in your reply.If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍Thanks!Best regards,Gonçalo Geraldes

anonymous · Answer

Hi&nbsp;goncalogeraldes&nbsp;,&nbsp;Thank you!I applied the formula. Now itseems to be blank for both NA and EMEA.&nbsp;&nbsp;

goncalogeraldes · Answer

I've had a look at your table and isnt it supposed to check the values in the "Region" column rather than the "PartPlantbyYearWeekNum"? Like this:&nbsp;R+ Order Value = 
IF(
'R+ Orders'[Region] in { "EMEA", "NA"},
DIVIDE( 'Part File'[PartPlantbyYearweekNum] , 'Part File'[Exchange Rates] ),
BLANK()
)&nbsp;Hope this answer solves your problem! If you need any additional help please tag me in your reply.If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍Thanks!Best regards,Gonçalo Geraldes

anonymous · Answer

Thank you!I actually get the R+ order value from an other table. I have done a lookup in the above formula I have applied. And I need to apply the exchage rate, i.e., divide R+ order value(which i have got by applying lookup) by exchage rate. And I only need to apply that, i.e., divide by exchange rate for EMEA region and not NA.

goncalogeraldes · Answer

In that case try using the RELATED() function since it is more efficient and has a better performance than the LOOKUPVALUE(). Something like:&nbsp;R+ Order Value = 
var _plant = RELATED('R+ Orders'[PartPlantbyYearWeekNum])
var _divide = DIVIDE( 'Part File'[PartPlantbyYearweekNum] , 'Part File'[Exchange Rates] )

return
IF(
_plant in { "EMEA", "NA"},
_divide,
BLANK()
)&nbsp;Hope this answer solves your problem! If you need any additional help please tag me in your reply.If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍Thanks!Best regards,Gonçalo Geraldes

Forum Discussion

DAX

9 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DATEDIFF DAX