Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jdubs
Helper V
Helper V

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)

2 REPLIES 2
lbendlin
Super User
Super User

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.

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. 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.