Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
As far as I can see usage of Query Parameters in DirectQuery mode is not well supported and please let me know if I'm wrong about it. I'm trying to create report with Azure SQL Database as data source in DirectQuery mode where I would like to apply row filtering based on the value of my parameter.
What I'm trying to do is to dynamically change parameter and get different result based on the parameter value? Right now Power BI Desktop is telling me that this approcah will result in transformation that DirectQuery mode is not supporting?
Is there a workaround on this?
Thanks,
Bingoooo..
Got solution finaly...if you download latest version of power bi dekstop and publish the report.your parametrs are enable in services and you can make it dyanamic.
latest version link-https://www.microsoft.com/en-us/download/details.aspx?id=58494
version -2.82.5858.1161
Hi ,
Putting some referenes
https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI
similar issue here but with SAP HANA.
Any luck with this? Im having the same question
Hi
Any resolution to this, I am facing the same issue
Just to confirm, did you enable load the parameters as below? If yes, parameters will be loaded to the Data Model and following warning message will pop up when you close and apply Query Editor.
The only thing you need to do is uncheck it. By default though, Parameters are not loaded to the Data Model.
And following two documents should be good references.
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
http://biinsight.com/power-bi-desktop-query-parameters-part-1/
Best Regards,
Herbert
@v-haibl-msft Thank you for your explanation but even when I do this I end up with a message that says "...query contains data transformations that are not supported in DirectQuery mode".
It seems that I can't do transformations on live query reports (I'm using Azure SQL database) using Power BI Desktop at the moment.
Query parameters are supported in Direct Query, could you please try with a simple table in your Azure SQL database?
According to this document, many data modeling and data transformations are available when using DirectQuery, though with some limitations. I suspect that there are some unsupported transformation in your query. If so you may need to try with Import mode.
Best Regards,
Herbert
I face the same problem. I created a simple parameter shown below:
I'd like this to show up as my data source in the worksheet. When I click on Auto Load, I get the same error message: Combining Direct Query & Import isn't supported. I can't change back to Import as DirectQuery is essential. Please advise.
@Encilo It depends. I have set this up earlier with directquery to sql server database and use query parameters to filter rows and it worked fine. Parameter was filtering on column type text but i have also used other parameters for date type columns. I haven't faced any issues using that.
@Encilo I don't think it has anything to do with parameters. It should fold into a constant in this case. Can you share the query formula?
Can you post the formula text of the filter rows step? This should be supported, although I have to warn you that the parameters will be only in effect in "design time" (query authoring). At runtime (reports view) if you change the parameter you would have to reload it.
Did we figure out a way to do this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |