This topic contains 12 replies, has 4 voices, and was last updated by 
-
Posts
-
I know what you’re thinking… I’m resisting change…
I’m building a dashboard and in the header area I have a set of fields I am using cube formulas to pull values from the workbook data model like current inventory amount, open accounts receivable amount, etc. I have slicers that are working with the CUBEVALUE formulas just fine.
The next challenge is to pull in the name of the branch manager, purchasing manager, district name, etc.
In the past I would have just pulled this information from a table with a VLOOKUP formula… no worries.
Since I am trying to let VLOOKUP go, and embrace PowerPivot & DAX I’m looking for an alternate approach.
If I have the column 1 value, can I pull the corresponding CUBEMEMBER from the column 2, 3, 4 etc?
I wanted to mention that I played around a bit with CUBEMEMBERPROPERTY and think that might be the answer here. I just can’t seem to get it to return a value… just #N/A so far.
Here is the formula I started with:
=CUBEMEMBERPROPERTY(“ThisWorkbookDataModel”,”[Directory1].[Branch].[All].[200500]”,”Name”)
My plan is to make the 200500 variable with a cell reference.
Having trouble finding resources on this topic… google didn’t return much even.
Hi,
Glad to see that you are “pushing the envelope” on cube functions. What you are trying to achieve is do-able; here are some links that will give you additional insights into cube formula reporting:
Tom
Thanks Tom, I read those posts… no joy.
OK.
Could you put together some sample anonymized data in a workbook and a copy of the report as you have developed it so far?
Convinced if we could be looking at similar workbooks we could resolve things right away.
Tom
Thank you sir, attached.
Hi,
I’ve done similar things in the past, using one-off cubemember calls to put eg Last Cube Process Date into a header (so the user knows how out of date they are).
I don’t know how to do the specific thing you’re after, but it sounds like it should be possible, FWIW.
-sff
Please see attached workbook.
I left the existing tables alone. Through Power Query (Get & Transform), added an un-pivoted version of the Directory table, which allowed my to create one cubevalue formula to extract all directory information (which I think was what you were aiming for with CUBEMEMBERPROPERY).
There are probably a couple of ways to replace the slicer with a “dropdown” style control, but I will save that for another day.
Please ask if any questions.
Tom
AWESOME! Thank you sir! I see the possibilities with dropdowns, great idea as well. I will use this a bunch.
Hello, I’m trying to do the same thing. It seems you created the measure [Measure].[Get Info] for this vlookup functionality to work in Power Query. Can you attach the source file so I can see how you created that measure? Or can you describe how you created that measure so I can replicate this using my data? Thank you!
Anita: The source file(s) should be available as attachments in their respective original posts.
Porter444: If you still subscribe to P3 forums, just wanted to give a shout-out to you and say it’s been a while…
Thank you….I get an error that says it’s not available….any chance you can post the code for the [Measure].[GetInfo]? We just need to be able to see what you’re doing.
GetInfo looks like this:
Get Info := IF ( HASONEVALUE ( DirectoryInfo[Branch] ) && HASONEVALUE ( DirectoryInfo[Key] ), VALUES ( DirectoryInfo[Value] ), "Multiple Info Rows" )
The forum ‘Power Pivot’ is closed to new topics and replies.