Power BI calculated columns: row-by-row values inside the model
Power BI calculated columns add a new field to a table by running a DAX formula once for every row. The model works out the values from data it has already loaded, so nothing new comes from the source. Authors use them for labels, bands and flags that readers slice by. This guide covers how they behave, when another tool fits better and what each column costs.
- One value per row
- Column or measure
- Refresh and query cost
Last updated
How do Power BI calculated columns work?
A calculated column starts with a formula, not with source data. You choose New column in Report, Table or Model view and write a DAX expression. Power BI then calculates a result for every row in that table.
Microsoft's own example joins a city field and a state field into one text value. After that, a map or a table can use the combined field like any other. The new field appears in the field list with its own icon, which shows that a formula produces it.
The values do not stay frozen forever. Power BI recalculates them when it needs to, for example after a refresh brings in changed data.
Power BI calculated columns compared with two close relatives
Three features can all add a field to a report, but they run at different moments and answer different needs.
| Question | Calculated column | Power Query custom column | Measure |
|---|---|---|---|
| Which language? | DAX | M, in Power Query Editor | DAX |
| When does it run? | Against data already in the model | During the query, before load | When a visual asks for a result |
| What does it return? | One value per row | One value per row | One result per filter selection |
| Can readers slice by it? | Yes | Yes | No, it fills the values area |
| Typical job | A band or label that needs model logic | Clean-up and shaping of source data | Totals, ratios and growth figures |
When is a DAX column the right choice?
Reach for a column when readers need to group, filter or sort by the result. A customer size band makes a good example. So does a flag that marks late orders, because a slicer can then split the page by it.
However, a column makes a poor home for totals. A sum or a ratio should react to the filters on the page, and only a measure does that. As a result, authors who put ratios in columns often see wrong totals.
Shaping work such as splitting text or fixing types usually belongs earlier, in Power Query. There the step runs as part of the query. Then the model receives data that already has the right shape.
Three costs every extra column adds to a model
Microsoft's documentation ties the cost of a column to the storage mode of its table.
Refresh time in Import
An Import table stores the results of a standard column. Therefore the model derives those values at refresh time, and a heavy formula slows each refresh.
Query time in DirectQuery
A DirectQuery table does not store the results. Instead the values come from the source at query time, so a complex column can slow visuals.
Upkeep for authors
Every added field also lengthens the field list. Because readers must scan that list, unused columns make a model harder to learn.
Power BI calculated columns: common mistakes to avoid
The first mistake is using a column where a measure belongs. If the number should change with a slicer, write a measure. If it describes the row itself, a column fits.
The second mistake is heavy logic in many columns on a large table. Each one adds work, either at refresh or at query time. So check whether one well-designed column can replace several.
The third mistake is forgetting the source. When a database view or a Power Query step can deliver the same field, that route often keeps the model simpler. Still, a DAX column earns its place when the logic depends on related tables in the model.
Add and check a new column in four steps
These steps follow the Desktop path that Microsoft documents for the new column feature.
Select the table
First right-click the table in the field list, then choose New column. The formula bar opens with a default name.
Write the expression
Then type a clear name, an equals sign and the DAX formula. Column references inside square brackets point at the current row.
Inspect the rows
Next open Table view and scan the new values. Blank or odd results show up faster there than in a chart.
Use it in a visual
Finally place the field on an axis or in a slicer, and confirm that it groups the data as intended.
Power BI calculated columns: frequent questions
Do calculated columns in Power BI make a file larger?
In an Import table, a standard column stores its results with the table, so it adds to the model. The effect depends on how many rows the table has and how varied the values are. Therefore remove columns that no report uses, and test heavy ones on a copy first.
Can calculated columns work with DirectQuery tables?
Yes, but they behave differently. Microsoft documents that such columns do not store values in DirectQuery mode. Instead the source derives them at query time. As a result, a complex formula there affects how fast visuals respond, so keep those expressions simple and test them against real data volumes.
What is the difference between a custom column and a calculated column?
A custom column comes from Add Custom Column in Power Query Editor and runs as part of the query. A calculated column uses DAX and works on data the model has already loaded. So the first shapes incoming data, while the second adds logic that depends on the finished model.
Can a column formula use values from another table?
Yes. Microsoft's documentation mentions combining values from two related tables as a typical use. The tables need a relationship for that to work. Then the formula can look up a value on the related side and place it on each row of the current table.
Will Power BI Agency tidy a model full of columns?
Yes. One of our consultants checks which fields reports actually use and which belong in Power Query or in a measure instead. Then we agree the scope in a written quote and do the work online in your own tenant. Power BI Agency is a division of Sataware Technologies and independent of Microsoft.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.