Source references other queries or steps, so it may not directly access a data source. Server

[earlr1765]

Rather that write independant sql queries and then join the tables later, I am interested in creating a parameter dimension table where it adds a column which is essentially a sql query based on a value in that row. I've looked up Parameterized SQL Queries but those tend to be a single query returned examples. 

So visually I have a table where the parameter is the first column, and the second column are the records that come from a SQL query that referencing the row value of the first column.

ClientParameter	SQL_Query
A	SELECT * from TABLE where ClientParameter = "A"
B	SELECT * from TABLE where ClientParameter = "B"
C	SELECT * from TABLE where ClientParameter = "C"

The results of that table I would want to expand as new nested rows. I'm sort of thinking of the current behaviour when you list of files in a folder, and then expand the binary results of those files to create new rows. 

I managed to get this to work by using the = Sql.Database("server", "database", [Query="Select ...."]) statement. 

So using PowerBI, I read in the ClientPameter table and then generate a new column that is based on this SQL query, where I'm referencing the row. 

To make this work in PowerBI server, I figure that I need to provide the client parameter using an excel file that I upload to our sharepoint server. 

When I view the result in PowerBI Desktop, it all works, but when I upload this to PowerBI server it complains providing a message that the powerbi file references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I found a video where the they explain this has to deal with privacy settings and also explain how to solve this (45 min into). https://www.youtube.com/watch?v=IQpniTiXQiI

however none of this fixes the issue. 

Is this ability something that is just broken in the current PowerBI server? 

[Anonymous]

Hi @earlr1765 

Because you used two source sql and excel in the same query so you will have the problem that it references other queries or steps, you can try the following solution.

1.Ignore the privacy levels, you can set it in power bi desktop, click 'Options and settings' to set it, you can refer to the following picture.

And you can refer to  the following link.

Solved: Formula.Firewall: Query references other queries, ... - Microsoft Fabric Community

Solved: Query references other queries or steps, so it may... - Microsoft Fabric Community

2.You can consider to use the dynamic paramater in power query so that you don't need to import the external  excel file, but it need that you use the direct query mode to connect to the SQL , you can refer to the following link about the dynamic paramater.

https://www.bing.com/search?q=power+bi+dynamic+parameter&qs=MT&pq=power+bi+dynamic+para&sc=8-21&cvid...

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[earlr1765]

No sadly this will not work. The issue doesn't happen on PowerBI Desktop because I have ignored privacy settings, but it does appear in PowerBI Server despite also making both datasets being set to Organization or Public. 

A dynamic query will not work since I want to aggregate all data across all the different ClientParameters, not just one selection. 

For context the queries only work when I'm specifying a where clause, since the data is too huge to merge together as one extract. So 20 sql queries are faster than 1 query with all 20 data elements.