SSAS Cube Source: Calculated Member invisible in PowerBI

[Anonymous]

Hello,

I am connecting to a SSAS Cube which has Calculated Members added to one of its dimensions.

These members are visible within SSMS Browse interface as well as in Excel PowerPivot, but do not appear in PowerBI.

I have found an old thread describing a similar problem ( https://community.powerbi.com/t5/Integrations-with-Files-and/Analysis-Services-Calculated-Members-no... ) and read the article linked in the solution.

After examination of the cube definition, I have confirmed that the calculated members are not in a user hierarchy, but an attribute hierarchy.

It is also not located in the key attribute.

It is also not located in a parent-child attribute.

The calculated members are created in SSAS Calculations tab of the cube via MDX scripting.

Any advice would be appreciated!

Cheers

Gerrit

Attached the definition of the calculated members:

 

Correct behavior in Excel:

 

Calculated members missing in PowerBI:

 

[v-yuezhe-msft]

@Anonymous,

Could you please post the dimension structure of Dimscenario and design property of the calculated members in Visual Studio?

And is there any possibility that you can share the abf file so that I can test?

Regards,
Lydia

[Anonymous]

Hello Lydia,

because the cube contains sensitive data, I am unfortunately not able to share an abf file.

As for the dimension structure, I have attached screenshots of that as well as the complete data that the dimension is built out of. I can share this since it is very generic and unspecific information.

I wanted to post images right into this thread as I did yesterday, but somehow it does not work anymore. So instead I uploaded it here: https://imgur.com/a/L8rPnKg

Best Regards

Gerrit

[Anonymous]

I have been taking a look at the queries that PowerBI fires against the cube and noticed a strange discrepancy between a dimension with calculated member that works just fine, and the dimension in question which does not show the calculated member in Power BI.

The working dimension, called TimeCalculation (it is set to omit the dimension name, hence only Comparison attribute is shown in the query) works fine with the following query being sent to the cube.

The not working dimension called DimScenario sends a different query which results in the calculated members not being displayed.

After this I noticed that our dimension in question had a NameColumn set that was different from the KeyColumn. Experimentally I removed the NameColumn which subsequently resulted in a different, but still failing query to be sent to the cube.

How does Power BI decide what type of query to use and what are the determining factors?

I am trying to spot the difference in how the working dimension and the failing one are implemented in hopes to make changes to the failing dimension as to that it works as desired.

Best Regards

Gerrit

[Anonymous · Accepted solution]

Hello,

Just as a quick update: We have not been able to figure out what exactly makes PowerBI make the distinction between the two above mentioned cases.

The last assumption was that it is somehow tied to the IsAggregable property and the Default member of the dimension.

However, we did not further go down that route and instead implemented a workaround where we created additional rows in the data source view and then added only the calculation to the cube script:

([DimScenario].[Value Type].[Variance] =
IIF(ISEMPTY([DimScenario].[Value Type].&[010]) AND ISEMPTY([DimScenario].[Value Type].&[020]), NULL,
IIF(ISEMPTY([DimScenario].[Value Type].&[010]), 0, ([DimScenario].[Value Type].&[010])) -
IIF(ISEMPTY([DimScenario].[Value Type].&[020]), 0, ([DimScenario].[Value Type].&[020]))
)
);

This way the members appear in all front end tools we employ including Power BI.