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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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 ?

Resources