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 1 year, 8 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:

    Pulling back the curtain: Intro to Cube Formulas

    Using Excel CUBE Functions with PowerPivot

    Introducing CUBESET() and CUBERANKEDMEMBER()

    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.

    Please ask if any questions.

    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.