Sum part of hierarchy based on second attribute from same dimension

Question

so I have this dimension which has a ragged hierarchy. The hierarchy is nice to navigate but a royal pain to search in (Excel is the front end, so you have to search manually in all 12 or so levels).

We created a separate "search" attribute with all the members of the hierarchy to search in - however, in this flat list the leaves will contain the proper data but the intermediate nodes will of course not be rolled up (in lack of hierarchical information)

My idea was to put a formula on the flat list to go look up its equivalent member in the hierarchy and get its value from there. Here's what I have - both approaches don't work unfortunately:

With

------ APPROACH 1: DESCENDANTS
Member [m1] As
Sum(
Descendants(
  Filter(
      [Dimension].[Hierarchy].Members
    , [Dimension].[Hierarchy].Properties("Key") = 
        [Dimension].[Flat List].CurrentMember.Properties("Key")
  ),, LEAVES), ([Measure].[MeasureHierarchy].CurrentMember, [Measures].[Amount]))


------- APPROACH 2: StrToMember + CHILDREN
Member [m2] As
Sum(
  StrToMember("[Dimension].[Hierarchy].&["+
    [Dimension].[Flat List].CurrentMember.Properties("Key")+
    "]").Children,
  ([Measure].[MeasureHierarchy].CurrentMember, [Measures].[Amount])
)

Select

{ [m1], [m2] }
On 0,

[Dimension].[Flat List].&[838]
  dimension properties member_key
On 1

From [Cube]
Where [Measure].[MeasureHierarchy].[SomeMeasure]

Both will always return null - if I query the Hierarchy directly, it works - just not if I use the flat list Any ideas?

BetterCallFrank BetterCallFrank · Accepted Answer · 2017-06-02T07:42:46

figured it out myself - had a spark of inspiration tonight :-D

the answer is as simple as:

with member
[m1] as
(
  StrToMember("[Dimension].[Hierarchy].&["+
    [Dimension].[Flat List].CurrentMember.Properties("Key")+
    "]")
  , [Dimension].[Flat List].[All]
)

...

Maybe that will help someone else too

Sum part of hierarchy based on second attribute from same dimension

1 Answers