I'm mdx rookie.. A box of gold for whoever can tell how to do UNION ALL of these two MDX queries:
SELECT
{
[Measures].[P Count]
}
ON COLUMNS,
{
(
[Dim P View].[Person Key].[Person Key].ALLMEMBERS *
[Dim P View].[Is Sensitive Data A].[Is Sensitive Data A].ALLMEMBERS *
[Dim P View].[Is Sensitive Data B].[Is Sensitive Data B].ALLMEMBERS *
[Dim P View].[Is Person Imp X].[Is Person Imp X].ALLMEMBERS
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM ( SELECT ( { [Dim P View].[Is Person Imp X].&[True] } )
ON COLUMNS FROM [BI_CUBE])
***UNION ALL***
SELECT
{
[Measures].[P Count]
}
ON COLUMNS,
{
(
[Dim P View].[Person Key].[Person Key].ALLMEMBERS *
[Dim P View].[Is Sensitive Data A].[Is Sensitive Data A].ALLMEMBERS *
[Dim P View].[Is Sensitive Data B].[Is Sensitive Data B].ALLMEMBERS *
[Dim P View].[Is Person Imp Y].[Is Person Imp Y].ALLMEMBERS
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM ( SELECT ( { [Dim P View].[Is Person Imp Y].&[True] } )
ON COLUMNS FROM [BI_CUBE])
I tried to append queries via Power Pivot / Power BI - which succeeded but this is main requirement to have it in one query.
Try this:
SELECT
{
[Measures].[P Count]
}
ON COLUMNS,
{
(
[Dim P View].[Person Key].[Person Key].ALLMEMBERS *
[Dim P View].[Is Sensitive Data A].[Is Sensitive Data A].ALLMEMBERS *
[Dim P View].[Is Sensitive Data B].[Is Sensitive Data B].ALLMEMBERS *
{
([Dim P View].[Is Person Imp X].&[True], [Dim P View].[Is Person Imp Y].[All]),
([Dim P View].[Is Person Imp X].[All], [Dim P View].[Is Person Imp Y].&[True])
}
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM [BI_CUBE]