Condition Statement

[karabryan]

I've seen many examples of how to handle currency but our company only wants the exchange rate to apply if the source currency is RMB.  Then it should be converted to USD.  The rest of the transactions, even if they aren't USD should be treated as if they are USD.  I've spent way more time on this than probably necessary but here is what I have:

Table Erp CurrExRate holds the exchange rate values for each calendar day.  I have it linked to the Date table and to the Bookings table using the SourceCurrCode.  I created a new column called SourceTarget that is a combination of the Source and Target Curr Code fields.

Table BookingsTerritory holds the daily sales with the source currency code and I added a column that states if the source currency code is RMB, then the currency type should be RMBUSD.  Otherwise, it will be USDUSD.  I did this only to try and create something common between the two tables.

I want to create a new column or measure that states,

New Exchange Rate = If('BookingsTerritory[CurrencyType] = "RMBUSD", 'ERP CurrExRate'[NewExchange], 1.000000.

Basically, I need the daily exchange rate for any RMBUSD transactions but want to use 1.00000 if it is any other type of currency transaction.  We don't convert to USD in that company by default and for the purpose of this report, they need me to.  They don't want to convert any other currency types, though. 

I don't get expected results when I use the condition statement above.  Only some of the values fill in and they aren't correct.  Could someone help with any ideas of a way to accomplish this?  Thank you in advance!

[v-deddai1-msft · Accepted solution]

Hello @karabryan ,

Could you please try to use firstnonblank in your formula like:

LOOKUPVALUE(
    FIRSTNONBLANK(Currency[NewRate],1),
    Currency[NewSourceTarget],
    Booking[NewSourceTarget]
    Currency[Date],
    Booking[Date]
)

If this post helps, then consider Accept it as the solution to help other members find it faster.

Best regards

Dedmon Dai

[MFelix]

Hi @karabryan ,

How are you making the calculation of the measure to have the values converted? Can you share a mockup file?

[karabryan]

What is the best way to do that?  This is one page in a .pbix with 60 tables and many pages of visuals.  Last night I tried to use look-up functions but need multiple nested functions and that didn't work for me as expected.  I also tried to merge the two tables in power query but that also didn't work because the exchange rate table had companies listed in it and although there is a relationship between the two, it didn't match them up correctly.  I'm removing the company info from the exchange rate table because it is irrelevant and seeing if that helps.  I can try to make a mock up file in the mean time if there is a way to isolate just that portion of my project.

[MFelix]

Hi @karabryan ,

Since your model is so large let's take it step by step, can you answer me this:

• What is the measure you are using for making the conversion?
• Do you have many-to-many relationship between the exchange rate table and your fact table?

[karabryan]

I made some changes and now have a table for the CurrExRate that only has the Date, NewSourceTarget(eitherUSDUSD or RMBUSD) and the new rate (1 or the RMBUSD conversion rate).  I created those with conditional columns in PowerQuery.  Then have a table called CurrencyType that is just the RMBUSD and USDUSD currency type values so I can create a relationship to the Booking Territory table without using the many-many relationship. 

ERP CurrExRate Table:

Bookings Territory Table

Relationships (I had tried using just SourceCurrCode and TargetCurrCode earlier after watching a tutorial but that didn't fit my needs).  I have relationship from ErpCurrExRate to the Date table and to the Currency Type Table.  Bookings Territory is related to the Date table, the Company table and the Currency Type. 

I thought I could merge the tables now in Power Query but that didn't work.  I could possible use a lookup now in DAX but I'm not sure how to lookup date and then currency type to get the correct value.

[MFelix]

Hi @karabryan ,

The lookup should look similar to this:

LOOKUPVALUE(
    Currency[NewRate],
    Currency[NewSourceTarget],
    Booking[NewSourceTarget]
    Currency[Date],
    Booking[Date]
)

Be advise that this may need some adjustments.

[karabryan]

It doesn't seem to like me pulling from the Bookings Territory table when I use the lookup function.  I'm adding this as a new column.  Should I have tried using a measure?

[karabryan]

Now this error...

[karabryan]

I deleted and tried re-typing it all out and it isn't bringing back any errors with the formula now, but isn't bringing back results either.  The New Exchange Rate column is blank.

[MFelix]

A measure will not work with the same syntax.

can you share print screens of the exchange and booking table?

[MFelix]

Where are you placing this calculated column? This should be placed in the bookingsteritory or do you want to return the rate in another table?

The parameter that is giving error is the Search value so it's the value you are looking in the other table.

[MFelix]

This error is on a measure correct?

[karabryan]

And I was using a new column - not a measure.  Let me try to switch it to a measure.

[karabryan]

I may have found the issue.  In the Erp CurrExRate table, the NewRate field was formatted as Text for some reason after I brought it back over.  I'm making some changes and will update this if it all works!  Thank you!!

[karabryan]

Unfortunately that didn't work.  Here are my print screens:

The top table is the ERP CurrExRate table.  The New Rate is a decimal number now.  The second table is the BookingsTerritory table with the new column formula showing in the formula bar.  No errors on the formula, but no results, either.

[MFelix · Accepted solution]

Hi @karabryan ,

In order to have a more permanent solution believe you need to do the following:

• Create a measure with the following code:

ExchangeRate = MIN(ExchangeRateTable[ExchangeRate])

• Now create a new measure to make the calculation:

Valueconverted = SUMX(Table, Table[Amount] * [ExchangeRate])

Be aware that you need to have the columns from your many-to-1 relationship from the oneside on the visualization.

You SUMX table need to be adjusted to your model.

this is just a generic solution needs to be adjusted to your model and needs.

[MFelix]

Hi @karabryan ,

I'm looking at the table you are presenting and there is row in blank. I want to see the data view of these tables and not the visualizations is that possible?

If you want share it trough private message since information can be sensitive

[karabryan]

I will send you a private message with the tables.

[v-deddai1-msft · Accepted solution]

Hello @karabryan ,

Could you please try to use firstnonblank in your formula like:

LOOKUPVALUE(
    FIRSTNONBLANK(Currency[NewRate],1),
    Currency[NewSourceTarget],
    Booking[NewSourceTarget]
    Currency[Date],
    Booking[Date]
)

If this post helps, then consider Accept it as the solution to help other members find it faster.

Best regards

Dedmon Dai