Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Encilo
Frequent Visitor

Direct Query and Query Parameters

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,

15 REPLIES 15
Anonymous
Not applicable

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

Jayendran
Solution Sage
Solution Sage
Jmenas
Advocate III
Advocate III

similar issue here but with SAP HANA. 

eduardopacheco
Regular Visitor

Any luck with this? Im having the same question

Anonymous
Not applicable

Hi 

Any resolution to this, I am facing the same issue

v-haibl-msft
Employee
Employee

@Encilo

 

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.

Direct Query and Query Parameters_1.jpgDirect Query and Query Parameters_2.jpg

 

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.

@Encilo

 

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

Anonymous
Not applicable

I face the same problem. I created a simple parameter shown below:DirectQuery Parameter.PNG

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.

ankitpatira
Community Champion
Community Champion

@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.

I have downloaded latest Power BI desktop an what I did is I have created direct query report with sql query. Then I have created parameter and I've used it on one column in text filter. As soon as I tried to apply these changes I get this message that direct query and import mode cannot be mixed. I assume that this filtering option somehow introduces functionality that requires import mode?

@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?

Encilo
Frequent Visitor

@ankitpatira If you are able to do this now can you please explain me how?

pqian
Employee
Employee

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.

Anonymous
Not applicable

Did we figure out a way to do this?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.