SELECTEDVALUE and SWITCH doesn't return value

[LisaB]

Hi,

I have a table where I want to return exchange rates based on a selected value.

Table LCY is used as a slicer and only single selection is allowed. I have multiple exchange rate tables and there is relationship created from each one of them to my transaction table.   

I have created a column and this is my formula but it doesn't return any values at all.

Exchange Rate = 

VAR  Selection =

    SELECTEDVALUE ( LCY[Currency Code])

RETURN

    SWITCH (

        TRUE (),
        Selection = "SEK", RELATED(Currenices_SE[ExchangeRateAmt]),

        Selection = "AUD", RELATED(Currenices_AU[ExchangeRateAmt]),
        
        Selection = "DKK", RELATED(Currenices_DK[ExchangeRateAmt]),

        Selection = "USD", RELATED(Currenices_US[ExchangeRateAmt]),
        
        Selection = "EUR", RELATED(Currenices_NL[ExchangeRateAmt])
    )

 Could you please advise? 🙂

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @LisaB ,

In your sample, if “Exchange Rate” is a measure, you can use MAX function to return the current value.

Best Regards,
Community Support Team _ kalyj

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

[PaulDBrown]

Do you mean a column as in a calculated column in a physical table? If so, it won't work: physical tables are "calculated" when the model is loaded and are oblivious to slicers.

You can however use it as a measure in a table visual

[LisaB]

Hi Paul,

I don't really follow. Yes, it is a column but when creating a measure instead I get an error saying following:

"The column either doesn't exist or does not have a relationship"

[Greg_Deckler]

@LisaB What does your data model look like? RELATED only works across 1 to many relationships and only returns values from the 1 side of the relationship.

[LisaB]

Hi Greg,

This is how it looks like. I tried only RELATED and it returns the correct values for each currency table (pleae disregard the misspelling 🙂).