Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cheidemann
New Member

SSAS with Direct Query and Calculated Columns

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

 

 

 

 

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

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:

 

DirectQuery_CalculateColumnerror.PNG

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

 

Thanks & Regards

Anonymous
Not applicable

@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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors