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
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]