Reference: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
In my prior blog post, I discussed implementing logical OR in MDX on members of different hierarchies. As a follow-up to that post, I will now demonstrate how to implement logical AND on members of the same hierarchy. If you have not read my prior post, I recommend doing so before proceeding.
In MDX, when two or more members of the same set are placed on a slicer, OR logic is automatically applied. For example, if you have two members (e.g. sizes ‘Small’ and ‘Large’) on a slicer, the resulting dataset will contain data where the first, second or both members occurred in the associated fact table. Similarly, a tuple formed by members of multiple hierarchies (e.g. size ‘Small’ and color ‘Red’) will only return results that have data associated to both members; AND logic is applied in this instance.
To summarize, in MDX
- Sets imply logical OR
- Tuples imply logical AND
What if you need to apply AND logic to members of the same hierarchy?
For example, what if we want to only see data for months where we sold products to customers in both Mexico and Brazil?
Let’s run an MDX query for Brazil only.
SELECT {[Measures].[Unique Purchasers]} ON 0, NON EMPTY {[GL Date].[Fiscal Month Of Year].MEMBERS} ON 1
FROM [MyCube]
WHERE {[Customers].[Country].&[Brazil]}
As we can see from the output we started selling product to customers in Brazil starting in Aug 2013.
Now let’s run a similar MDX query for Mexico. The output is shown below:
In this case, we have data back to 2011.
If we restrict this output to only include Aug 2013 onward, we see that we have sold product to 54 unique customers in Mexico.
SELECT {[Measures].[Unique Purchasers]} ON 0
FROM [MyCube]
WHERE ({[Customers].[Country].&[Mexico]},{[GL Date].[Fiscal Month Of Year].&[Aug 2013]:NULL})
To only include data for months when we sold product to customers in both Mexico and Brazil, we can use the following MDX query:
SELECT {[Measures].[Unique Purchasers]} ON 0, NON EMPTY {[Customers].[Country].MEMBERS} ON 1
FROM (
SELECT
{[Customers].[Country].&[Brazil],[Customers].[Country].&[Mexico]} ON 0
FROM [MyCube]
)
WHERE
(
Exists(
Exists( {[GL Date].[Fiscal Month Of Year].[Fiscal Month Of Year].MEMBERS},
{[Customers].[Country].&[Brazil]}, “Sales”
),
{[Customers].[Country].&[Mexico]}, “Sales”
)
)
The output of this query is:
As you can see, the output for both Brazil and Mexico is the aggregate of the months starting in Aug 2013 onward since we are only including months where we had sales in both countries.
A few note-worthy details:
In this query, we have a sub-select that includes the two members [Customers].[Country].&[Brazil] and [Customers].[Country].&[Mexico].
In the WHERE clause we are using two Exists, the first argument is the ‘Fiscal Month of Year’ level (not hierarchy). It is important to note that you need to use the level and not the hierarchy for this query to work properly. We also need to specify the measure group of interest, in this case “Sales”. The second Exists function uses the output of the initial Exists as its first argument.