Forum Discussion
Refreshes for Datasets containing Calculated Tables over Direct Query to AS started failing Mar 30th
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}}}
62 Replies
i changed the workspace settings -> Data Connections -> tick on the box and it works
Enable granular access control for all data connections
Enforce strict access control for all data connection types. When this is turned on, shared items will be disconnected from data sources if they're edited by users who don't have permission to use the data connections.HoaNguyen is right! tested and it works. Thank you so much for sharing it!
Regarding to "Enable granular access control ", beside the config in workspace level, we also can do it on Dataset setting /Data connections (more specific) or Tenant setting (more widely).
see detail from https://powerbi.microsoft.com/en-us/blog/enabling-granular-access-control-for-all-data-connection-types/
Hi Anonymous and all,
I am happy that when ticking "Disabling this item from datasources..." it works
However the explanation given by the microsoft post isn't quite clear for me.
https://powerbi.microsoft.com/en-us/blog/enabling-granular-access-control-for-all-data-connection-types/Is it only me? this feature is more around controlling which users can "modify" the dataset and better control (from IT dept let's say) the connections. But in this specific thread and this scenario we are not talking about modifying the dataset right? or am I confused? Is this because in the composite model when adding a calc table over a DQ it "modifies" the dataset?
I like to understand things and not only be happy when it works after clicking a button (although I am really happy here 😃)
Also is seems it only works on our side when enabling both Authentication and SSO:
at first I used only SSO but it didn't work. When enabling both it can refresh the model with mix of import/DQ and calc tables over DQ.
Any comments/explanations are welcomed! Sometimes the community is the best way to find answers! 😉
Thanks
If your data source is a semantic model (dataset), you will get this error if you create a calculated table. The solution is to create the calculated table in the original report/dataset (and publish/refresh), or to use an alternative solution (and delete the calculated table).
Worked for me!
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.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.
Hi GilbertQ, the gateway was already updated to latest version, March 2022, but that shouldn't have an impact since this is DQ to a Power BI Dataset (DQ to AS).
Thanks
Posting to keep the thread alive.
Still facing the issue IN 2024. Any roadmap to have a resolution on this?It could possibly be that there has been a change in the underlying calculated table?
There are often is changes to the Power BI Service.
What happens if you try this on PBI Desktop does it work there too (Using the latest version of PBI Desktop)?It seems like a chance at the service is the cause. In total 5 reports stopped working at the same time and there were no changes in any of them. I have a ticket opened but still no solution. Thanks!
Hi AugustoChaves , any news or update from Microsoft? Did they communicate a timeline for the fix? Regards, Stijn
No timeline. Support is very poor and takes a long time to break 1st layer barrier.
I'm pressing for a date and will post here when I hear.
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 insteadMy 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, 2022I hope this helps!
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.
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:
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
Hi, I only have the number of the support ticket that was opened last year, 2203310040005513. Unfortunately I don't have the ICM#.
Solved this - randomly got the error on multiple reports that use SQL server on refresh.
In the data source credentials - I unselected the 'reviewers can access this data source with their own PBI identities in DQ'
and signed in again with SSO authorisation.
Th
Had to sign in a few times for this to work - but worked, and didn't have to change any calculated columns
Hello all,
just wondering was thie issue ever resolved by power bi?i'm starting to get the issue into a couple of my reports and im just wondering if it's a ME issue or a THEM issue.
if they didn't fix what was your work around?
regards,Ross
No work around that I am aware of yet.
It doesn't even support refresh through the Gateway.
Have run into the same issue with a calculated table build off of two DQ tables. The table was being used as a data source for a paginated report hosted on PB Service.
I do not have a solution for issue but have eliminated the calculated table by pulling it in as its own DQ table, and I'm no longer receiving the gateway error upon refresh. So to me this would point the issue to being the calculated table. I did not attempt to try creating the calculated table through an other manner within Desktop.
Would appreciate if a solution is identified or the issue is resolved that you update this thread with the outcome.
Much Appreciated.
Hi Nathan, I have a support ticket opened and it's confirmed it's a bug/requires a fix. I'll post here when I get a response. Thanks
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.
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.
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.
was there any solution to this??? I've had reports that were auto refreshing without any issues and suddently they show this error.
