# Is there a limit on the number of calculated fields in PowerPivot?



## bigck2 (Mar 10, 2015)

Hello,

I'm wondering if there is a limit to the number of calculated fields I can add in PowerPivot? I am using excel 2013, and have been pretty liberal in making calculated fields in my powerpivot data model. I believe I have close to 50 and need to add many more. Will I hit a wall? Or is there basically no limit?

I really don't want to get much farther and then hit a wall.

Also I have been adding these to the main Fact table. In my data model I have three tables. The main one with data, and then two smaller tables - one for dates/time intelligence - then one for asset details. 

I'm wondering if the best practice is to set up a specific table just for the calculated fields? I have to admit my PivotTables are becoming a little cumbersome with so many fields in the main data table.

Thanks in advance for sharing any wisdom in these areas.

-Chris


----------



## GDRIII (Mar 11, 2015)

Sounds like my first model.

After I went and did some learning, which was mostly retraining how I approached things, I eliminated the majority of Calculated columns in my models.

Here is a great read and a great place to dig up some new habits as well.

5 common mistakes made by self taught DAX students « PowerPivotPro


----------



## bigck2 (Mar 11, 2015)

Haha, I'm not going to lie this may be my first rodeo. I am sure there are a number of mistakes I am making this go around. Thanks for sharing that article. 

In my defense I have used next to zero calculated columns. I'm leaning heavy on calculated fields, which is why I am wondering if there is a limit??


----------



## GDRIII (Mar 11, 2015)

Haha.  I was not a generous listener and thought you meant columns...

When you say Calculated field I imagine you mean Measures.

I imagine there is some limit but, I also seem to remember that they pretty much sit idle until you drop them onto the Pivot.

Scottsen might have more to add but, I think sky's the limit.


----------



## scottsen (Mar 15, 2015)

Go nuts.  

I have never seen a limit there, kinda doubt there is one... but, now you got me curious... how many you got!?


----------



## PowerDAX (Mar 16, 2015)

There is virtually no limit....2 billion+


ObjectSpecification / LimitNumber of calculated measures in a table(2^31) - 1 = 2,147,483,647

<colgroup><col><col></colgroup><tbody>

</tbody>
https://msdn.microsoft.com/en-us/library/gg413465(v=sql.110).aspx?f=255&MSPPError=-2147217396

I have started placing calculated fields into their own special table(s).  This is just in case I have to rebuild a table or data connection.  In tabular models, you can utilize BIDS Helper in order to organize your measures into folders.  In PowerPivot, it may make more sense to create a special table per measure category (i.e Sales / Inventory / Purchasing / Manufacturing / Etc.).  This makes it easier for the end user to navigate to select measures.

I would suggest looking into dimension slicers to reduce the number of calculated fields.  As well, typically first-time BI solutions say they want every flavor of time intelligence measure for each and every measure and then do not utilize them...so be weary.


----------



## bigck2 (Mar 17, 2015)

Thanks to all for your feedback. I don't think I'll be hitting that limit any time soon!


----------

