SSAS with Direct Query and Calculated Columns

[cheidemann]

Hello,

When I add a calculated column to the Report, that I have did on SSAS Model, I'm getting this error :
The expression referenced column 'alarms'[HourOfDay] which does not hold any data because it needs to be recalculated or refreshed.

I have tried add more calculated columns and the same error shows for the visualizations that is using a calculated column.

Looks like Power BI doesn't support models with calculated columns and DirectQuery enabled.

Can someone confirm that ?

Details:

Tabular Model 

Compatibility Level: SQL Server 2017 / Azure Analysis Services (1400)

DirectQuery Mode: On

Best regards,

Charles

[v-piga-msft]

Hi @cheidemann ,

I'm sorry that I don't have the test environment for Azure Analysis Services.

In addition, I'm afraid that there are only two ways (import and Connect live) for us to connect the Azure Analysis Services Database in Power BI Desktop.

For you error message, please try this ways below.

1. Refresh the data source after you connecting the data in Power BI Desktop.

2. Save the pbix after loading the data and open it again to check if the issue will be fixed.

Besides, please check if you have installed the latest version of Power BI Desktop 2.68.5432.841.

Best  Regards,

Cherry

[Anonymous]

Hello @v-piga-msft 

I'm working with @cheidemann on the same project.

SASS and DirectQuery is working for us with regular columns, but the calculated ones are broken.

The issue is only with calculated columns.

Thanks!

[ptewary]

Hi,

Has there been any development in this issue? I am facing the same problem - however, the documentation for Azure Analysis Services promises that Calculated Columns are enabled with DirectQuery mode.

I have processed Table/Partitions/All, Re-built and Re-deployed the model, refreshed the data preview in Power BI. It still shows the same error:

Can someone confirm that this is in fact not a capability in Azure Analysis DirectQuery mode with Power BI?

Thanks & Regards

[Anonymous]

@ptewary we gave up on Calculated Columns in SASS with DirectQuery. We ended up transfering them to T-SQL in SQLServer and doing all the logic there. We also noted quite a performance hit with Direct Query in our reports.

Documentation on this is quite misleading.

[ptewary]

I got some feedback from the Microsoft Support. Turns out that after creating the calculated column in SSAS and deploying the model, we need to go into SQL Server Management Studio and Process our Azure Analysis Services Database (connect to the Azure AS server -> right-click your DB name (which you deployed from Visual Studio/SSAS)

-> click Process). After carrying out this step, the calculated columns work correctly in Power BI.