cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juchen Frequent Visitor
Frequent Visitor

Can What if parameter pass to advanced Query?

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. 

2019-10-28 18_05_18-Microsoft Edge.png

 

SQL Stored Procedure

2019-10-28 18_08_36-SQLQuery10.sql - not connected_ - Microsoft SQL Server Management Studio.png

 

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

2019-10-28 18_15_15-SQLQuery10.sql - not connected_ - Microsoft SQL Server Management Studio.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Can What if parameter pass to advanced Query?

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.

 

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Can What if parameter pass to advanced Query?

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.
Mariusz Repczynski

 

juchen Frequent Visitor
Frequent Visitor

Re: Can What if parameter pass to advanced Query?

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. 😃
Super User
Super User

Re: Can What if parameter pass to advanced Query?

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.
Mariusz Repczynski

 

juchen Frequent Visitor
Frequent Visitor

Re: Can What if parameter pass to advanced Query?

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

Re: Can What if parameter pass to advanced Query?

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.

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,924)