Forum Discussion
SSAS Cube Source: Calculated Member invisible 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.
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
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
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
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.