Refreshes for Datasets containing Calculated Tables over Direct Query to AS started failing Mar 30th

[AugustoChaves]

Hello, 

Refreshes of datasets that contain calculated tables from DQ to AS started failing March 30th, 2022. This worked completely fine until March 29th which points to a change at Power BI service side. The error message is as follows:

{"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}}}

[C4YNelis]

This is mildly infuriating to say the least...

Just changed the name of some workspaces (introduced seperate test and production environments) and had to therefore update the sources in one major report that has taken a lot of time and effort to develop and which accesses many different sources. Nothing else changed, just the name, which is a one-click update (change datasource) in every other report.

It has always worked just fine up till now (it refreshed just fine too), using field paramaters, a calculated column and a several calculated tables. Now, republishing with the new name, I get an error and it won't update anymore. Nor can I revert my changes...

I understand why I'm receiving this error, but that doesn't help me much unfortunately.

[asjones]

Curious has anyone heard any comments about this issue from any of the more public Power BI / Fabric people that are active and respond on Twitter or other forums?

We have been "taught" to seperate out things... have a seperate data model vs report then combine that with some other data set. Then MS Releases a feature like Field Paramters. Field Paramters are great. However they are not something I would setup in my core data model in many cases as they involve a slicer etc. Thus if you have composet model things are not available.

https://community.fabric.microsoft.com/t5/Desktop/Field-Parameters-Conflict-with-Composite-Models/m-...

[asjones]

Wow, just hit this issue also. Very disappointing ... i was happy updating a report, reday to publish and bam
"Publishing to Power BI"
"This dataset contins calculated tables or calculated columns that refer to remote tables, which will result in refresh failures after publication. Are you sure you want to continue publishing?"

Is there a formal ticket on this or just forum complaints? Any discussions or comments from MS PMS or others on Twitter?

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Calculated tables aren't supported in the Service using this feature. Attempting to perform a refresh on a dataset with a calculated table or a calculated column that references a DirectQuery data source will result in a "Single sign-on (SSO) credential isn't provided" error message.

[apodo01]

Hi!

The same thing is happening to us. Hit the same wall.

We are dealing with an issue refreshing a dataset in a composite model, with an opened support ticket since June 18th.

We spent 3 months building the composite model and couldn't get it to update inside the service.

And now, in version PBI desktop version 2.19.986, they added this warning popup.

It's a shame.

Months of work wasted.

Best,

Adrian.

[asjones]

A few related items from the ideas pages. Please go vote for them, addcomments and share if there are other ideas.

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=83567bed-3451-ec11-a3f3-281878de4cf8

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=6b14127d-4400-ee11-a81c-6045bdb98602

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=4c2702a6-e4b1-4240-a3f5-c1baf31e4204

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=22546afa-efd2-ed11-9139-501ac5249853

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=6c461101-3ac7-46ad-a442-fe2f2e2976ea

[charrington]

This is a shame. Voted.

[AugustoChaves]

Hello, a year later and I still see replies here. Sorry folks, I've got the official answer and this scenario is unsupported. Not sure if this can change in the future but for now you can't use it. Why was it working before? Total mystery.

[dbeavon3]

@AugustoChaves  Thanks for the update.  Can you please post the referencing information, that identifies the bug you reported (eg. last five digits of the ICM# or something like that)?  This is probably something that customers may want to vote on, or revisit in the future.  It would be helpful to get a jump-start on my own discussions about this with them some day. 

The confusing documentation is something that bothers me about this.  I think they did a halfhast job of the docs ... and it is possible that they were doing so deliberately (ie. leaving their options open to change the implementation at a later time).  Another unfortunate thing is that we experience a difference between the service and the desktop.  Even in the docs they differentiate behavior in the "Service", in comparison to the behavior in the desktop

"Calculated tables aren't supported in the ***Service*** using this feature..."

see:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#considerations-a...

It seems like a cruel joke it is to allow this stuff to work on the desktop, and allow developers to build a solution, only to find out that it fails when deployed to the service.  I'm guessing that on the desktop the whole solution runs in the context of the user's credentials, so there is no complexity related to the security infrastructure of the service/dataset/gateways/etc.

Insofar as complexity goes, I suspect the refreshing of a calculated table for Direct-Query-to-AS suffers from security challenges during refresh operations (some part of the dataset refresh that needs to occur outside of the context of the end user's credentials).

On the desktop, when "msmdsrv" is running in under the user's credentials, the security challenges don't really apply.  It is possible that the differences we see in the desktop model are actually intentional, and they allow some powerful & flexible features to exist in certain related Microsoft tools, eg. "Power Pivot" in Excel.

Just guessing

[AugustoChaves]

Hi, I only have the number of the support ticket that was opened last year, 2203310040005513. Unfortunately I don't have the ICM#.

[andrewtuplin]

I've tested this a few different ways and I've found the specific condition that causes refresh to fail. 

If you create a table in DAX that references a Power BI Dataset (i.e. a dataset outside of the the report being used as a dataset) refresh will fail. 

For example. Say I have a dataset in the Service called 'DIM Date'.  In my report, I can add that Power BI Dataset as a datasource and, in fact, I can add multiple external data sources, and I can also add local data sources in both DAX and Power Query.  What I cannot do is create a table in DAX that references a Power BI dataset.  For example, if I create a new table as: 
MyDate = 'DIM Date', the report will not refresh in the service. 

[frw]

I just wanted to say thanks, because this advice solved the problem for me. I had a calculated Date table based on a summary table accessed through DirectQuery. Switching it to a hard-coded calendar range, while inelegant, fixed the error message I was getting.

It's bonkers to me that this issue exists in the first place. I can reference these DirectQuery tables in measures and manipulate them in so many ways, but any reference to them in table/column construction screws the whole thing up.

[andrewtuplin]

This is an issue for me as well. 

[PetrK]

Hi all, do you have any news related to this topic?
I spend last two week by building report with calculated tables based on DQ and finally I get same error as you. 

Which solution you used to step around it?

[Prateeksha11]

Is there any update/solution to this? Still facing this error currently and need a fix for it.

Thanks,

[rajeswar_j1022]

Hello

Are you finding any solution for this issue, I am also facing same issue.

Would appreciate if a solution is identified.

Much Appreciated.

[kymramosrpo]

Hello everyone,

I, too, built a couple of calculated tables with data directly querying a Power BI dataset, only to find out that the report will fail to refresh once published in the service.

The workaround I came up with is not perfect but it works (also, this alternative might only work for smaller datasets...):
(1) create a flow in Power Automate to get the data from the PBi dataset and export as CSV* (way faster and simpler than exporting as an Excel spreadsheet) > (2) query the CSV file instead

My flow uses the Recurrence trigger, I just arranged the order of refresh and trigger schedules.

*Reference: Automatic exports of Power BI data 🤖 | Run a query against a dataset - YouTube, Curbal, @ruthpozuelo, Jun 20, 2022

I hope this helps!

[BeAStar]

was there any solution to this??? I've had reports that were auto refreshing without any issues and suddently they show this error.

[faroukwahsh]

There's already an Idea posted by a Microsoft employee reagrding this Premium_ASWL_Error - Single sign-on (SSO) credenti... - Microsoft Power BI Community

Please go in and vote on it. 

[AugustoChaves]

This is not the same thing. Calculated Tables from a DQ Dataset used to work until March 29th, 2022 and then stopped working after an update. SSO is not in play here.

[faroukwahsh]

Odd, refreshing a report with a calculated table that references a DQ to a PBI Dataset never worked for me on the service. Now I am thinking it might be source specific since you mention this wasn't an issue for you before.