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.

That’s really nifty Bob! Have you considered the tradeoffs against using a text measure to fetch these, in combo with CUBEVALUE? FIRSTNONBLANK(InsertMemberPropColumnNameHere, 1)? I used that technique in this post: http://www.powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/#more-8965 but obviously not with cube formulas.

I’m asking at face value – as one who has NOT weighed the two techniques against each other. (I did not even know about the EXISTS statement). I have a built-in tendency toward text measures because they strike me as cool, but that’s about it 🙂

I have to admit that I hadn’t even considered it Rob. I am aware of the text measure technique, and I agree that it is a nifty technique, but I have never used it in anger (have you?), but this is something I have used a few times.

Maybe worth looking into further for a later post 🙂

If pulling a cubeset, can you use more than just two set expressions?

Use cube formulae like Lookups to rerieve data from model into spreadsheet = Gold dust

Many thanks!

I am having trouble using the except and descendants functions nested in a cubeset formula – I just can’t get it to work any help would be SO appreciated…..here is my formula

=CUBESET($A$1,CONCATENATE(“except(descendants([companyalt].[lu – distribution (base line)],2),([companyalt].[coporate – liberty utilities – usd]))”),”States – Level 2 from LU Co”)

Best regards,

Lily,

EXCEPT expects a set, so try using braces

except(descendants([companyalt].[lu – distribution (base line)],2),{[companyalt].[coporate – liberty utilities – usd]})

This is really useful – thanks a million!

WARNING if you’re using localized excel-versions, where you would normally replace the separating commas by semikolons:

Don’t replace them within the EXISTS-Statement, it will not work!

Hi Bob, thanks a lot for this nice piece of information.

I’d love to know if you could tell us how to use the formular if theres multiple tuples turning up. Fro example tell Excel to grab the first Email adress is there’s more then one.

Thanks a lot!!

Bob, great article. I have yet to really try the Exists part yet, but, do love to use the cube member formula (what I cannot get as a member quickly turns into a nice cubeset for filtering). Another I have found useful (since descendants is mentioned) is parent. I really find this useful when you have returned data and want to know where it is from (recently used it for fiscal periods where I needed to know exactly what fiscal year the date 1/1/2015 falls into (even tho it ended up being the parent of a parent of a parent of a parent, all wrapped up in one cubemember after another)) I sincerely hope we get to see more of these examples – for us who use them often (work GREAT with OLAP’s) we look forward to more insight!

Thanks a lot, why I have not read you blog like 2 years !!!!

And why haven’t I seen your blog before, it looks great. I need to set a few days aside and read through a number of your articles. Serendipity.

Genius – thank you so much, I struggled with this for way too long!