Forum Discussion

jdubs's avatar
jdubs
Helper V
3 years ago

Problem using 'SWITCH' function

I'm trying to convert an expression from an old SSRS report to Power BI and running into the following problem. I have a table of Monthly sales figures (Jan, Feb, Mar,...) and the rows contain numerical values representing sales figures for that month.

 

I am trying to use the following expression but am receiving the error: "Function 'SWITCH' does not support comparing values of type True/False with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values"

 

I found a few posts on this but I'm still uinsure of how to structure the expression for a new measure. 

 

Monthly Sales = 

(switch(Month(Now()) = 1, Sum(Jan_Value),
Month(Now()) = 2, Sum(Feb_Value),
Month(Now()) = 3, Sum(Mar_Value),
Month(Now()) = 4, Sum(Apr_Value),
Month(Now()) = 5, Sum(May_Value),
Month(Now()) = 6, Sum(Jun_Value),
Month(Now()) = 7, Sum(Jul_Value),
Month(Now()) = 8, Sum(Aug_Value),
Month(Now()) = 9, Sum(Sept_Value),
Month(Now()) = 10, Sum(Oct_Value),
Month(Now()) = 11, Sum(Nov_Value),
Month(Now()) = 12, Sum(Dec_Value))/1000)

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    There is another option

     

    Monthly Sales = 
DIVIDE(
switch(Month(Now(),1, Sum(Jan_Value),
2, Sum(Feb_Value),
3, Sum(Mar_Value),
4, Sum(Apr_Value),
5, Sum(May_Value),
6, Sum(Jun_Value),
7, Sum(Jul_Value),
8, Sum(Aug_Value),
9, Sum(Sept_Value),
10, Sum(Oct_Value),
11, Sum(Nov_Value),
12, Sum(Dec_Value))
,1000)

     

    Having values per month in different column is a design red flag.

    • jdubs's avatar
      jdubs
      Helper V
      3 years ago

      I was able to get it to work by adding TRUE() before the arguments, so:

       

      Monthly Sales = 

      (switch(

      TRUE(),

      Month(Now()) = 1, Sum(Jan_Value),
      Month(Now()) = 2, Sum(Feb_Value),
      Month(Now()) = 3, Sum(Mar_Value),
      Month(Now()) = 4, Sum(Apr_Value),
      Month(Now()) = 5, Sum(May_Value),
      Month(Now()) = 6, Sum(Jun_Value),
      Month(Now()) = 7, Sum(Jul_Value),
      Month(Now()) = 8, Sum(Aug_Value),
      Month(Now()) = 9, Sum(Sept_Value),
      Month(Now()) = 10, Sum(Oct_Value),
      Month(Now()) = 11, Sum(Nov_Value),
      Month(Now()) = 12, Sum(Dec_Value),

      BLANK()

      )

       

      I could also drop the /1000 since I can just format the column directly in POWER BI.

       

      Agreed on the data structure, but this was easier than trying to get a programmer to update the source data.