Advice when using a DirectQuery and dataflow together

[MichaelHutchens]

Hi folks, I'm looking for some advice.

I have a PBI report with two data sources. One is a DirectQuery that is controlled by my organisation. The other is a dataflow I've created myself.

I need to mash these two sources together, but in order to do that I've been using a SUMMARIZECOLUMN DAX table to pull data from the DirectQuery, so that I can avoid the restrictions of the DirectQuery (e.g. no access to PowerQuery).

I've now found that this is a problem, because once I upload my report to my PBI Workspace I get the following error:

Data source error{"error":{"code":"Premium_ASWL_Error","pbi.error":{"code":"Premium_ASWL_Error","parameters":{},"details":[{"code":"Premium_ASWL_Error_Details_Label","detail":{"type":1,"value":"Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery."}}],"exceptionCulprit":1}}}
Cluster URIWABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net
Activity ID87dd4a74-9d9f-427f-8ae3-0626857f046f
Request ID412665ce-8c4b-4f5f-8268-d9fcda937bf8
Time2022-06-19 14:01:41Z

What I'm looking for here is advice about how to avoid this error message, e.g. alternatives to SUMMARIZECOLUMNS or ways I can create a 'local copy' of the DirectQuery that I can transform for my report.

Any advice would be appreciated 🙂

[Krishnaraj]

Hi @MichaelHutchens 

Were you able to resolve the issue or find alternate options?

Thanks 

Krishna

[MichaelHutchens]

Thanks for following up, and no I haven't been able to resolve this yet or find alternatives. I've responded to the most recent support response in this conversation chain now.

[v-yiruan-msft]

Hi @MichaelHutchens ,

There is the following limitation when using DirectQuery, please find the details in this official documentation.

• Calculated tables and calculated columns that reference a DirectQuery table from a data source with Single Sign-on (SSO) authentication are not supported in the Power BI Service.

Could you please explain what data source that DirectQuery is using? Why do you need to mash DirectQuery and your dataflow together? What kind of functionality is it to achieve? Later we can see if there is an alternative solution to achieve this functionality...

Best Regards

[MichaelHutchens]

Hi there, apologies for my long delay. The DirectQuery is using an Active Directory data source I believe. My dataflow is some SharePoint Online library information - page names, URLs, and a column for the page owner names. I need to mash these two data sources together to create a report that shows the pages on our intranet, who owns them, and further details (from AD) about those owners.