CODEWITHSUNDEEP
sql-serverssasmdx
eAlchemist
eAlchemist

Reputation: 106

MDX - Count(.children) > 0, but .FirstChild.Name is blank

I'm trying to create a calculated member that returns a string of the value when there is only one distinct child, "Multi" when there are is more than one distinct child, and "None" when there are no children. Specifically, I'm having trouble returning the case where there is on distinct child name. Here is my calculated measure:

WITH MEMBER [Measures].[SSN] AS
  CASE
    WHEN DISTINCTCOUNT([Item].[Season Code Name].Children) = 0
        Then 'None'
    WHEN DISTINCTCOUNT([Item].[Season Code Name].Children) = 1
        Then [Item].[Season Code Name].FirstChild.Name   --.Value is null
    ELSE
        'Multi'
  END

The else returns a blank, but if I use [Item].[Season Code Name] in the crossjoin, the children show up as expected.

What am I doing wrong?

Thanks in advance for your help!

Upvotes: 0

Views: 1480

Answers (1)

ic3
ic3

Reputation: 7680

This is a classical, I do that all the times :-), missing currentMember after the hierarchy.

 WITH MEMBER [Measures].[SSN] AS
  CASE
 WHEN DISTINCTCOUNT([Item].[Season Code Name].currentmember.Children() ) = 0
    Then 'None'
 WHEN DISTINCTCOUNT([Item].[Season Code Name].currentmember.Children() ) = 1
    Then [Item].[Season Code Name].currentmember.FirstChild.Name   --.Value is null
 ELSE
    'Multi'
 END

Upvotes: 2

Related Questions