How to add one more measure in MDX where clause?

Question

Below query is perfectly working and bringing rolling 12 months data for selected measure. Now how do I add one more measure to same MDX query so that query fetch rolling 12 months for 2 measures? Thanks in advance for your help.

Working query with single measure

SELECT 
  NON EMPTY 
    {LastPeriods(12,[Time].[By Fiscal Year].[Period].&[Jul-21])} ON COLUMNS
 ,[Customer].[CustomerName].[CustomerName].MEMBERS ON ROWS
FROM 
(
  SELECT 
    [CustomerNamedSet] ON COLUMNS
  FROM [CSIS]
  WHERE 
    (
      {[Time].[By Fiscal Year].[Period].&[Jul-21]}
     ,{[Measures].[measure1]}
    )
);

Modified MDX query by adding one more measure in where clause (Not working)

SELECT 
  NON EMPTY 
    {LastPeriods(12,[Time].[By Fiscal Year].[Period].&[Jul-21])} ON COLUMNS
 ,[Customer].[CustomerName].[CustomerName].MEMBERS ON ROWS
FROM 
(
  SELECT 
    [customerNamedSet] ON COLUMNS
  FROM [CSIS]
  WHERE 
    (
      {[Time].[By Fiscal Year].[Period].&[Jul-21]}
     ,{
        [Measures].[measure1]
       ,[Measures].[measure2]
      }
    )
);

Expected results::

Kindly let me know if you need additional information. – kpsr Feb 08 '23 at 08:04 — kpsr, Feb 08 '23 at 08:04

score 0 · Answer 1 · answered Jun 06 '23 at 18:57

Good question! In the sub-select you can move the set of measures into the COLUMNS section like this:

SELECT 
  NON EMPTY 
    Measures.MEMBERS * 
    [Date].[Calendar].[Month].MEMBERS ON COLUMNS
 ,[Product].[Category].[Category].MEMBERS ON ROWS
FROM 
(
  SELECT 
      {
        [Product].[Category].[Accessories]
       ,[Product].[Category].[Bikes]
      }*
      {
        [Measures].[Sales Amount]
       ,[Measures].[Total Product Cost]
      }*
      LastPeriods
      (12
       ,[Date].[Calendar].[Month].&[2011]&[12]
      ) ON COLUMNS
  FROM [Adventure Works]
);

This produces output like you have specified:

Do you really need to use a sub-select? If not the query could be a lot simpler:

SELECT 
  NON EMPTY 
      {
        [Measures].[Sales Amount]
       ,[Measures].[Total Product Cost]
      }
    * 
      LastPeriods
      (12
       ,[Date].[Calendar].[Month].&[2011]&[12]
      ) ON COLUMNS
 ,{
    [Product].[Category].[Accessories]
   ,[Product].[Category].[Bikes]
  } ON ROWS
FROM [Adventure Works];

Gives the same result:

How to add one more measure in MDX where clause?

1 Answers1