"Could not add field" error.

Question

I am getting the error below with both a calculated column and SOME non-calculated columns that are the same data type as columns that work. The example below is the calculated column.
It calculates without problem in the PowerPivot Window and its formula is =Table_slcbrasstest_RDA_ANALYSIS_TAX_DISTRICT_HIST[PAID_BASED_VAL]*RELATED(Table4[CW_All])
The calculated column is pulling a rate from a related table that joins based on year.

Any help you can give will be appreciated. I am giving a lot of info in hopes of avoiding too much back-and-forth.

THE ERROR:
Could not add the field "CountywideDiversion" to the PivotTable. Please ensure the field exists and is calculated, and try refreshing the PivotTable.

============================
Call Stack:
============================

   at Microsoft.AnalysisServices.Modeler.FieldList.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
   at Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.SetFieldOrientation(FieldLocation location, IGeminiColumn column, Int32 positionIndex)
----------------------------
   at Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.SetFieldOrientation(FieldLocation location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.AddToFieldList(FieldLocation location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.MoveField(IGeminiColumn column, FieldLocation source, FieldLocation target, Int32 positionIndex)
   at Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.AddField(IGeminiColumn column, Int32 index)
   at Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.AddField(IGeminiColumn column)
   at Microsoft.AnalysisServices.Modeler.FieldList.FieldListControl.fieldsTreeView_AfterCheck(Object sender, TreeViewEventArgs e)

============================

ABOUT MY ENVIRONMENT:
Running in windows 7 64-bit, 32-bit Excel.

ABOUT THE DATA

Three of the tables in the PowerPivot come from SqlServer data sources that load into Excel tables. Those tables are then linked to PowerPivot.
The fourth table (Table4) was entered via cut and paste directly into Excel and linked to PP.
The relationships are as follows:

Table Related Lookup Table
Table_slcbrasstest_RDA_ANALYSIS_TAX_DISTRICT_HIST [PROJ_CODE] Table_slcbrasstest_RDA_ANALYSIS_PROJECT2 [PROJ_CODE]
Table_slcbrasstest_RDA_ANALYSIS_TAX_DISTRICT_HIST [YR] Table4 [Year]
Table_slcbrasstest_RDA_ANALYSIS_PROJECT2 [REDEV_AGENCY_CODE] Table_slcbrasstest_RDA_ANALYSIS_REDEV_AGENCIES [REDEV_AGENCY_CODE]

I have included the specs of Sql Server tables.

TABLE [dbo].[TAX_DISTRICT_HIST](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [YR] [nvarchar](4) NULL,
 [PROJ_CODE] [nvarchar](15) NULL,
 [TAX_DIST_CODE] [nvarchar](3) NULL,
 [RDA_DIST] [bit] NULL,
 [RULE_CODE] [nvarchar](2) NULL,
 [TOTAL_VALUE] [money] NULL,
 [VALUE_ADJUSTMENTS] [money] NULL,
 [TOTAL_ADJUSTED_VAL] [money] NULL,
 [TAX_DIST_RATE] [float] NULL,
 [TEMP_FULL_INCR_AVAIL] [money] NULL,
 [FULL_INCR_AVAIL] [money] NULL,
 [TEMP_TAX_INCR_RATE_LIMIT] [float] NULL,
 [TAX_INCR_RATE_LIMIT] [float] NULL,
 [TEMP_TAX_INCR_AVAIL] [money] NULL,
 [TAX_INCR_AVAIL] [money] NULL,
 [TEMP_ADDIT_AMT] [money] NULL,
 [ADDIT_AMT] [money] NULL,
 [TEMP_TAX_INCR_W_ADDIT_AMT] [money] NULL,
 [TAX_INCR_W_ADDIT_AMT] [money] NULL,
 [TEMP_ADJ_TAX_INCR_AVAIL] [money] NULL,
 [ADJ_TAX_INCR_AVAIL] [money] NULL,
 [TAX_INCR_REQ] [money] NULL,
 [TAX_INCR_PAID] [money] NULL,
 [NOTES] [nvarchar](255) NULL,
 [PAID_BASED_VAL] [money] NULL,
 [PMT_ADJ] [money] NULL,
 [PMT_NOTES] [ntext] NULL,
 [LOWER_REQORAVAIL] [money] NULL,
 [LOWER_BASEDVAL] [money] NULL,
 [DERVD_REALMVSA_BASEDVAL] [money] NULL,
 [DERVD_PERS_BASEDVAL] [money] NULL,
 [RATE_BASED_VAL] [money] NULL,
 [PERIOD] [nvarchar](1) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

TABLE [dbo].[REDEV_AGENCIES](
 [REDEV_AGENCY_CODE] [nvarchar](6) NULL,
 [REDEV_AGENCY_NAME] [nvarchar](50) NULL
) ON [PRIMARY]

TABLE [dbo].[PROJECT2](
 [PROJ_CODE] [nvarchar](15) NOT NULL,
 [PROJ_NAME] [nvarchar](45) NULL,
 [REDEV_AGENCY_CODE] [nvarchar](6) NULL,
 [BASE_YEAR] [nvarchar](4) NULL,
 [ACTIVE] [bit] NOT NULL,
 [TAX_INCR_RULE] [nvarchar](2) NULL,
 [EXCEPTION_DESCR] [ntext] NULL,
 [HOUSING] [nvarchar](15) NULL,
 [FIRST_PMT_YEAR] [nvarchar](4) NULL,
 [RECORDED_DATE] [datetime] NULL,
 [NOTES] [nvarchar](255) NULL,
 [REQUEST_BLOCK] [bit] NOT NULL,
 [PROJ_NOTES] [nvarchar](150) NULL,
 [INCR_MAXIMUM] [numeric](18, 0) NULL,
 [SPECIFIED_RATE] [float] NULL,
 CONSTRAINT [PK_PROJECT] PRIMARY KEY CLUSTERED
(
 [PROJ_CODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)

 

 

Answer 1

To this issue, it seems that other communities have the same behavior occasionally. This is the same issue at this thread http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/07abe299-6e48-4528-8725-6cba373904e4/

We have submit this feedback to the product connect group site

http://connect.microsoft.com/SQLServer/feedback/details/561074/relationships-doesnt-work-in-powerpivot

you can give it more weight by voting on it. Thanks for your understanding.

Thanks,

---

Challen Fu

TechNet Community Support