A CUBEMEMBERPROPERTY Equivalent With PowerPivot

I was recently asked how to use the CUBEMEMBERPROPERTY function with a Power Pivot model, to get the Customer Name with a formula by using a unique code such as the Customer Code. I can’t say that I use this function much, but I thought I could recall using it at some point. However, a little testing made me wonder if I had ever used it as I could not get it to work. A quick Google suggested that other people had the same problem,  member properties cannot be defined in the model so maybe it can’t work.

I sat down and tried to figure out another way to emulate this, and this is what this post describes.

I am using the AdventureWorksDWR2008 database to demonstrate this technique, and in concert with the question originally asked I will get a value the Customer table such as email address or gender, given a known customer key. I have also added a customer column in that table with a simple formula of
=IF([MiddleName]=””,[FirstName]&” “&[LastName],[FirstName]&” “&[MiddleName]&” “&[LastName])

You are probably aware that you can easily get a CUBEMEMBER with a formula such as
=CUBEMEMBER(“PowerPivot Data”,”[ProductCategory].[Product Category Name].[Bikes]”)
This is a bit restrictive as you have to know the item to retrieve, it can be done more dynamically using CUBESET and CUBERANKEDMEMBER, but the point of showing this is to show that CUBEMEMBER will do what we want, if only we could put some logic in the expression that determines the member using another value held in a ell on the worksheet.

And we can. All (sic!) we have to do is included a bit more MDX, using the EXISTS function. The syntax of EXISTS on Technet is
Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] )
and is described as …

Returning the set of tuples of the first set specified that exist with one or more tuples of the second set specified. If the optional <Measure Group Name> is provided, the function returns tuples that exist with one or more tuples from the second set and those tuples that have associated rows in the fact table of the specified measure group.

The MeasureGroupName would be useful if we were looking only for customers with sales, but we don’t care whether the customer has sales or not, so we can omit that optional parameter.

The first set expression will define the set we want returned, the second will define the variable condition that we are applying.

Assuming that we have our customer key in D17, we can use EXISTS within a CUBEMEMBER function to get the customer name,
=CUBEMEMBER(“PowerPivot Data”,”EXISTS([Customer].[Customer].Children,[Customer].[CustomerKey].[“&D17&”])”)
or the customer’s email address
=CUBEMEMBER(“PowerPivot Data”,”EXISTS([Customer].[EmailAddress].Children,[Customer].[CustomerKey].[“&D17&”])”)

To make it a little tidier, in case we have a wrong customer key, we can wrap it in IFERROR
=IFERROR(CUBEMEMBER(“PowerPivot Data”,”EXISTS([Customer].[Customer].Children,[Customer].[CustomerKey].[“&D17&”])”),””)

That is about it. The only thing to be aware of is that using .Children could result in multiple tuples being returned. In our example, it was safe to assume that we would only get one result as we were using the key to retrieve from the customer table,  but if the function could return multiple tuples, we have to use CUBESET and CUBERANKEDMEMBER to pick a value out of the set.