MDX query to calculate measure with AND condition by members from same dimension

2
votes

I have cube within OLAP database (SSAS 2012) where [Persons], [Dates] (date of order), [Categories] (category of item within order) are dimensions and [Orders], [OrdersCategories] are fact tables. [OrdersCategories] is many-to-many table to connect orders with categiries.

Using query:

SELECT
    {[Measures].[Orders Distinct Count]} ON COLUMNS,
    {[Persons].[Id].Children} ON ROWS
FROM [DB]
WHERE ( 
    {[Dates].[Id].&[2015-06-11T00:00:00] : [Dates].[Id].&[2015-06-17T00:00:00]},  
    {[Categories].[Id].&[10], [Categories].[Id].&[11]}
)

i can count number of orders (for specified time period and by specified categories) for each person. Is this case condition by category works like 'OR' condition and no matter order has relation with first category or second category or both.

Is it possible to write query with condition by categories which would work like 'AND' condition, namely order was counted only if it is related with both categories?

1
ssasmdx

1 Answers

2
votes

AND with members from the same hierarchy is generally implemented like the following:

SELECT 
  {[Measures].[Orders Distinct Count]} ON COLUMNS
 ,{[Persons].[Id].Children} ON ROWS
FROM [DB]
WHERE 
  Exists
  (
    Exists
    (
      {
        [Dates].[Id].&[2015-06-11T00:00:00] : [Dates].[Id].&[2015-06-17T00:00:00]
      }
     ,{[Categories].[Id].&[10]}
     ,"MeasuresGroupName"
    )
   ,{[Categories].[Id].&[11]}
   ,"MeasuresGroupName"
  );

You need to add a measure group name from your cube. Ameasure group name corresponds to the folder names in the Measures hierarchy - an example in Adventure Works is "Internet Sales"

enter image description here

Possible Alternative

I think you could try implementing the above using nested NonEmpty functions:

SELECT 
  {[Measures].[Orders Distinct Count]} ON COLUMNS
 ,{[Persons].[Id].Children} ON ROWS
FROM [DB]
WHERE 
  NonEmpty
  (
    NonEmpty
    (
      {
        [Dates].[Id].&[2015-06-11T00:00:00] : [Dates].[Id].&[2015-06-17T00:00:00]
      }
     ,{([Categories].[Id].&[10],[Measures].[Orders Distinct Count])}
    )
   ,{([Categories].[Id].&[11],[Measures].[Orders Distinct Count])}
  );