UNION multiple MDX queries in SSAS (powerpivot)

Question

I have some difficulties trying to combine two MDX queries. When working separately, they work fine. script below

WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Main_Incidents].[Priority].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Main_Incidents].[Priority].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Main_Incidents].[Priority].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,[Main_Incidents].[Priority].ALLMEMBERS ON ROWS
FROM [Model];


WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,[Main_Incidents].[usr_directorate].ALLMEMBERS ON ROWS
FROM [Model];

The most important bit for me is that I need a label column to display. Therefore, I want the UNIONtwo queries together so that the ParameterCaption captures the values ​​from the Priority and Control dimensions.

Please help me achieve this?

Answer 1

This is a bit complicated, but definitely possible.

Union MDX , Tuples, . , ALLMEMBERS [All] . Caption, Value Level, .

:

WITH 
  MEMBER [Measures].[ParameterCaption] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.Member_Caption, [Main_Incidents].[Priority].CurrentMember.Member_Caption)
  MEMBER [Measures].[ParameterValue] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.UniqueName, [Main_Incidents].[Priority].CurrentMember.UniqueName)
  MEMBER [Measures].[ParameterLevel] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.Level.Ordinal , [Main_Incidents].[Priority].CurrentMember.Level.Ordinal)
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,{
    [Main_Incidents].[Priority].ALLMEMBERS * [Main_Incidents].[usr_directorate].[All],
    [Main_Incidents].[Priority].[All] * [Main_Incidents].[usr_directorate].ALLMEMBERS
  } ON ROWS
FROM [Model];

Answer 2

, MDX. UNION MDX