Excel Cube Functions: Exclude a Single Member from a Set

I’ve talked previously about how one can use MDX to create sophisticated cube sets in Excel, but I was recently stumped by a questions from one of my clients. The question seemed simple enough (“How do I create a cube set that includes all of my customers except one that I identify?”) but the best I could come up with on the spot was to calculate the total value and then subtract the value for the customer to be excluded. That’s a good answer when there’s just one value to be calculated, but is quite cumbersome when there is a whole sheet of values to be calculated using the same restriction. When that’s the case, it makes good sense to create a set within the workbook that can be reused for all the values. Thankfully, after some troubleshooting, I was able to successfully use the MDX EXCEPT function in my Excel CUBESET function and here’s how.

The syntax for EXCEPT is fairly straightforward:

Except(Set_Expression1, Set_Expression2)

The functions evaluates the two sets, removes members of the first set that are found in the second set, and returns a set of those remaining members of the first set. For example, if your fist set is “Primary Colors” and your second set is “Red,” the function would return “Blue” and “Yellow.”

If I wanted a set of customers that IBIS works with but excluding IBIS itself (in the event that we bill ourselves), I would write a cube set against our project like this:

=cubeset(“Project Cube”,”except([Customers].[Customer Name].[All].children,{[Customers].[Customer Name].[IBIS]})”,”All Customers Except IBIS”)

The syntax of EXCEPT is a bit picky. I originally thought that I could reference cells that contain cube sets in place of the two set expressions, but that returns an error. Instead, you have to reference a cell that has a set expression typed out (or built through CONCATENATE). I was also unable to exclude multiple items; my set expressions were not evaluated properly. If you know how to do so, please make a comment below.


Leave a Reply

Your email address will not be published. Required fields are marked *

X