What is the point of creating a calculated field in PowerPivot data model

[INN]

Hi
I do not understand what is the point of adding a calculated field to a table in the data model because adding a table in the data model is basically adding the whole excel table, so why the calculated field not added to the excel table before adding the table in the data model. I do not see the point? I see why I need to add a calculated field to a pivot table, that is ok but do not the benfit in the data model. Could you please help me understand this:

"The formulas in calculated columns are much like the formulas you create in Excel. However, you cannot create different formulas for different rows in a table. Rather, the DAX formula is automatically applied to the entire column.
When a column contains a formula, the value is computed for each row. The results are calculated for the column as soon as you enter the formula. Column values are then recalculated as necessary, such as when the underlying data is refreshed."

Calculated Columns in Power Pivot - Microsoft Support

A calculated column gives you the ability to add new data to a table in your Power Pivot Data Model. Instead of pasting or importing values into the column, you create a Data Analysis Expressions (DAX)formula that defines the column values.

support.microsoft.com

 

[jdellasala]

You can't Filter a Pivot Table by a measure. (Good guess?)

 

[INN]

Sorry what do you mean?

 

[jdellasala]

Sorry what do you mean?

I kind of took a stab at the question not really knowing the answer, but now I checked. The following could be inaccurate, but I think is close.
If you have a field called Sales, and create a Measure for Total Sales that sums up the Sales field, you'll notice that if you create it in the Data Model, it gives you the total of ALL Sales with no filtering, but when you put the measure into a Pivot Table where the first column is Years and you add the Measure as a value, the answer you'll get is the Total Sales for the year row the measure is in. That's because the Measure uses "Context" to determine what Total Sales is - that is, it is FILTERED by any criteria that it sees.
If you had Column Headers of Departments, that same Total Sales measure would have the answer of the Total Sales for the Year of the row it's in, and the Column of the Department it's in. That measure is always of what's "Filtering" it as determined by it's row and column placement.
As such, you couldn't use that measure as a filter because its value is dependent on what's filtering IT. I just tried it on a small table using both SUM and SUMX as measures, and sure enough, neither can be used as a Filter.
The point of using them is they're "hyper" dynamic. Kind of a dumb term I just made up, but what I mean is that a Measure only does the math it needs to do whereas if you used a calculated column, that calculation has to happen all the time for every cell. Also, Implicit values are values that are calculated directly from the data table - like if you dragged a Sales COLUMN into Values, and a Measure is an Explicit value. If you have a static column with Sales and a measure that uses SUM/SUMX to determine the Total Sales, it's better to use the Measure in a Pivot Table because filtering is built into the calculation. If you use the Field Sales in a pivot table, it has to scan every row of the entire table to determine which Sales cells to SUM (doing the math along the way?), and then give the answer. A Measure first looks at its CONTEXT - what's filtering it or what row and column it's in, filters the table accordingly and then does one SUM operation. That makes the Measure faster. I've learned that if you create Measures of fields in a table - like Total Sales, it's best to simply Hide the Sales column and only use the Total Sales measure.

At least that's my understanding and welcome any corrections!

 

[GraH]

why the calculated field not added to the excel table before adding the table in the data model

DAX calculates faster, even on millions of rows, which can't be handled in Excel. Once DAX has calulated the column, the values are stored and never recalculated except when data changes.
So depending on the situation it might even be better to have a calculated column over a measure. As a principle measure over calculated column. But not always.

 

[jdellasala]

DAX calculates faster, even on millions of rows, which can't be handled in Excel. Once DAX has calulated the column, the values are stored and never recalculated except when data changes.
So depending on the situation it might even be better to have a calculated column over a measure. As a principle measure over calculated column. But not always.

Oxymoron - "never recalculated", "except when"... I'm not sure when it's recalculated, but I'd guess any operation on that column or any column in the calculation would cause a recalc, but I really don't know.
The Data Model is not needed for Excel to handle millions of rows. Millions of rows can be pulled in through Power Query, loaded as a connection only, and then create a pivot table from that. I wouldn't recommend it, but it can be done. It's far better to add it to the Data Model, even without Power Query (as in SQL, Access, Existing Connections), although PQ would be needed for common connections - CSV, Workbook, etc.

 

[GraH]

One important concept that you need to remember about calculated columns is that they are computed during the database processing and then stored in the model. This might seem strange if you are accustomed to SQL-computed columns – not persisted – which are computed at query time and do not use memory. In data models for DAX, however, all calculated columns occupy space in memory and are computed during table processing.

This behavior is helpful whenever you create very complex calculated columns. The time required to compute them is always process time and not query time, resulting in a better user experience. Nevertheless, you must always remember that a calculated column uses precious RAM. If, for example, you have a complex formula for a calculated column, you might be tempted to separate the steps of computation in different intermediate columns. Although this technique is useful during project development, it is a bad habit in production because each intermediate calculation is stored in RAM and wastes precious space.

From the Italians Ferrari/Russo.

 

[jdellasala]

From the Italians Ferrari/Russo.

"they are computed during the database processing", not clear on what that means, but off hand I'd say unless a calculated column is absolutely necessary (rarely), then use a measure.

 

[GraH]

It means during the load into the data model. So it's not calculated afterwards, i.e when you put it in your pivot table.
But we agree as I already stated

As a principle measure over calculated column. But not always.