具有多个属性层次结构的计算成员-MDX [英] Calculated Members with multiple Attribute Hierarchy - MDX

问题描述

我发现了这个小代码，可以为我想要的所有维度/层次动态计算TOTAL.

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

这接近于我的需要，但不适用于具有不同层次级别(属性层次)数量的维.由于CurrentMember.Parent，仅在只有一个属性层次结构的情况下，当前代码"才有效.我可以将CurrentMember.Parent.Parent用于具有两个级别的Dimension.Hierarchy，依此类推，但不适用于只有一个属性层次结构(级别)的那些.

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  ;

我想使此MDX代码适用于任何Dimension.Hierarchy，而不考虑属性层次结构的数量(级别/秒).

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

感谢您的帮助！

推荐答案

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

上面给出了正确的答案，总和，可以动态选择任何维度，但是此MDX计算不适用于PowerBI(DAX)报告，这仅仅是PowerBI的局限性.

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.

我现在已经在TOTAL上使用它-

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;

我只需要对多维数据集中的每个[Dimension].[Hierarchy]重复此范围，以使TOTAL对包括Power BI中多个维度在内的任何选择有效.它没有Axis()那样的动态功能，但是可以产生我需要的结果.

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

这篇关于具有多个属性层次结构的计算成员-MDX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！