CUBEVALUE function will only return a value of 1

[petraidm]

I am relatively new to Power Query, Power Pivot so if this has been answered before, forgive me because I could not find it.

I created a power query to compile data from many spreadsheets. The Power Query is loaded as a connection as opposed to loaded into the workbook. The data is there and I can slice and dice it as needed with power pivot so I know the data got imported correctly. In another part of the workbook I have a worksheet that uses specific values from the power query to do calculations and I only need one specific data point for this calculation. I tried to use the CUBEVALUE Function to get this.

In my worksheet I have the following in various cells

=CUBEVALUE("ThisWorkbookDataModel","[Data Masters].[DesiredField]","[Data Masters].[Business].["&C$3&"]","[Data Masters].[Time_Preiod].["&F$12&"]")

The value I want to retrieve comes from "[Data Masters].[DesiredField]"
The DesiredField text can be any of a number of different field names
Cell C3 contains the name of the Business which is in the data model
F12 contains the year.

On this worksheet sheet there are 4 possible field names for DesiredField. As I said before in can slice and dice the data model data with no problem using Power Pivot. When using Power Pivot and filters or slicers for Business and Time_Period, the pivot table would return values of 72526, 95%, 45.0, and Under Construction. When I try to retrieve these values using the CUBEVALUE function, I get

=CUBEVALUE("ThisWorkbookDataModel","[Data Masters].[DesiredField1]","[Data Masters].[Business].["&C$3&"]","[Data Masters].[Time_Preiod].["&F$12&"]") <== Returns a value of 1.0 instead of 72526
=CUBEVALUE("ThisWorkbookDataModel","[Data Masters].[DesiredField2]","[Data Masters].[Business].["&C$3&"]","[Data Masters].[Time_Preiod].["&F$12&"]") <== Returns a value of 1.0 instead of 95%
=CUBEVALUE("ThisWorkbookDataModel","[Data Masters].[DesiredField3]","[Data Masters].[Business].["&C$3&"]","[Data Masters].[Time_Preiod].["&F$12&"]") <== Returns a value of 1.0 instead of 45.0
=CUBEVALUE("ThisWorkbookDataModel","[Data Masters].[DesiredField4]","[Data Masters].[Business].["&C$3&"]","[Data Masters].[Time_Preiod].["&F$12&"]") <== Returns a value of 1.0 instead of Under Construction

I have tried this using typed in values for "&C$3&" and "&F$12&" same result
I have tried using different fields other than [Data Masters].[Business] and / or [Data Masters].[Time_Preiod] Same result

Out of desperation I even tried something dumb like =CUBEVALUE("ThisWorkbookDataModel","[Data Masters].[DesiredField]") with no other expressions and get the same result

The function will only return a value of 1.0 no matter what I do!

I have searched the internet but have not found anything remotely like this, hopefully someone using this board can point me in the right direction.

 

[akice]

I admit i have not looked at your post too closely, but what I found helpful when learning CUBE functions is to convert an existing power pivot. Create a Pivot, then go to Analyze -> OLAP Tools -> Convert to Formulas. This will get you started and from there you can modify to insert cell references, etc.

 

[petraidm]

I have done this and it has been semi helpful. Here is what I found out:

1) Evidently when I created the power pivot, Excel created a dataset or something called measures. Therefore, I was able to retrieve numbers by changing the formula to the following:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of DesiredField1]","[Data Masters].[Business].["&C$3&"]","[Data Masters].[Time_Preiod].["&F$12&"]")

This took care of the numbers but I can not retrieve text values. When I converted the Power Pivot to formulas, the formula came back with text string in the cells. So for the example I showed above, the formula obtained:
=CUBEMEMBER("ThisWorkbookDataModel",{"[Data Masters].[Business].&[Detergents]","[Data Masters].[Time_Period].&[2013]","[Data Masters].[DESIREDFIELD].&[Under Construction]"})

In addition, I cannot replace Detergents or 2013 with relative references like the CUBEVALUE function. In other words, trying to use the CUBEMEMBER function I have to know what's in the data maodel before I can write a formula to retrieve it.

There has got to be a way to do this

 

[petraidm]

Let me ask the question another way:

Do you know if CUBEVALUE can even return a text string?
If not, what should I use
If so, how would you write the formula and would it actually retrieve the text string assuming the following

the connection is "ThisWorkbookDataModel"
The Query is called Data_Masters

The field containing the desired text sting to be retrieve is DesiredField (i.e. [Data_Masters].[DesiredField] )

The item can be found with just two parameters
[Data_Masters].[SearchField1].[SearchValue1] and
[Data_Masters].[SearchField2].[SearchValue2]

Thanks

 

[petraidm]

No one knows how to do this?

 

[deharris]

@petraidm - did you ever get a reply to this? I'm trying to find the same solution where I'm basically trying to use a CUBE formula to source a text value in a powerpivot table. It seems straightforward but no luck anywhere on the web or in forums. Basically use a CUBE to source data in "This Workbook" and if it finds 3-4 specific things, it returns me a variable form another, all of which are in the same powerpivot table.
thank you for any help!

 

[petraidm]

@petraidm - did you ever get a reply to this? I'm trying to find the same solution where I'm basically trying to use a CUBE formula to source a text value in a powerpivot table. It seems straightforward but no luck anywhere on the web or in forums. Basically use a CUBE to source data in "This Workbook" and if it finds 3-4 specific things, it returns me a variable form another, all of which are in the same powerpivot table.
thank you for any help!

Unfortunately, I received no response from anyone and have not yet found a solution.

 

[macfuller]

Even though it seems that CUBEVALUE should return a specific value from a table it seems to only work with measures. For a specific table entry you have to use a combination of CUBERANKEDMEMBER and CUBESET. I don't know why it's so convoluted but that's the way it works.
Here is an example where I download LIBOR rates (from the Wall Street Journal site if you're curious) into a model and want to know the Latest USD Overnight rate...

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","([LIBOR].[Currency].[USD], [LIBOR].[Term].[Overnight],[LIBOR].[Latest].children)"),1)

The table vector with the .children is the one with the value you want to return.

 

[Rickmaurinus]

Hi!

I would love to have a look at it.. yet your post is so long, and I find it difficult to grasp. If you could post a file, and clarify your question, I may be able to help... I've been able to get "Text" as an answer to a cube function before...

 

[MichChrdk]

I actually did something that might work, i create a measure in dax and call that with cubevalue and get text output.
like i wanted to see the "current" month in the data set, i have a calculated column that determines the month number from a date stamp "monthsum".
Then i do a lookup in Calendar with that month number and return the month value.
Month:=LOOKUPVALUE('Calendar'[Month];'Calendar'[Month Number];[Monthsum])

I havent looked through all the above coding, just wanted to share one of my own homemade solution to getting text into excel from datamodel. This will return the month name in text and i can use it in a pivot table in the value field.

Or this one where i append some static text as well
Title:="rapport for " & [Client] & " Department -" & [DeptNr]

i'm very much a nub myself and selftaught
Hope someone can use this.