Try this
=cubemember("Sales","[Region].[District].[Store].&[10].Parent")
The Parent function will (as it suggests) the parent of a specified member from the specified hierarchy, in your case Region.
Hope this helps.
Will
Is there a way to return the parent from a cubemember?
Ie I have store locations that are referenced via:
=cubemember("Sales","[Region].[District].[Store].&[10]")
Which returns the store name for location number 10. I want to return the District name for store number 10.
Jesse
Try this
=cubemember("Sales","[Region].[District].[Store].&[10].Parent")
The Parent function will (as it suggests) the parent of a specified member from the specified hierarchy, in your case Region.
Hope this helps.
Will
Hope this helps,
Will
Awesome, thanks for the help Will.
Jesse
When I do this it returns "STORE" as opposed to the district name which is what I'm looking for.
I'm curious... my cube knowledge is pretty close to nothing, but what about...
=cubemember("Sales","[Region].[District].[Store].&[10].Parent.Parent")
or is there a property that you can tag with the parent, like as
=cubemember("Sales","[Region].[District].[Store].&[10].Parent.Parent.&[Name]")
Totally guessing, but that's what I'd try... Course I'd probably fail too, but...
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Hmmm, This could have something to do with the limited support for hierarchies in Powerpivot. The function syntax I gave you was based on my "upbringing" with MDX and SSAS cubes and is the correct way to navigate a dimension hierarchy in MDX. If we have the AdventureWorks SSAS cube, we can have =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3]"This gives us a value of "Central" Using the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent"
We return "United States" which is the correct parent for "Central" (FYI Ken, the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent.Parent"
will return "North America", the parent of "United States" ) If I get the time today, I'll explore this with Powerpivot (which is I assume what you are using?) - I remember some colleagues in the PPVT world commenting on a lack of support for hierarchies so this might be the sort of thing they were referring to! Cheers, Will
Last edited by Will Riley; 2011-04-01 at 08:20 AM. Reason: weird rendering of my post!
Ken... not sure what went wrong with the above rendering... looked fine until I posted it but the formatting seems to get all screwed? Seemed to be working fine yesterday
(Ken... this is using the standard text editor as opposed to WYSIWYG - maybe a bug?)
Hmmm,
This could have something to do with the limited support for hierarchies in Powerpivot.
The function syntax I gave you was based on my "upbringing" with MDX and SSAS cubes and is the correct way to navigate a dimension hierarchy in MDX.
If we have the AdventureWorks SSAS cube, we can have
=CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3]")
This gives us a value of "Central"
Using the following formula
=CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent")
We return "United States" which is the correct parent for "Central"
(FYI Ken, the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent.Parent") will return "North America", the parent of "United States" )
If I get the time today, I'll explore this with Powerpivot (which is I assume what you are using?) - I remember some colleagues in the PPVT world commenting on a lack of support for hierarchies so this might be the sort of thing they were referring to!
Cheers,
Will
Well, I tried to do this in PowerPivot and it seems you can't (at least not using the Parent MDX function)
Reading around various posts on the interweb, it seems that there is no inherent hierarchy support in PPVT like there is in SSAS, so even though dragging related attributes from a PPVT table or related tables (Country, Region, City etc) does "appear" to give the same visual hierarchy effect, the fact is that under the covers, there is no hierarchy established between say Country & Region, it merely places them in the grid in a one-many left to right order so you get the same effect.
Therefore, the CUBEMEMBER function, has no idea that Country is a parent of City, as with Powerpivot, such a hierarchy is not supported.
Until Denali and the new BISM designer, where you will be able to author PPVT and SSAS from the same semantic model, I think you're stuffed. I'll keep playing, but i'm not sure you'll be able to do this easily with a single function, which is a real shame.
Bookmarks