DAX

[Anonymous]

Hi All,

I have attached 2 images below.

They are both in the same table, just different regions. The problem is, for the region EMEA, I need to apply exchange rates.  Hence I have used the formula below:

R+ Order Value = IFERROR(LOOKUPVALUE('R+ Orders'[Material Value in-week],'R+ Orders'[PartPlantbyYearWeekNum],'Part File'[PartPlantbyYearweekNum])/'Part File'[Exchange Rates],BLANK())

There is some problem with the formula because, there is no exchange rate for NA and hence R+ Order value column goes blank. 

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?

Any help would be appreciated!

Thank you!

[v-xulin-mstf]

Hi @Anonymous,

Could you provide your pbix file after removing sensitve information?

I will check it in my environment.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[goncalogeraldes]

Hello there @Anonymous ! If I understood your problem correctly, I think solution may work:

R+ Order Value = 
IF(
'R+ Orders'[PartPlantbyYearWeekNum] in { "EMEA", "NA"},
DIVIDE( 'Part File'[PartPlantbyYearweekNum] , 'Part File'[Exchange Rates] ),
BLANK()
)

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]

Hi @goncalogeraldes ,

Thank you!

I applied the formula. Now itseems to be blank for both NA and EMEA.

[goncalogeraldes]

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:

R+ Order Value = 
IF(
'R+ Orders'[Region] in { "EMEA", "NA"},
DIVIDE( 'Part File'[PartPlantbyYearweekNum] , 'Part File'[Exchange Rates] ),
BLANK()
)

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]

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]

In that case try using the RELATED() function since it is more efficient and has a better performance than the LOOKUPVALUE(). Something like:

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()
)

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]

Thank you! @goncalogeraldes 

But I am getting an error.

[goncalogeraldes]

@Anonymous do you have a relationship between the to table that are being used?

[Anonymous]

@goncalogeraldes the current table name is Part file.

I have the R+ order value  in the R+ order table.

I want R+ order value in the part file. hence doing lookup.

Now I want to apply the exchange rate to R + order value, EMEA region.

Both the tables have PartPlantbyYearWeekNum.