DirectQuery Dataflow not working in premium capacity workspace

[MAG_PRG]

I have created a dataset that uses a direct query connection to a dataflow. In our dev workspace this worked without issue. It worked in the test workspace as well, but in our product workspace it refuses to make a directquery connection. I turned on enhanced compute engine in all workspaces and refreshed the dataflow several times after turning it on.

The only difference I can think of between the workspaces is that our production workspace is premium capacity, could that be causing this issure?

[Anonymous]

Hi, @MAG_PRG 

You need to verify that your user account has the necessary permissions to access the dataflows in your production workspace, which may vary between environments. Also make sure that the data source you're connecting to supports DirectQuery in Premium capacity, and there may be differences in how the data source is handled between different environments. See if there are any error messages or look at the logs, which may provide clues.

You can check the following link:

Using DirectQuery with dataflows - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[MAG_PRG]

Hi

If I attempt to use the dataflow thorugh a dataset that has been pushed through a pipeline, and therefore expects the dataflow to have a direct query connection, I am getting the error message "We couldn't fold the expression to the datasource" on any visual that attempts to use a field from the dataflow.

If I try to connect to the dataflow from a fresh report, not run through the pipeline, I am not getting an error message, I am just not given the option of using direct query, it goes straight to import mode. 

If the issue was related to my users security access I'd expect to get an error attempting to use the dataflow with import mode as well, and my user should have all accesses for the production workspace.

The only thing I can figure is that for some reason it does not consider the dataflow to be eligible for a direct query, but the only difference is which workspace it is in, I've enabled enhanced compute engine, and the only difference I can think of is that production uses premium capacity.

I read the links you posted, but I was unable to find anything related to premium capacity or any other reason it should not work for me

[MAG_PRG]

Hi

It's unlikely to be a connection issue, it works with import mode, just not direct query. I'm not getting any error messages, I'm just not given the option to choose between direct query and import. I've tried it from starting a blank report in desktop.

Edit: When I tested with a dataset using the dataflow that was also pushed through the pipeline I would get the error that the datasource did not support query folding when it connected to prod instead of dev/test.

This would make sense if it'd forgotten to turn on enchaned compute engine, but I've double checked that it's on and refreshed several times after, and it's been fine in dev and test workspaces.

The dataflow is pushed through a pipeline, I don't know if that could cause any issues, but I don't swap out any data sources, and it worked from dev to test.

[SaiTejaTalasila]

Hi @MAG_PRG ,

It might be due to gateway version.

Thanks,

Sai Teja 

[MAG_PRG]

Hi

I'm not sure exactly what you mean by gateway version, but I expect it to be the same for all workspaces. I am using Oauth2 with an organizational account.

[collinq]

HI @MAG_PRG ,

The fact that the production is premium and dev is not should not be an issue.  The premium will have MORE capabilities than a non-premium site so that will almost certainly not be a factor.

If this works in dev, then the data source should be a support data source.  As seen here: (Power BI data sources - Power BI | Microsoft Learn)

This leads me to think that the issue may be in the connection - that the data source or gateway does not allow you or your userid to use the Power BI connection and/or your userid does not have permissions to the production data, even though you do have dev access.  Could that be it?

What exactly is the error message that you are receiving?

And, here is a link with some overview that may be helpful (or you know this all already  🙂 )

DirectQuery in Power BI - Power BI | Microsoft Learn

[MAG_PRG]

Hi

When I tested with a dataset using the dataflow that was also pushed through the pipeline, which had a direct query connection to it in dev and test,  I would get the error that the datasource did not support query folding in production.

If I use a fresh report and connect directly to in in prod I am not getting an error message, I am just not given the option of a direct query connection