Dec 08 2021 04:57 PM
Hello,
I seriously think that this is a bug. Actually maybe more than one bug.
First;
Using regular data model, if the source data includes a time value such as 00:00, you can't display those fields as times. 00:00 appears as 00 and changing number format doesn't do anything. In addition to this, you can't make a cell reference to use getpivotdata using a time value, let's says my cell a3 is 00:00 and I want to get that time's value but I can't reference it using a3. you have to hard code it as "0" and this kills the purpose because you can't flashfill this way.
Second;
Using Excel's Data Model,
You can get the pivot to display the time values correctly such as 00:00. However, as you know using getpivotdata with Excel's Data Model, things get complicated and again you can't reference a cell as a time value because excel reads the time as "1899-12-30T00:00:00" and referenced cell doesn't match this value.
Are these really bugs? If so how can I report them? Is there anyway around this?
Dec 09 2021 04:18 AM
SolutionI'd avoid using of implicit measures and GETPIVOTDATA(). For such sample
we may create explicit measure
Values sum:=SUM( [Value] )
and return related value by
=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[Table1].[Time].[" & TEXT( $K$4, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[Values sum]") )
In the sample above time is selected from drop down list, please see attached.
Dec 09 2021 12:50 PM - edited Dec 09 2021 01:11 PM
Hmm I just learnt cube functions from your post and doing research on them. Can you use them without power pivot?
=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[PivotTable4].[Hours].["& TEXT(A3, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[SLA]") )
This formula returns #N/A the pivot's name is PivotTable4 the field to look for is Hours and the value is SLA. The pivot is in another sheet. What am i doing wrong?
Dec 09 2021 01:14 PM
In general yes, Power Pivot is only interface to data model. In this case to use implicit measures or create explicit measures by right click on table name
In attached file same formula with implicit measure, it works.
Dec 09 2021 01:34 PM - edited Dec 09 2021 01:36 PM
This is the layout
=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[PivotTable1].[Hour Formula].["& TEXT(A3, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[SLA]") )
Why do you think I still get #N/A ?
Dec 10 2021 06:32 AM
It's hard to say without the file. What is PivotTable1, is it generated automatically when you start typing "[ , or you added it manually? In general it shall be name of the table which is source for PivotTable, not the name of the PivotTable.
Dec 15 2021 01:00 AM - edited Dec 15 2021 01:09 AM
Thank you! I managed to get it done by changing it to table name! You are super helpful. I can't thank you enough!
Dec 15 2021 11:41 AM
@kheldar , you are welcome
Dec 09 2021 04:18 AM
SolutionI'd avoid using of implicit measures and GETPIVOTDATA(). For such sample
we may create explicit measure
Values sum:=SUM( [Value] )
and return related value by
=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[Table1].[Time].[" & TEXT( $K$4, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[Values sum]") )
In the sample above time is selected from drop down list, please see attached.