PBI service - conditional parametric connections

[fabiuzz]

Hi guys, I need some help, please.
I have a PBI report with a set of queries that must be executed conditionally, depending on the presence or absence of an optional applicative module, with its server, database and tables.
The data model must be the same for both the alternative scenarios (standard module only or standard+optional modules) and I must prevent the data model connections and the visual objects from crashing in case there is no optional module installed.
The solution I found tests if the optional module connection parameters are filled in: if so, it executes the real sql query on the optional module server and database; if not, it executes a "fake" sql select statement with null or 0 values on the standard module server and database.
The following M example code is working properly in Power BI desktop, but not in Power BI service:

let
#"Optional Module" = if #"Server - Optional Module" <> null and #"Database - Optional Module" <> null then "yes" else "no",
#"Server" = if #"Optional Module" = "yes" then #"Server - Optional Module" else #"Server - Standard Module",
#"Database" = if #"Optional Module" = "yes" then #"Database - Optional Module" else #"Database - Standard Module",
#"Query" = if #"Optional Module" = "yes" then "SELECT Field1, Field2, Field3 FROM Table" else "SELECT null AS Field1, null AS Field2, null AS Field3",
Source = Sql.Database(#"Server", #"Database", [Query= #"Query", CreateNavigationProperties=false])
in
Source

The PBI service error says: "Query contains unsupported function. Function name: Sql.Database".

I suspect PBI service can't solve the conditional expressions:

#"Server" = if #"Optional Module" = "yes" then #"Server - Optional Module" else #"Server - Standard Module"
and
#"Database" = if #"Optional Module" = "yes" then #"Database - Optional Module" else #"Database - Standard Module",

so the Sql.Database() function fails. Then, if I write:

#"Server" = if true then #"Server - Optional Module" else #"Server - Standard Module"
and
#"Database" = if true then #"Database - Optional Module" else #"Database - Standard Module"

it works, but if write:

#"Server" = if 1=1 then #"Server - Optional Module" else #"Server - Standard Module"
and
#"Database" = if 1=1 then #"Database - Optional Module" else #"Database - Standard Module"

it fails. I suppose it can't access any basic functions or operators before testing the connections.

Another strange thing is that if I write a simple M query to get a table of the standard M functions:

let
Source = #shared
in
Source

it works properly in PBI Desktop, while in PBI service I get an error similar to the previous:
"Query contains unsupported function. Function name: #shared".

Any ideas?

Thanks

[v-shex-msft]

Hi @fabiuzz ,

It seems like you are invoke other query steps in your query table to achieve conditional query.

AFAIK, current power bi service still not support some of advanced operations. (e.g. invoke custom function, reference query steps, loop calculations...)

Regards,

Xiaoxin Sheng