Creating Report Measures in PBI Desktop make Analysis Services semi crash

[sdjensen]

Hi,

I am working as a BI Consultant at a Microsoft Partner and I first discovered this bug at a customer and I have now been able to recreate this on my test/development server.

First some technical stuff:

Power BI Desktop 2.62.5222.601 64Bit (September 2018)

On-Premise Data Gateway 14.16.6792.1 (August 2018) (I am not able to upgrade this, due to the fact that the new installer requires Windows feature that are currently in preview, but our IT department don't want preview features on servers - but this is another matter)

Analysis Servers (Tabular) 13.0.4001.0 (Standard Edition)

Bug Description:

Whenever I try to create a Report Measure in Power BI Desktop using a live connection to a Dataset on Powerbi.com (also tried with direct connection to my SSAS model), it semi crashes Analysis Services.

As a test, I tried with a very simple measure. In my SSAS model I have 2 measures [Sales Amount] and [Sales Quantity] and then I want to calculate [Sales Unit Price] as DIVIDE( [Sales Amount]; [Sales Quantity] ). However, if I create this report measure and start using it in a visual the original [Sales Amount] and [Sales Quantity] measures from the model stop working (of course including all other measures in the model that uses either of the measures in their calculation), but other measures like [Sales Cost] is still working. If I create a measure in the SSAS model with the exact same formula there is no problem.

To get the [Sales Amount] and [Sales Quantity] measures working again I either have to full process the SSAS data model or restart Analysis Services service. Then everything is working again until I open the Power BI file with the calculation and this file makes a query against the data model.

I have created a .zip file with .pbix files, event viewer errors and a backup of Tabular model

Download Files

Please let me know if there is more information you need to fix this.

[v-qiuyu-msft]

Hi @sdjensen,

I have restored your abf file in my tabular server version 13.0.4522.0, open the report report 4_TestReportWithReportMeasureDirectSSAS.pbix in Power BI desktop version 2.62.5222.761 and modify the connection to my tabular server, drag the measure Report UnitPrice = DIVIDE( [Sales Amount], [Sales Quantity] ) to column chart and pie chart visual, desktop and visuals works fine. 

I would suggest you update your both tabular server and Power BI desktop versions as ours then test again. 

Best Regards,
Qiuyun Yu 

[sdjensen]

Hi @v-qiuyu-msft,

It's not the Report measure that is the problem - the calculated measure works just fine, but the measures used in the calculation stop responding. In the example above [Sales Amount] and [Sales Quantity] stop working.

Kind Regards,

Søren

[sdjensen]

Hi @v-qiuyu-msft

I have now found a solution - I upgraded my SQL2016 to SP2 which solve the problem, I did however have to upgrade to SP2 CU3 to have a full working solution. SP2 have a bug with processing Analysis Services models from SSIS.

[v-qiuyu-msft]

Hi @sdjensen,

Glad to hear the issue is gone with fix on SQL side. It's recommended to apply the latest fix to avoid potential issue. 

Best Regards,
Qiuyun Yu