Search code examples
ssasmdx

using a named set in a calculated member

I have a calculated field (getting a count) and a named set (defines a key) which I want to use in a new calculated member and return the count based on when the key is defined in that set.

So far i have tried this:

([Measures].[Person ID Distinct Count], [SET]) and

([Measures].[Person ID Distinct Count], {[SET]})

Just on a side note, I for this discount count in 2 steps:

I first did a count on the table using new measure... option from cube structure pane, then used it in a calculation to define the year of count i want. And now I am here. My mdx above didn't work but however in the browser i can pull the count (where its filtered by year) and use the set as a slice and it works fine. But I want this defined in a calculation.

Solution

  • This signifies a tuple (....)

    A tuple is made up of members - so a set cannot be used as an argument.

    You need to aggregate your set separately to create a member:

    Aggregate([SET])

    The memeber can then be used in the tuple you mention:

    ([Measures].[Person ID Distinct Count], [SomeDim].[SomeHierarchy].[All].[AggregatedSET])