SCHEDULED REFRESH ERROR WHEN USING DATASET CONNECTION

[BRNS]

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 URI: WABI-SOUTH-CENTRAL-US-redirect.analysis.windows.net
Activity ID: 954fd614-029c-451f-ab77-aac648a599e3
Request ID: 02fb20e5-43e4-376e-ccc8-b07440f335f0
Time: 2023-01-12 10:31:06Z

[Anonymous]

Hi @BRNS 

It sounds like you are trying to refresh a Power BI dataset that includes a calculated table that depends on another table that uses Direct Query mode to connect to an Analysis Services data source. Unfortunately, Power BI does not currently support refreshing datasets that include calculated tables that depend on tables using Direct Query mode.

One solution you could consider is to change the source data for the calculated table so that it does not use Direct Query mode. This would allow you to refresh the dataset, but you would need to find a way to incorporate the data from the Direct Query source into the calculated table using other means, such as using Power Query to merge or append the data.

Another option is to use a separate dataset for the calculated table, and then use a measure or a report-level filter to control the data displayed in the report based on the data in the calculated table. This would allow you to refresh the main dataset without affecting the calculated table.

It's also worth noting that Power BI recently introduced the ability to refresh datasets that include calculated columns, so you may want to consider using calculated columns instead of a calculated table if that meets your needs.

Best Regards,
Community Support Team _ Ailsa Tao

[ManuGP]

If this is "not supported" shouldn't there be a warning when creating this type of formula in PBI desktop? It is sort of treacherous that it allows you to build this setup in PBI desktop working perfectly fine and that you only realize it wouldn't work after you publish and try to refresh.

According to various users in this thread: https://community.powerbi.com/t5/Service/Refreshes-for-Datasets-containing-Calculated-Tables-over-Di... this worked perfectly fine and stopped working during 2022. 

In my particular use case we have fact tables from separate PBI datasets in the workspace and we create composite models joining together the different fact tables and to build dimensions tables we have the calculated tables with SUMMARIZE or SUMMARIZECOLUMNS DAX functions (in this way we have en easy and effective way to have the smaller possible dim tables while ensuring referential intergrity).

[pavel_lazarov]

Same problem:
On the Desktop Application everything in the report is working. Just published in cloud it is still working. Once refreshed (online) gets broken!

Same as @ManuGP position.

[chrishol]

I am having the same issue.  I've even trieed removing the calculated columns and only using measures but still getting same issue when trying to refresh online.

[SylviaJ]

I also have the same issue. Even after removing all calculated tables and columns, it still shows a failure when refreshing data. Has anyone achieved a successful refreshing result after removing calculated tables and columns? 

[Maieev]

I wish this feature of refreshing a Power BI dataset that includes a calculated table that depends on another table that uses Direct Query mode to connect to an Analysis Services data source would be available. It was in 2022 and it should be brought back again :). 

[Anonymous]

I don't have any calculated tables using direct query-sourced tables, and I still get this error. Do you have any idea how to fix this?? 

I have one calculated table that I am creating using an import model sourced data.

[ERD]

Hi @Anonymous,

It's also worth noting that Power BI recently introduced the ability to refresh datasets that include calculated columns, so you may want to consider using calculated columns instead of a calculated table if that meets your needs.

---

I tried to use calculated columns with no calculated table and still receive the same error:

"We cannot refresh this dataset because the dataset contains calculated tables or calculated columns based on data from a Single Sign-on (SSO)-enabled Direct Query data source. Please configure the dataset to use an explicit connection with granular access control to access this data source and then try again."

---

There is no point in connecting the dataset if we cannot use it for calculations and then refresh own dataset. Please, advice how this can be resolved.

Thank you

Update

After I completely removed the existing dataset and report from the workspace and published it again, the dataset refresh started to work. Calculated table was removed and I used calculated columns instead. Please, take into account that the report link will change in this case.

[planbmedia]

I cant belive that Im fcing this in 2024

[jtao]

Also facing the same problem when semantic model is refreshing.

We cannot refresh this dataset because the dataset contains calculated tables or calculated columns based on data from a Single Sign-on (SSO)-enabled Direct Query data source. Please configure the dataset to use an explicit connection with granular access control to access this data source and then try again.