ssasmdxolapdimensionmeasures

Combining two calculated measures and using the combination result in MDX


I am creating two different calculated measure and I want to use the result of both calculated measure in one query from a same cube.

One of the calculated measure created is -

With Member [Measures].[VenueSalesCost]
 as
 (
 [Measures].[Amount - Reporting Currency]
)
Select [Measures].[VenueSalesCost]
 on columns
 from  [Project accounting cube]
where 
[Chart of accounts].[Main account name].&[Venue Hire Costs]

and the second is --

 With Member [Measures].[VenueSalesAmount]
as (
[Measures].[Amount - Reporting Currency]
)
 Select [Measures].[VenueSalesAmount]
 on columns
 from  [Project accounting cube]
where 
[Chart of accounts].[Main account name].&[Rental of Venue]

Now i want to use both the measures in my query.please let me know how to combine the two measure.


Solution

  • Just move the WHERE condition of the different queries into the defining tuple of the calculated measures:

    With Member [Measures].[VenueSalesCost]
         as
         (
         [Chart of accounts].[Main account name].&[Venue Hire Costs],
         [Measures].[Amount - Reporting Currency]
         )
         Member [Measures].[VenueSalesAmount]
         as 
         (
         [Chart of accounts].[Main account name].&[Rental of Venue],
         [Measures].[Amount - Reporting Currency]
         )
         Member [Measures].[new Measure]
         as 
         [Measures].[VenueSalesCost] - [Measures].[VenueSalesAmount]
    Select {
           [Measures].[VenueSalesCost],
           [Measures].[VenueSalesAmount],
           [Measures].[new Measure]
           }
           on columns
     from  [Project accounting cube]