ssasmdxcubeolap-cube

Check if an item exists in MDX named set

I want to create a named set for two football teams. I'm not exactly sure what the syntax is, but what I have thus far is:

EXISTS(
    [Team].[Team], 
    {[Team].[Team].&[BAL], [Team].[Team].&[DEN]}
)

In other words, I want to create a Named set if the team is named "BAL" or "DEN". What would be the proper way to write this expresion?

The following query syntax works for me, but I'd like to translate this into "creating a named set" in BIDS:

WITH SET[FavoriteTeams] AS{
   [Team].[Team].&[DEN],
   [Team].[Team].&[BAL]
} 
SELECT
   [Measures].[Net Wins] on 0,
   [FavoriteTeams] on 1
FROM [NFL]

It seems perhaps it is as simple as just typing that in manually to the expression?

enter image description here

Solution

  • Sets are an important concept in MDX. A set is a collection of members from the same dimension and hierarchy. The hierarchy can be an attribute hierarchy or a user-defined hierarchy.

    set = {membre1,member 2 ..}

    the simpler the set expression the better it is.

    So you should use the second expression

    {
   [Team].[Team].&[DEN],
   [Team].[Team].&[BAL]
}

    In your case no need to use the exists function since the members are defined. we use exists in some setuations like we want to get all the cities of a specific region.

     EXISTS([City].[City], [region].[region].[Region].&[1])

    Visit : Microsoft.doc