Is there a maximum to the number of else if statements in a create conditional column?

[lasthero]

Hi everyone, 

As I know theres a limitation within Power BI to be able to grab data from multilple DBs even though they are in the same SQL Server. 

So to get around this I was thinking of hardcoding some conditional columns, I was hoping to get around the limitation by doing this however I seem to be running into a limit in else if statments before I run into an error saying "This Step results in a query that is not supported by DirectQuery Mode"

Below is a sample of the statement:

= Table.AddColumn(#"Changed Type", "Category", each if [VENDOR] = "ABCD123" then "External" else if [VENDOR] = "EFG123" then "Internal")

Basically the above statement works, until I seem to pass a certain number of else if statements then i get the error.

Is there a max # of else if statements I can use?

[CahabaData]

For some reason I want to say there is is a max of 3 - but I don't know why that tidbit is in my head....  maybe in coming from SQL/Visual where there really isn't a limit it caught my eye when going thru info here - - - but I can't put my hand on that source of info so I'm not 100% on this point.....

but in any case you get around it by not nesting them all in one statement/column     segment them apart in separate columns...and then make a final column that selects the correct column/value to use... (you may need an intermediary column or 2 depending on how many are involved).....  this actually is a good approach in aiding with trouble shooting because unlike the nested approach, you can visually see the results of each logical step just in case there is an error mid stream....

[lasthero]

Hmm this might actually be a decent approach Cahaba, i didnt think of actually just creating a 2nd column for the next set of segmented parts.  I was more focused on trying to keep everything in one column.

The max seems somewhere in the low 100s, I was able to put around 100 or so before I received the error.

[v-yuezhe-msft]

Hi @lasthero,

I test the scenario as yours. In DirectQuery mode, when I add 82 else if statements in the conational column, I  get  the same error as yours. However,  when I switch to import mode using the option in the following screenshot, everything works ok.


I believe that this is a limitation on else if statements in DirectQuery mode. I have encountered a similar issue when using the following formula to get Monthname in  DirectQuery mode, I got the error message “Case expressions may only be nested to level 10”. Similarly, the following formula works well when I connect to SQL Server via “Import” option in Power BI Desktop.

MonthName = SWITCH(MONTH('Table1'[Modified_date]); 1;"Jan"; 02;"Feb";3;"März";4;"April"; 5;"May";6;"Jun";7;"Jul";8;"August";9;"September";10;"Oktober";11;"November";12;"December")

Thanks,
Lydia Zhang

[lasthero]

Thanks Lydia for testing, and yea if possible I wanted to avoid Import due to the sheer amount of data Im manipulating, would rather avoid having to import millions of rows.

But thank you for your suggestion, I think I'm going to just treat this as a limitation of Power BI for now and perhaps use the work around described earlier.

[Sean]

@lasthero There are some DAX functions not supported in Direct Query. I wonder if this is one of them.

https://msdn.microsoft.com/en-us/library/mt723603.aspx

[ankitpatira]

@lasthero In power bi desktop go to File -> Options & Settings -> DirectQuery and tick allow unrestricted measures and see if that works for you.

[lasthero]

Hi Ankit, 

Thanks for the reply and suggestion, however I still get the same error.