Implementing the logical AND on members from the same hierarchy

This recipe shows how to implement the AND logic using members from the same hierarchy.

In the Adventure Works DW 2016 database, there are two members, [New Product] and [Excess Inventory], in the [Promotion Type] hierarchy:

[Promotion].[Promotion Type].&[New Product] 
[Promotion].[Promotion Type].&[Excess Inventory] 

These two promotion types have reseller orders, but the only two months in which they both have reseller orders are January and December.

The idea is to have a single query that displays the reseller orders, where both promotion types occur in the same month. In other words, we want to show the reseller orders for January and December.

Our goal is to somehow combine these ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.