Forum Discussion

kheldar's avatar
kheldar
Iron Contributor
Dec 09, 2021
Solved

GetPivotData and Time Values as Cell References

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 tho...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 09, 2021

    kheldar 

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

kheldar's avatar
kheldar
Iron Contributor
Dec 09, 2021

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?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 09, 2021

kheldar 

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.

  • kheldar's avatar
    kheldar
    Iron Contributor
    Dec 09, 2021

    SergeiBaklan 

     

    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 ?

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 10, 2021

      kheldar 

      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.

      • kheldar's avatar
        kheldar
        Iron Contributor
        Dec 15, 2021

        Thank you! I managed to get it done by changing it to table name! You are super helpful. I can't thank you enough!

Resources