Forum Discussion

karabryan's avatar
karabryan
Helper I
5 years ago
Solved

Condition Statement

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 transactio...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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's avatar
    MFelix
    5 years ago

    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's avatar
MFelix
Super User
5 years ago

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's avatar
karabryan
Helper I
5 years ago

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's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      karabryan
      Helper I
      5 years ago

      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?

       

       
       

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        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.