Implementing the logical AND on members from the same hierarchy

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

In the Adventure Works DW 2012 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 July and August.

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

Our goal is to somehow combine these two members from the same hierarchy so that we perform logic AND along the [Month of Year] hierarchy on the Date dimension.

Getting ready

Start a new query in SSMS and make sure that you're working on the Adventure Works DW 2012 database.

Our first query...