Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 541
Mutai Avatar asked Jul 29 '26 02:07

Mutai


1 Answers

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]
like image 160
Danylo Korostil Avatar answered Jul 31 '26 16:07

Danylo Korostil



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!