Forum Discussion

LisaB's avatar
LisaB
Helper III
4 years ago
Solved

SELECTEDVALUE and SWITCH doesn't return value

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!

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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's avatar
      LisaB
      Helper III
      4 years ago

      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's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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's avatar
      LisaB
      Helper III
      4 years ago

      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 🙂). 

       

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.