Error when updating dataset from PowerShell of from PowerBI Service

[ocasares]

Hi,

Since some days ago we have started to get errors when trying to update the PBI Service Dataset from PowerShell or from the Service.

This has been working fine since last month and no change has been performed in any of the models we have uploaded. All of them have started to give the same error message at the same time:

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-NORTH-EUROPE-L-PRIMARY-redirect.analysis.windows.net

So the automatic update is now working now. However, there is no issue if we perform the update from the Desktop.

 

[v-xiaoyan-msft]

Hi @ocasares ,

Based on the above information, if you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[ocasares]

Hi @v-xiaoyan-msft 

Thanks very much for your answer.

Actually, I'm not getting a ""Single sign-on (SSO) credential is not provided" error message. The error we got is "Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery."

The fact is that this functionality was working well till end of March and from April this was stop working, without any change from our side. 

We could publish and update the dataset both from Power BI Desktop or from the PowerShell .

But now this is failling when attempting to upload the dataset from PowerShell.

This is the excerpt of the script that now is provoking error:

$updatecube = @"
{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "my_dataset_name"
      }
    ]
  }
}
"@
 
Invoke-ASCmd -Server $server -Database:$db -Query:$updatecube -Credential $Credential

[lbendlin]

We usually run the custom cmdlet

Login-PowerBI
$updatecube = @"
{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "my_dataset_name"
      }
    ]
  }
}
"@
 
$res=Invoke-PowerBIRestMethod -Url 'groups/$groupid/datasets/$datasetid/refreshes' -Method Post -Body $updatecube 

 Have you tried that?

[ocasares]

Same 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 URI: WABI-NORTH-EUROPE-L-PRIMARY-redirect.analysis.windows.net
Activity ID: 00000000-0000-0000-0000-000000000000
Request ID: 524c7c66-7e7d-48b9-8012-72fd695e09f3
Time: 2022-04-14 06:55:57Z

We are going to make a try with PowerAutomate...

But, nevertheless, it does not explain why we can refresh from PowerBI Desktop and publish this manually in the Service, and cannot be done with the same service scheduler or with PowerShell.

[v-xiaoyan-msft]

Hi @ocasares ，

This is a documented limitation:

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

• Calculated tables are not supported in the Service using this feature. Attempting to perform a refresh on a dataset with a calculated table or a calculated column which references a Direct Query data source will result in a "Single sign-on (SSO) credential is not provided" error message.

Best Regards,
Community Support Team _ Caitlyn

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

[charrington]

In our case we have no calculated tables. But it seems that the local data table created for time intelligence might interfer here. Could they be interpreted as calc table, then throw the error? I suggest this might be a bug. Plus the other people mentionned it was working before. 

I have described my case in more details here, in case anyone is interested.

Thanks