Power BI Template Dynamic Query Formula.Firewall Error

[Anonymous]

Hello,

I am trying to create a template that asks users to input parameters where these parameters are populated using different stored procedures.

My first step was to create these parameters with Query option, then create the report and export a template to be shared. As many have experienced, I was having difficulty with the usual and famous Formula.Firewall error. There are plenty of cases of this error here on the forums and also over the internet different people talking about it for Excel as well as Power BI. In my case, using  Value.NativeQuery() as suggested by Chriss Webb on his blog post series about Data Privacy in Power BI solved all of my problems. In the end, I ended up factoring my query as follows.

let
    Param_List = [EntityParam = Entity, LedgerParam = Ledger, PeriodParam = #"From Period", AccountParam = Account],
    Source = Sql.Database("<server_name>", "<database_name>"),
    Query = Value.NativeQuery(Source, 
        "EXEC [addin].[BI_TEST_AgedTrialBalance_Monthly]
            @EntityCode = @EntityParam,
            @LedgerCode = @LedgerParam,
            @FromLongPeriod = @PeriodParam,
            @AccountCode = @AccountParam",
            Param_List
    )
in
    Query

From here on changing the parameter values worked perfectly and I am able to retrieve the correct data. 

However, exporting a template from this Power BI file did not go so smoothly. Launching/importing from template from a new Power BI file starts with no problems.  All the parameter values are population correctly with no error. Screenshot below.

When I fill in all the parameters and click load, I get the famous  Formula.Firewall again. 

Closing this and going into Edit Queries and Refreshing the data however works perfectly with no error.

I do not understand why this is happening. Everything works fine in the original Power BI file but when trying to load the data initially into a template, it does not work. Has anyone experienced something similar to this?

I would appreciate any help or tips on this.

[v-chuncz-msft]

@Anonymous,

You may take a look at post below.

https://community.powerbi.com/t5/Desktop/Pass-List-Query-Parameter-Value-into-Another-SQL-Server-Sto...

[Anonymous]

Thank you for the reply @v-chuncz-msft.

Privacy isolation levels is the main reason I have refactored the query to bypass that particular problem. However, I do not want to change the privacy levels. 

I am trying to figure out why everything works in the PBIX file but does not work when exported to a template.

Do you think when using one single data source like in my case it is safe to ignore privacy levels?