Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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-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:

 

 mdx.png

 

 

Correct behavior in Excel:

 

 excel.png

 

 

Calculated members missing in PowerBI:

 

 powerbi.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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.

 

TimeCalcDAXQuery.png

 

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

 

DimScenarioDAXOld.png

 

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.

 

DimScenarioDAXNew.png

 

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
Not applicable

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors