Search code examples
reporting-servicesssasunionmdxssas-tabular

Mdx union between different dimensions


I have situation where I have two different dimensions in a model. Dimension A has ColorId, FirstColor and SecondColor. Dimension B has ColorId and Firstcolor. I am trying to get a complete list of colorIds from both dimensions that are distinct. Please keep in mind that this mdx needs to be used in ssrs.

Dimension A

ColorId     FirstColor    SecondColor
T0z17       Blue           Green
T0lqa       Red            Yellow

Dimension B

ColorId     FirstColor
T201k       Black
Tph1v       Orange

Intended Output (only 1 column)

ColorId
T0z17
T0lqa
T201k
Tph1v

Solution

  • As Greg's answer isn't real distinct, I'd fix his code in the following way:

    With 
    Member [Measures].[SameColor] as
    IIF(
        [Dimension A].[Color Id].CurrentMember.Name = [Dimension B].[Color Id].CurrentMember.Name,
        1,
        NULL
    )
    
    Member [Measures].[OnlyAColor] as
    IIF(
        IsEmpty(
            LinkMember(
                [Dimension A].[Color Id].CurrentMember,
                [Dimension B].[Color Id]
            )
        ),
        1,
        NULL
    )
    
    Member [Measures].[OnlyBColor] as
    IIF(
        IsEmpty(
            LinkMember(
                [Dimension B].[Color Id].CurrentMember,
                [Dimension A].[Color Id]
            )
        ),
        1,
        NULL
    )
    
    Member [Measures].[Color] as 
    IIF(
        [Dimension A].[Color Id].CurrentMember is [Dimension A].[Color Id].[All],
        [Dimension B].[Color Id].CurrentMember.Name
        [Dimension A].[Color Id].CurrentMember.Name
    )
    
    Select [Measures].[Color] on 0,  
    NonEmpty(
        [Dimension A].[Color Id].[Color Id].Members * [Dimension B].[Color Id].[Color Id].Members,
        [Measures].[SameColor]
    )
    +
    NonEmpty(
        [Dimension A].[Color Id].[Color Id].Members * [Dimension B].[Color Id].[All],
        [Measures].[OnlyAColor]
    )
    +
    NonEmpty(
        [Dimension A].[Color Id].[All] * [Dimension B].[Color Id].[Color Id].Members,
        [Measures].[OnlyBColor]
    ) on 1
    From [YourCube]