Home Forums Power Pivot Is it possible to use CUBEMEMBER like you would use VLOOKUP?

This topic contains 12 replies, has 4 voices, and was last updated by  tomallan 4 years, 9 months ago.

Viewing 13 posts - 1 through 13 (of 13 total)
• Author
Posts
• #5212

porter444
Participant
• Started: 10
• Replies: 24
• Total: 34

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?

#5213

porter444
Participant
• Started: 10
• Replies: 24
• Total: 34

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.

#5214

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

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

#5217

porter444
Participant
• Started: 10
• Replies: 24
• Total: 34

Thanks Tom, I read those posts… no joy.

#5224

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

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

#5230

porter444
Participant
• Started: 10
• Replies: 24
• Total: 34

Thank you sir, attached.

###### Attachments:
You must be logged in to view attached files.
#5235

sherifffruitfly
Participant
• Started: 0
• Replies: 1
• Total: 1

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

#5242

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

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.

Tom

###### Attachments:
You must be logged in to view attached files.
#5244

porter444
Participant
• Started: 10
• Replies: 24
• Total: 34

AWESOME!  Thank you sir!  I see the possibilities with dropdowns, great idea as well.  I will use this a bunch.

#18156

anita.clerisse
Participant
• Started: 0
• Replies: 2
• Total: 2

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!

#18157

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

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…

#18158

anita.clerisse
Participant
• Started: 0
• Replies: 2
• Total: 2

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.

#18161

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

GetInfo looks like this:

``````Get Info :=
IF (
HASONEVALUE ( DirectoryInfo[Branch] ) && HASONEVALUE ( DirectoryInfo[Key] ),
VALUES ( DirectoryInfo[Value] ),
"Multiple Info Rows"
)``````
Viewing 13 posts - 1 through 13 (of 13 total)

The forum ‘Power Pivot’ is closed to new topics and replies.