Working with Functions or Stored Procedures on Direct Query Mode

[Anonymous]

Hi Everyone,
I am having trouble at working  with Stored procedures or Functions while using them in PowerBI with Direct Query. But this is not a technical issue maybe I miss something or need a trick.

Let me explain more about that.

I am working with Developer guys who had worked with so many hours on SQL to present their data in a third-party application that build on Visual Studio. They came with a project and want to use that database (Azure SQL Database) and create visualizations on PowerBI. But they want to keep it simple and they want me to use their function or stored procedures so they hope that the Power BI project doesn't take so much time.

The function which I had to use has two date parameters for BeginDateTime and EndDateTime.

In the picture below I use parameters and everything works fine.

But the main thing is that the report must be interactive and get triggered when the user changes the slicer or filters. So this query has to work on a direct query model if I am not wrong.

Using Parameters doesn't allow users to change these dates on the PowerBI Service. I need a trick to find another way to manage these parameters with slicers or filters in PowerBI Service.

Thanks in advance for your help 🙂

[fmunar1985]

I have a doubt about the parameters in BI.
I have the next store exec Proc Proc_Demo where dFecha = @Fecha
Now in the powerbi i've already did a function with the parameter but i can't make a relation with other tble.
What i need to do is bassed on this parameter has a date in other table that i will use as a filter so in that way the user can refresh the store with that date.
Have any idea how to do that?

[Anonymous]

Sorry for the late response , 

Dynamic M query parameters in Power BI Desktop (preview) - Power BI | Microsoft Docs
That link show the solution what I needed but unfortunately it's not supported on T-SQL based sources. 
I wonder if there is any other solution or way to workaround or trick that end user can manipulate m query with dynamic parameters in Power BI service.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

"Any" or "Binary" type parameters work in Power BI Desktop, but we currently don't support them in the service, for security reasons. You can use "Date/Time" type, then users can change these dates on the PowerBI Service.

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters 

https://docs.microsoft.com/en-us/power-bi/connect-data/service-parameters 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[faisalharoon]

Hi,

i am having same issue.

i developed one dashboard with Direct Query. i created one parameter in power bi (associated with slicer) which i am passing to table valued function in sql server.

in my local machine its working fine. but once i am deploying to reporting server. slicer values are not refreshing.

in the below screenshot please see i created parameter1 as Text. and then in the other screenshot i am binding to Parameter1.