MDX with many-to-many relationship and OR/AND filtering

Question

I have a cube which contains many-to-many relationships between products (say T-Shirts) and their attributes (e.g. color, size, etc.).

The many-to-many dimension has been designed as it was described here: https://docs.microsoft.com/en-us/sql/analysis-services/lesson-5-3-defining-a-many-to-many-relationship

Now, I would like to write MDX query which contains some AND/OR filtering. Basically I need to get the quantity of all t-shirts that meet BOTH requirements:

Size is M or L,
Color is red.

In other words, I would like to exclue all green M-sized, red S-sized, etc.

We should use "AND" logic when checking the attributes and "OR" logic when checking the values of each attribute.

I created something as below, but it doesn't seem to work properly:

SELECT {
  [Geography].[City]
} ON ROWS,
{
  [Measures].[Quantity]
} ON COLUMNS
FROM [My cube]
WHERE (
  (
    [Attributes].[Attribute].&[Size] * 
    {
        [Attributes].[AttributeValues].&[M]
        , [Attributes].[AttributeValues].&[L]
    }
  ),
  (
    [Attributes].[Attribute].&[Color] * 
    {
        [Attributes].[AttributeValues].&[Red]
    }
  )
)

Is the goal to be able to write MDX queries that achieve what you want? Or is the goal for users to drag and drop in Excel PivotTables to achieve what you want? — GregGalloway

GregGalloway GregGalloway · Accepted Answer · 2017-04-28T14:38:27

Assuming your measure in your intermediate measure group for the many-to-many is called [Measures].[Product Attribute Count] see if the following works:

SELECT {
  [Geography].[City]
} ON ROWS,
{
  [Measures].[Quantity]
} ON COLUMNS
FROM (
 SELECT
 NonEmpty(
  NonEmpty(
   [Product].[Product].[Product].Members,
   {([Measures].[Product Attribute Count],[Attributes].[Attribute].&[Size])}
   * {
         [Attributes].[AttributeValues].&[M]
         , [Attributes].[AttributeValues].&[L]
     }
   ),
   ([Measures].[Product Attribute Count], [Attributes].[Attribute].&[Color][Attributes].[AttributeValues].&[Red])
  ) on 0
  FROM [My cube]
)

Basically it finds the products which are M or L. Then it further filters that list of products to just products which are also Red. Then it filters the results by that final list of products.

MDX with many-to-many relationship and OR/AND filtering

1 Answers