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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.