Search code examples
ssasmdx

Adding OR into the FILTER function


We've got the following thanks to @FrankPI in this SO Post

WITH 
   MEMBER [Measures].[LevelName] AS
       [Employee].[Employee Department].CurrentMember.Level.Name
   MEMBER [Measures].[LevelNumber] AS
       [Employee].[Employee Department].CurrentMember.Level.Ordinal
   MEMBER [Measures].[MemName] AS
       [Employee].[Employee Department].CurrentMember.Name
   SET [Set_TargetEmp] AS
        {
        FILTER(
            [Employee Department].AllMembers,
                (
                InStr(
                    1, 
                    [Employee].[Employee Department].currentmember.name, 
                    "WC4") <> 0
                ) 
            )
        }
SELECT
    {
    [Measures].[MemName], 
    [Measures].[LevelName],
    [Measures].[LevelNumber] 
    } ON 0,
    Hierarchize(
        Generate([Set_TargetEmp] as e,
            Ascendants(e.Current)
            +
            Descendants(e.Current, e.Current.Level, SELF_AND_AFTER)
        )
    )
    ON 1
FROM [Adventure Works] 

I want to make one small change but the new member (Reuben H. D'sa) who is key 53, does not appear in the result set. Why?

WITH 
    MEMBER [Measures].[LevelName] AS
        [Employee].[Employee Department].CurrentMember.Level.Name
    MEMBER [Measures].[LevelNumber] AS
        [Employee].[Employee Department].CurrentMember.Level.Ordinal
    MEMBER [Measures].[MemName] AS
        [Employee].[Employee Department].CurrentMember.Name
    SET [Set_TargetEmp] AS
        {
        FILTER(
            [Employee Department].AllMembers,
                (
                InStr(
                    1, 
                    [Employee].[Employee Department].currentmember.name, 
                    "David P. Hamilton") <> 0
                ) 
            or
            [Employee].[Employee Department].currentmember.member_key = 53 //<<<<<<new bit
            )
        }
SELECT
    {
    [Measures].[MemName], 
    [Measures].[LevelName],
    [Measures].[LevelNumber] 
    } ON 0,
    Hierarchize(
        Generate([Set_TargetEmp] as e,
            Ascendants(e.Current)
            +
            Descendants(e.Current, e.Current.Level, SELF_AND_AFTER)
        )
    )
    ON 1
FROM [Adventure Works] 

Solution

  • It seems that currentmember.member_key = 53 does not return any matching result. I tried currentmember.Properties("Key0", typed) = 53, and that returned both employees.

    See the documentation on the Properties method and the documentation on intrinsic properties (sroll down to "Non-Context Sensitive Member Properties") for details.