How to GetPivotData from Data Model Pivot Table

[tb426]

Hello all,

need some help on this... Have been trying to adjust my usual formula but doesn't work...

I know I need to change my GETPIVOTDATA formula a bit when the Pivot Table is using Data Model, to [Measures].][]

I have a Pivot Table in a sheet named Not Ready Table2, it start at Cell B2. Rows has regular labels Reason Code (such as Lunch[9], Scheduled Breaks[1], Coaching[5], etc.), Columns has regular label Date (11/01/2019, 11/02/2019, etc.), and Values has a Measure named Duration (which is time HH:MM:SS).

I am trying to get data from within the pivot table but doesn't seems to work. My current formula is:

=IFERROR(GETPIVOTDATA("[Measures].[Duration ]",'Not Ready Table2'!B2,"Reason Code","Scheduled Break[1]","Date",DATE(2019,11,1)),"")

and when I press enter it returns blank.

I attached a small sceenshot of what it looks like

 

[RoryA]

Those field names look a bit short to me. If you let Excel create the GETPIVOTDATA formula for that cell, what does it look like?

 

[tb426]

Hi Rory...

you mean like this...

=GETPIVOTDATA('Not Ready Table2'!B2,'Not Ready Table2'!B2,'Not Ready Table2'!B3,'Not Ready Table2'!B18,'Not Ready Table2'!C2,'Not Ready Table2'!C3)

but it gives back #REF...


The Duration is a measure within my Data Model. I believe it needs to be something like [Measures].[Duration]

I guess my question would be how to I get the pivotdata for say the Schedule Break time 00:29:39 on 11/1/2019? The Pivot Table starts in Cell B2

 

[RoryA]

No, I mean that, assuming you have the option for Excel to generate GETPIVOTDATA formulas automatically turned on, just type = then select the value cell your formula should be returning. That will show you want the formula should look like, and we can then add in the cell references to replace hardcoded values.

 

[tb426]

AH GOT IT...

this is what it looks like...

=GETPIVOTDATA("[Measures].[Duration ]",'Not Ready Table2'!$B$2,"[NotReadyNov].[Reason Code]","[NotReadyNov].[Reason Code].&[Scheduled Break[1]]]","[NotReadyNov].[Date]","[NotReadyNov].[Date].&[2019-11-01T00:00:00]")

but it is kind of messy though... is it possible to clean it up to maybe make the formula looks nicer and cleaner, cause I will be have multiple field (Coaching, Lunch, etc.) for each and every day (2019,11,01, 2019, 11, 02, etc.)