Pass parameter to SQL Stored Procedure query from Slicer

[Anonymous]

Hi all,

As the title, we are now facing a problem with the data source. We have a stored procedure which required a parameter is YEAR, because we need to filter the data by Year before showing on the report/chart. 

We found that Power BI has Parameters management already but I'm not sure this can work with Slicer?

We really need help to select a year from Slicer and then reload all the data from the database.

Thanks in advance.

[JosefPrakljacic]

Hi @Anonymous,

the way you imagine it will not work, because the slicer is 'active' after PowerQuery is executed and "you can't get back".

What you can do is when the user opens the report he has to enter a year parameter.

To do this, I'd like to refer you to this article. This is about entering a username and receiving the data but the principle is basically the same.

I don't see any more elegant way to accomplish this.

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[Anonymous]

Thank you so much @JosefPrakljacic,

It's really helped me out when I need to change the data from DataSource.

But it just requests the Parameter when Open the PowerBI Template file with Power BI Desktop, can we apply it with the Power BI Web? 

Hope to hear from you soon.

[pthapa]

Hello,

As far as I know about passing parameter values on the web(service); you can only do it in the dataset settings. You will have to give user access to the dataset inorder for the user to be able to enter the parameter value. I don't belive so far there is a way to have user enter the parameter value in the report and get the data, I wish power bi will update such requirements in the future updates. We really need that option enabled.

In the sementic model, the user need to go to the schedule refresh icon, go to parameter section and you will have option to enter the parameter value there, then apply. After that, they need to manual refresh the dataset inorder for the the parameter to take effect. Hope this information helps.

Thanks,

Pthapa

[v-piga-msft]

Hi @Anonymous ,

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, free to ask.

Best  Regards,

Cherry

[v-piga-msft]

Hi @Anonymous ,

Normally, we could edit parameter setting in power bi service.

Please refer to this document.

Best  Regards,

Cherry

[pthapa]

Is there a way or workaround that report users will enter the parameter value in the report visual instead of doing it in the settings, sementic model?

Allowing users to access sementic model is not the best approach.

Thanks,

Pthapa

[JosefPrakljacic]

Hey does this meet your requirement?