cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cheidemann New Member
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
Community Support Team
Community Support Team

Re: SSAS with Direct Query and Calculated Columns

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.
mpozgaj Frequent Visitor
Frequent Visitor

Re: SSAS with Direct Query and Calculated Columns

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 Frequent Visitor
Frequent Visitor

Re: SSAS with Direct Query and Calculated Columns

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

mpozgaj Frequent Visitor
Frequent Visitor

Re: SSAS with Direct Query and Calculated Columns

@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 Frequent Visitor
Frequent Visitor

Re: SSAS with Direct Query and Calculated Columns

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 272 members 2,667 guests
Please welcome our newest community members: