Forum Discussion
SSAS Cube Source: Calculated Member invisible in PowerBI
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-not-on-Measures-not-showing/td-p/180382 ) 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:
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.