Power BI gives you three places to calculate something: Power Query, a DAX calculated column, and a DAX measure. Many results can be produced in all three, so the question is not “can I?” but “where should I?”.
The rule experienced developers use
Data engineers often quote a rule known as Roche’s maxim: transform data as far upstream as possible, and as far downstream as necessary. In Power BI terms:
- If the source system or data warehouse can produce it, do it there.
- Otherwise, if it is preparation of the data and does not depend on report filters, do it in Power Query.
- If the result must react to what the user selects in the report, write a measure.
- Use a calculated column only when you need a column and Power Query is not an option, for example when the logic depends on other tables in the model.
Why upstream is better
Columns created in Power Query are loaded like any imported column. They are compressed efficiently, the logic lives in one place, and other reports that use the same dataset or dataflow benefit from it. Calculated columns are computed after loading and often compress less well. Measures are calculated at query time and store nothing, which is ideal for numbers that depend on filters and wasteful for values that never change.
Examples
| Requirement | Best place | Why |
|---|---|---|
| Clean country names (“canada”, “ Canada”) | Power Query | Data preparation, same for every report |
| Product code: first three letters of the name | Power Query | Fixed per row, no filters involved |
| Revenue per order line (quantity × price) | Power Query, or a measure with SUMX |
Store it if used everywhere; otherwise calculate on the fly |
| Total sales | Measure | Must follow slicers and visuals |
| Sales year-to-date | Measure | Depends on the dates in context |
| Profit margin | Measure | A ratio of two totals; summing row margins gives wrong results |
| Customer segment based on lifetime sales | Calculated column or upstream | Needs data from another table; fixed between refreshes |
The ratio trap
Ratios are a common reason to choose a measure. A margin calculated per row and then averaged is not the same as total profit divided by total sales. Write ratios as measures, dividing one aggregate by another:
Profit Margin = DIVIDE ( [Total Profit], [Total Sales] )Performance and refresh
Power Query steps run at refresh time. When the source is a database, many steps can be sent back to it as a single query, which is called query folding. Keep folding steps, such as filtering rows and removing columns, early in the query so the database does most of the work.
Sources
- What is Power Query?, Microsoft Learn
- Create calculated columns in Power BI Desktop, Microsoft Learn
- Create measures for data analysis in Power BI Desktop, Microsoft Learn
- Power Query query folding, Microsoft Learn