Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Select Value as variable not working

Hello !

 

Im trying to change and old DAX that is not working anymore due to the year change, the last analyst didn´t considered year change.

 

So my ideia is to use SELECTEDVALUE as a variable on the formula, so it doesn´t need to be changed every year, heres the code:

 

Economia Real =

VAR A = [RS_TON 2019]
VAR B = [RS_TON 2020]
VAR C = [RS_TON 2021]
VAR D = [Peso NF 2019]
VAR E = [Peso NF 2020]
VAR F = [Peso Util 2021]
VAR G = [Frete 2020]
VAR H = [Frete 2021]
VAR I = [Volume 2020]
VAR J = [Volume 2021]

VAR YEAR = SELECTEDVALUE(Consolidado[Ano Faturamento])

RETURN

SWITCH(TRUE(),
YEAR = 2019, (A-A)*D,
YEAR = 2020, (B-A)*E)
 
 
the problem is, when i do filter 2020, for example, it doesnt actually calculate the SWITCH(TRUE() argument. It just simply summarize all my data (whichis sales values), and don´t execute the (B-A)*E) part of the formula, but the wierdest part, which i coudn´t figure out why, is that if i change VAR YEAR to = VAR YEAR = 2020, the code runs flawless, could i get any help ?

 
Wrong Value
 
 
correct value.
 
 
Thanks !
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous 

    I don't think you can just use selectedvalue() directly on the current table. Try following:

    1. create a new table with year column, more years if needed.

    2. You do not need VAR Year, remove it. And change the return formula:

    Return

    SWITCH(SELECTEDVALUE(NewTable[Year]),
    2019, (A-A)*D,
    2020, (B-A)*E)

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Is this you actual formula or I doubt. Why so many var when you are not using them ?

     

    This formula seem correct. Best way to test is

     

    SWITCH(TRUE(),
    YEAR = 2019, 1,
    YEAR = 2020, 2

    )

     

    Check what value it returns

    Post that return A , B and E one at time and check what is wrong. May be one formula have some issue

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Hi!

       

      yes, its my formula, i have this many variables just for now, i will try to fix them later, since i didn´t manage to make the first part of DAX to work, i plan to make them more easy to manipulate, most of these are year specifically formulas.

       

      I´ve tried your advice but it didn´t seems to work 😞

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

    I don't think you can just use selectedvalue() directly on the current table. Try following:

    1. create a new table with year column, more years if needed.

    2. You do not need VAR Year, remove it. And change the return formula:

    Return

    SWITCH(SELECTEDVALUE(NewTable[Year]),
    2019, (A-A)*D,
    2020, (B-A)*E)

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks ! That actually worked 🙂

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.