PowerBI Stored Procedures not working when using parameters that comes from my Database

[julsr]

Hello everyone,

My primary data table comes from a Stored Procedure which uses parameters from PowerBI where:

• The user selects a Company from the parameters lists, which is a query that takes values from a View table in my Database
• The user enters a date in the parameters text box
• My final Stored Procedure must use these two results to bring the data, but the following error appears: 
  • Formula.Firewall: Query 'spName' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

How can I pass both parameters, the one from my view (could select multiple values which are the result of the view) and the one the user picks (he can enter one date value here), which the users are using, to this last stored procedure in POwerBI? Privacy is important here, so I can't ignore the privacy levels.

Thanks

[lbendlin]

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

put everything in the same partition.

[julsr]

Thanks for your reply! It makes sense to me but I can't see how can I solve it (without disablinythe firewall) because :

• I'm using two different queries results that are converted into a list to become parameters (I'm showing parameters values based on two queries) which must be selected by the user. So here, we have two queries where data must be selected. 
• Then, I have another query (the stored procedure) which uses information from the parameters selected by the user (described above) to get the data from the database.

The problem here is that I can't put everything into one M query because information from two partitions (the two queries) must be filtered before for the business user to pass this parameters into the stored procedure.

Thanks

[lbendlin · Accepted solution]

must be filtered before for the business user to pass this parameters into the stored procedure.

That won't work anyway unless you are accessing the SP via Direct Query and use Value.NativeQuery with enforced folding. 

[julsr]

Thanks! @lbendlin  I did it this way, and it worked. The folding is to make the queries faster, right?

[lbendlin]

Technically it pushes the work back into the data source.  If that makes it faster or not depends on the data source performance. Usually you are right, it will be faster.

[julsr]

Do you mean something like this https://powerusers.microsoft.com/t5/Power-Query/Getting-data-from-stored-procedure-into-power-bi-nat...? 

Call the SP using value.nativequery instead normal mode?

Thanks 

[lbendlin]

Yes.  Are you using a Direct Query connection?

[julsr]

Yes, I'm using Direct Query and passing the arguments directly through it.