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.
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.