DirectQuery Parameters

[Anonymous]

Good afternoon,

I am new to PowerBI and have a question(s) regarding the input of parameters with DirectQuery.  My DirectQuery runs with no issue, but when I attempt to add a parameter as an applied step, I receive the message "This step results in a query that is not supported in DirectQuery mode.  Switch all tables to import mode"  Is there a workaround for this that does not entail me having to import all tables and/or re-write my query?  I would rather stay with DirectQuery since I have already written all of the queries for the reports I am developing in PowerBI.  Thank you!  

[HamidBee]

Hi @Anonymous. I was able to fix the following by firstly using Import mode instead of DirectQuery. It would not work with DirecQuery. Here are the full steps I followed:

No I need to use the EnableFolding=true command and this is only available when using the Value.NativeQuery function. I created a new step and typed the following:

= Value.NativeQuery( Source, "
SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total 
FROM Orders As O
FULL OUTER JOIN [Order Details] AS OID
ON O.OrderID = OID.OrderID", null, [EnableFolding=true])

Now if I apply any parameter based filters to the dataset it will work without any errors.

[Syndicate_Admin]

Hello good day,

when enhancing a connection to a query that has defined parameters or CTEs, the power Query tool recognizes the query but is not able to load the information (this is presented with connection by import or direct query). The most viable recommendation is that a view is first created in the database with the query and its respective parameters, so that power BI can then connect to this view; in this way the database engine solves all the subqueries and power bi only has to deal with the coenxion to the respective view.

In case of not being able to create a view by organizational restrictions, as far as possible avoid using CTEs and parameters in the query, since this could generate inconveniences in the coenxión

[Anonymous]

Thank you for your reply!  

My DirectQuery is creating the following columns:

Region | Location | Percentage

I want to create the parameters for Region and Location.  I seem to be able to do that, however, when I try to attach the parameter to the query via the 'Filter Rows' process where I am choosing the Filter Row equals the parameter I have created, I receive the not supported in Direct Query mode messaging.  Does that help in making it clearer?  Thank you again for your assistance.  

[Anonymous]

Hi @Anonymous,

Yes, these type of operations does not support in 'direct query' mode. I'd like to confirm which type of database you worked with.

If your data source(e.g. SQL Server, Oracle) supports queries, you can consider directly adding these filter queries in the data connector.

Import data from a database using native database query - Power Query | Microsoft Docs

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous,

Can you please share some more detailed information about the operation that you applied to the direct query mode data tables? They will help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

In addition, if you are working with the data source which supports advanced SQL statements, you can try to add these parameters into the t-SQL query to process on the data source side instead of adding steps process/calculated on the response result table in the query editor.

Regards,

Xiaoxin Sheng