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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Switch formula is there a maximum number

Imagine for a moment that the switch formula contained "if statements" is there a maximum number of "ifs" I can use in the switch formulae ?

Or any other limits to how big you can make the formula?

 

 

5 REPLIES 5

Hi @Anonymous ,


If we answered your question, please mark a post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

In the syntax of "switch":SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  ,where do you wanna use if statements?As simply for "switch",I dont find any limitation about the size in the formula..

Here is the reference, hope it would help.

 

 

Best Regards,
Kelly

Hi @Anonymous ,

 

I don't know of a limit.
Why do you ask?

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


az38
Community Champion
Community Champion

Hi @Anonymous 

I haven't seen any limitation for SWITCH() or IF statements

So, if you will be able to discover it - your post something would be very useful for the Community 🙂

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

There definitely seems to be a limit for me of 10. In both SQL and DAX. My SQL query originally contained the CASE columnname WHEN '1' then 'A' when '2' then 'B' ELSE 'unknown' END AS PartDesc

 

If I had more then 10 WHEN's, power bi would puke saying it could not prepare the statement and the IDataReader interface thru up an error. As SOON as I brought my WHEN's down to only 10, then power displayed the data just fine! If I use the raw column itself, instead of the CASE-interpration, it works fine.

 

So I thought I would try using the Power BI SWITCH statement instead of doing it in the SQL. So I created a new Column on the query, and set it to switch(true(), Query1[foo] = "1", "a", and so forth. But I got the same error! So I lowered the conditions down to just 10, and it worked fine! But 11 conditions and it dies. 

 

I'm using DirectQuery. Is there a way around this silly 10 limit for the # of conditions you can have? What if you had a column from the database called "Month" and you wanted to convert 1 - 12 to January thru December, the case/switch condition limit wouldn't allow it! Any ideas? 🙂

----

UPDATE: This limit must be due to some other constraints like memory or something. I was able to create a new Power BI with an SQL statement like so:

 

select
case month(getdate())
when '1' then 'Jan' when '2' then 'Feb' when '3' then 'Mar' when '4' then 'Apr' when '5' then 'May'
when '6' then 'Jun'  when '7' then 'Jul' when '8' then 'Aug' when '9' then 'Sep' when '10' then 'Oct'
when '11' then 'Nov' when '12' then 'Dec'
else 'blah'
end as Month

 

And it worked just fine with 12 conditions. So i don't know why my other power BI query seems to have a hard limit of 10, there must be some resource constraint someplace on the system or gateway. It used to work a long time ago, I just reopened it recently and saw one of the visuals was broken and that's when I determined the CURRENT limit of 10. I'll keep troubleshooting and see where the resources are constrained. I've run across this a lot with power BI, where stuff will work one day and then not work the next day, etc...

 

Thanks!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.