Search code examples
sqlexcelmdxpowerpivotvba

Cube function in Excel using Powerpivot to display specific row values in worksheet

This is a highly specialized question and I have searched the web to no avail for an answer with no luck. I am working with Powerpivot that is connected to a SQL database to create essentially something I would compare to a web application. I use cubevalue functions a lot to get sums and other output in the workbook with regards to specific query parameters. I am looking for a way query a row with some key value and output some different column's value that is on the same row in the database. Essentially this is as easy as a vlookup if I just went from sheet to sheet and the data wasn't so large that it had to be stored in powerpivot.

Here is kinda what I am looking at.

Example Image

Given a query on the applicantid I want the output of the learner_id.

Here is what I hoped would work but it gives #N/A

=CUBEMEMBERPROPERTY("ThisWorkbookDataModel","[MasterStartQuery].[applicantid].[1119195]","[learner_id]")

Any insight would be super appreciated or if you know someone that may know. Thank you.

Solution

  • The bad news is that CUBEMEMBERPROPERTY() doesn't work with PowerPivot - http://www.mrexcel.com/forum/power-bi/730287-function-cubememberproperty-always-return-n.html

    That's not the only way to approach this, if we are talking about a simple, unique one to one relationship between your two fields then you could simply create a measure to return the learner_id e.g. something like:

    get_learner_id = MAX(MasterStartQuery[learner_id])

    Then call that with:

    =CUBEVALUE("ThisWorkbookDataModel",
           CUBEMEMBER("ThisWorkbookDataModel","[MasterStartQuery].[applicantid].[1119195]"),
           CUBEMEMBER("ThisWorkbookDataModel","[Measures].[get_learner_id]")
           )

    There are more elegant ways of structuring this.....

    This was easy because it is a numerical field, text based ones should work in a VALUES() instead of MAX() but you will get an error if you have multiple results (not a bad thing).