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

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

This error is on a measure correct?

  • karabryan's avatar
    karabryan
    Helper I
    5 years ago

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

  • karabryan's avatar
    karabryan
    Helper I
    5 years ago

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

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

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

    I will send you a private message with the tables.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    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
    Super User
    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.