Calculated Members with multiple Attribute Hierarchy - MDX

Question

I found this little code where it dynamically calculates TOTALs for all Dimension/Hierarchy I want.

This is close to what I need but will not work for Dimensions that have different number of Hierarchy Levels (Attribute Hierarchy). Current Code only works if there is only one Attribute Hierarchy because of CurrentMember.Parent. I could use CurrentMember.Parent.Parent for Dimension.Hierarchy that have two levels and so on but would not work for the the ones with only one Attribute Hierarchy (Level).

CALCULATE;     
CREATE MEMBER CURRENTCUBE.[Measures].[Total On Hand Amount]
AS ([Measures].[On Hand Amount],Axis(1).Item(0).Item(0).Dimension.CurrentMember.Parent), 
FORMAT_STRING = "#,#", 
VISIBLE = 1  ;

I would like to make this MDX code work for any Dimension.Hierarchy regardless of number of Attribute Hierarchy (Level/s).

Any help is appreciated!!

Answer 1

You can use the ancestors function instead of parent. It takes a dimension parameter and a second parameter which shows how many levels you want to get (how deep in the tree to go). So if you know how many levels your dimension has you can use something like:

Ancestors(Axis(1).Item(0).Item(0).Dimension.CurrentMember, 5) 

Instead of a number you can also add a dimension level as a second parameter. Then it will go as deep as the dimension level specified - so if you add the root dimension level it should get to there

Answer 2

(Axis(1).Item(0).Item(0).Dimension.Levels(0).Item(0), [Measures].[On Hand Amount])

Above gave me the correct answer, total for a measure for dynamic selection of any dimension but this MDX calculation would not work from PowerBI(DAX) report, which is merely PowerBI's limitation.

I got the TOTAL working now with this -

SCOPE(DESCENDANTS([Warehouses].[Warehouses],,AFTER));          
[Measures].[Total On Hand Amount] = (ROOT([Warehouses]),[Measures].[On Hand Amount]);
END SCOPE;

I just have to repeat this SCOPE for each [Dimension].[Hierarchy] in the cube to make the TOTAL work for any selection including multiple dimensions from Power BI. It does not have dynamic functionality like Axis() did, but it yields the result I needed.

Hope this would help someone else too!!