Can What if parameter pass to advanced Query?

[juchen]

Hi Power BI experts,

Would like to know if some one has suggestion on pass "what if paramter" to Power query, and then pass it to SQL Server stored procedure. I have tried way community provided, that's report designer pre-defined parameter value in Power BI Desktop. 

SQL Stored Procedure

But considering use defined paramter, I'm thinkg What If parameter, but when I replace parameter name into Advanced Query, it not works.

let   

= Sql.Database("JU-NB", "AdventureWorks2016", [Query="EXEC [dbo].[usp_GetTransactionHistory] @sdate = '" &syear &"-" &smonth & "-" & sdate & "',@edate='2013-08-02'"])

It will have error.

Can someone has any suggestion of the workaround? Thank you.

[v-xicai · Accepted solution]

Hi @juchen ,

Your demand is a good idea, while it is not supported to use What-if parameter in Advanced Editor in Power BI currently. You can post your new idea in Idea Forum , to improve the Power BI.

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Amy

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

[Mariusz]

Hi @juchen 

You can not pass what-if parameters back to SQL server, you load all your data into a model on refresh, from there you use the only model as your data source.

If you want to reflect all the changes in you SQL DB you can use direct query mode, where you do not need to worry about passing parameters as DAX is folded into SQL on the fly, so filter context is translated into SQL.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[juchen]

Hi @Mariusz,
Thanks for your promptly reply. I’m actually using direct query mode now. Since I need to pass user input value as variables of SQL server stored procedure, and user input function/field is not support in Power BI now, alternatively I’m thinking What-If parameter, letting user to slicer a range, then pass it to source sql server as direct query mode.
If what-if parameter cannot pass to stored procedure, do you have any suggestion on this scenario? Very appreciate for your help. 😃

[Mariusz]

Hi @juchen 

Sorry for the misunderstanding, but I don't know how to help you with your requirement.

Best Regards,
Mariusz

Please feel free to connect with me.

[juchen]

Hi @Mariusz,
Don’t worry, I will keep trying and welcome any idea from all Power BI experts.