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

[bigck2]

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]

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]

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]

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]

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]

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

Object	Specification / Limit
Number 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]

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