Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Hi @Anonymous ,
If we answered your question, please mark a post as solution, this will also help others.
Please give Kudos for 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.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |