Adding measure while using SSAS tabular datasource corrupt the whole report

[Dars]

Hello,

We have Power BI Server installed locally.  We are using Power BI for Desktop optimized for Power BI Server (June 2017 version).  We ran into some issues while creating report using an SSAS Tabular datasource.

We have a cube with +- 30 measures.  A colleague created a PowerBI report with many different visuals / filters and everything works fine.  As soon as she adds a new measure within the report, everything starts messing up where various visuals displays an "(X) Can't display the visual.  See details" but surely, there's never any details to see (unexpected exception occured).

My first thoughts was that there was something wrong with the mesure definition that was added to the report.  So what I did is take an existing measure from the cube and I've added a new measure in the report with the exact same definition except the define a new measure name.

So now, I have two treemap displaying both measures.  As soon as I change the year from a slicer, everything starts messing up while it was not the case at all before I add a measure locally to the report.

So my question, is there known issues about adding measures to a report that is based on an SSAS tabular?

Thank you for any help.

Best regards

[Anonymous]

I have a Dataset created in PowerBI Service that is based on an SSAS Tabular model.  The Dataset was created with 'Connect live' option.

Then I open up PowerBI Desktop, connect successfully to the Dataset in PowerBI Service and begin to create a report. I Selected something from my Date dimension and several measures.  This worked fine.  But there's was one particular Measure that was causing the report to fail with the error message: 'Can't display visual'.

I asked one of my colleagues at work to try the same set of actions that I did and for her it worked without any issues even when adding the measure that made my report crash.  Weird!

At first I thought it was some permission issue but then if that is the case then I shouldn't have been able to connect to the Dataset and create the report in the first place (at least with the measures that were working).

I really don't know what the cause this but there was one action I took that solved the issue.  I simply went to my SSAS Tabular model and did a Full Process through SSMS.  And that solved the problem.  But I really don't have an explanation for why this worked because if it did work for my colleague then why not for me?

[sdjensen]

Hi @Dars,

We you able to solve this problem? I am currently experiencing a very similar problem.

Setup

SSAS Tabular cube on Analysis Services SQL 2016 standard edition (build 13.0.4206.0)

A live connection is created through Power BI Enterprise gateway (14.16.6697.1)

Building a Power BI report with Power BI Desktop (septempber 2018) using dataset from PBI workspace

Everything is working fine until I created a report calculated measure based on 2 of the measures in the tabular model.

The measure I create is very simple Unitprice = DIVIDE([Sales Amount]; [Sales Quantity])

But when I add this measure my original [Sales Amount] measure stops working (and all other measures that use this in it's calculation), but measures that doesn't use [Sales Amount] works fine, so it's a "Semi" crash of my cube. If I reprocess the cube everything works again until a query is send using the report calculated measure.

[Dars]

Unfortunately no.  It's clearly a bug.  You either have to build your model directly on tables or add the measures in your cube to avoid this problem.  Funny that the same measure works in the cube but screw up the whole thing when you add it on top of your cube directly in the model 😞

[sdjensen]

@Dars,

Thank you for your reply - I guess I will have to investigate this further to see if I can find a way to reproduce it everything and then report it to Microsoft.

[sdjensen]

@Dars

I have now created an issue with this bug, if you want to support it to give it some attention please give it a vote up:

https://community.powerbi.com/t5/Issues/Creating-Report-Measures-in-PBI-Desktop-make-Analysis-Servic...

[muchinski]

Hello @Dars and @sdjensen,

Just for a quick checkpoint: I've downloaded the files on the bug report, restored the database and used the Power BI file 20181004_ReportMeasures_4_TestReportWithReportMeasureDirectSSAS.pbix on the latest version of Power BI optimized for Report Server, as well as the latest cloud version. Everything ran fine here.

The difference is that my environment is running with the latest PBIRS version ( 2.61.5192.641 64-bit (August 2018)) and also my SSAS is 14.0.204.1 (SQL 2017 CU5) - It is not the last CU because of a bug on SSAS from CU6 that closes a connection when updating the model...lol...

Nevertheless, version June 2017 is not supported anymore.

My thoughts here after reading @sdjensen post: It should be an SSAS problem. Did you try to use SQL Server Profiler to trace your queries on the SSAS side and check if it gives you some error message?

[sdjensen]

Hi @muchinski,

I don't know what you are refering to with June 2017?

You are using SQL2017 and yes, I have no issues with this feature on a SQL2017, this issue is related to SQL2016 and I can't find anywhere in the documentation, that this feature requires SQL2017 or a certain build of SQL2016 - I will be more than happy to test on a SQL2016 SP2 (or later since SP2 had a bug related to SSIS), but I need more space on my test server before I can upgrade it.

[muchinski]

Hi @sdjensen,

on the original post, @Dars mentioned he was using June 2017 version

[sdjensen]

@muchinski - okay, but that is a very old post.

[sdjensen]

@Dars

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.

[Anonymous]

Dars,

Try updating your version to the October 2017 Release version and try again. Lots of updates were added since June 2017 in the October release

Best Regards

[Dars]

I have updated to October version, still no luck.  I have also tried using the Power BI Desktop regular version to see if the result was different, again no luck.

I really think that this is a serious bug.  I have create a simple new tabular cube with only one measure 

:

YTD Sales Amt:=
CALCULATE(
	SUM(Sales[SalesAmount]),
	FILTER(
		ALL('Dates'),
		Dates[CalendarYear]=MAX('Dates'[CalendarYear])
			&& 'Dates'[FullDate] <= MAX('Dates'[FullDate])
	)
)

This measure works fine without any problem in Powerpivot and also Power BI.  Then when I add a measure manually in Power BI while connected to my SSAS tabular cube :

Top 3 Category = 
IF (
    ISBLANK (
        VAR Top3 =
            RANKX ( ALL ( Products[Category] ), [YTD Sales Amt] )
        RETURN
            IF ( Top3 <= 3, Top3, BLANK () )
    ),
    BLANK (),
    [YTD Sales Amt]
)

Again, it works fine.  But when I change my slicer value from 2017 to 2016, then one of the graph starts displaying (X) can't display the visual.  If I remove the measure I manually added, I still see this (X) even on the graph (Card) where I only show the YTD amount.  It simply doesn't make any sense 😞

[Anonymous]

We had a similar issue with a SSAS MultiDimensional data source. Built to a set os visuals. then added a local measure. Local meausre wokred fine until you changed a slicer. Removing the local measure still left the visuals broken. Restarting PBI Desktop didn't seem to help. The original (un amended) PBIX still gave us an error.

We had to restart the SSAS MD service on the server to get the visuals to work again (it was all direct query node)

The best thing was we could open the same PBIX on two different machines, leave one unchanged, produce the crash on the second mahcine and the unchanged one would now give an error! That's how we figured out it was happening somewhere on the SSAS MD server. 

We tried to work up a repro but got pushed onto other work before we could get something concrete submitted to MS.

[Dars]

Thanks for your reply.  It doesn't really fix my problem but at least I know that I'm not crazy 🙂

Therefore, we will avoid adding manual measures in Power BI report on top of a cube for now 😞

Cheers

[Anonymous]

HI @Dars,

I might be misunderstanding but I think the problem is because you are using a SSAS Tabular datamodel, not a local PBI datamodel. Thus you need to add the measures to the remote SSAS data model. Same for SSAS MD I would have thought?

If you have chosen the SSAS Tabular data connection my understanding is that the data is coming direct from SSAS, not being stored in the local PBIX, so not able to add measures or calculated columns in the local data model.

Anybody else agree / disagree?

Rob

[Dars]

Hello Rob,

I understand but I would not allow to add local measure if the source doesn't permit it.  Whenever a user have a specific need for a report, I don't want to add tons of measures covering all theses particular needs in the cube for a single report.  We already have a lot of measures and I like having the flexibility defining ad-hoc measures in the PBI model.

The funny thing is that when add a local measure and it works, so the mechanic is there.  But everything gets messed up whenever a slicer selection is changed.

[Anonymous]

The following article suggests that you can't add measures when using a Live connection.

https://community.powerbi.com/t5/Community-Blog/Power-BI-Live-connection-vs-Import-comparison-and-li...

However the following article says you can (section pulled out)

https://docs.microsoft.com/en-us/power-bi/desktop-analysis-services-tabular-data

Question: If I created a live connection, can I edit the model or query in Power BI Desktop?+

Answer: You can create report level measures in the Power BI Desktop, but all other query and modeling features are disabled when exploring live data.

This second version is why PBI Desktop allows you to add measures in a Live connected report, though the table page and data model are hidden. Shoing the Data model would be crazy useful, as would sample data. Why these aren't shown in a READ ONLY mode I'm not sure, they are inherently there in the backend, they're just obviosuly not being passed to what is now a dumb client app!  It's just that as these measures get more complex (changing the context for example) they seem to cause issues on the SSAS backend. Like behaving badly as described above.

Like many early versions of MS software its a bit rough around the edges... 

[Anonymous]

Hi @Anonymous,

I think the question / Answer you highlight only applies if you have IMPORTED the data into a local PBI data model, and are then effectively working off-line to the SSAS Tab data. And Not if you are using a Live SSAS Tabular connection. Reading the introduction to the link you provide seems to confirm this, but the answer to the question you highlight is not very well phrased.

My opinion remains that if you use the Live connection, and thus get updated SSAS Tabular data everytime you load the PBI report you cannot create local measures and calc columns.

Rob

[Anonymous]

HI Rob

You can create measures in PBI Desktop (it's not actually disabled) and the question as it is answered suggests that this is by design as it says "live connection" which is not "importing the data"

Question: If I created a live connection, can I edit the model or query in Power BI Desktop?

Answer: You can create report level measures in the Power BI Desktop, but all other query and modeling features are disabled when exploring live data.

So local report measures are "supposed" to work when connected using a Live Query  to a SSAS data source. If the goal posts have moved subsequently then that's entirely fine, they just need to disable the add measure capability. Maybe that's what will turn up in the imminent release. Though it would be a serious step back in capability as importing our reporting cubes is impractical as no one is fitting that much data onto their laptop instance.