Power Query M vs DAX: where each calculation belongs
Comparison · By Tahir Nas · Examples run in our simulator · Published · Updated
Two engines, two moments
Power Query runs when data is refreshed. Its output is fixed until the next refresh. DAX runs when a report is viewed: every time a user clicks a slicer, measures recalculate for the current selection. That timing difference decides most placement questions.
| Job | Power Query (M) | DAX |
|---|---|---|
| Fix types, cultures, text | Yes | Possible, but wrong place |
| Unpivot, append, combine files | Yes | No |
| Lookup columns from another table | Merge, or leave to a relationship | RELATED across a relationship |
| Row-level calculation (price × qty) | Yes, as a typed column | Calculated column (stored), or inside a measure |
| Total sales for the selected region | No (fixed at refresh) | Yes, a measure |
| % of total for the current filter | No | Yes |
| Year-to-date, same period last year | Only for fixed periods | Yes, time intelligence |
| Rank that changes with the selection | No | Yes |
The percentage trap
A share of total computed in Power Query is correct for the whole table and wrong as soon as someone filters the report. Here the share is computed at refresh over all regions:
let
Sales = #table({"Region", "Amount"}, {{"North", 600}, {"South", 300}, {"West", 100}}),
Total = List.Sum(Sales[Amount]),
Shares = Table.AddColumn(Sales, "Share of all regions", each [Amount] / Total, type number)
in
SharesResult
| Region | Amount | Share of all regions |
|---|---|---|
| North | 600 | 0.6 |
| South | 300 | 0.3 |
| West | 100 | 0.1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
If a user filters the report to North and South, these stored shares still say 0.6 and 0.3, which no longer add up to 100%. A DAX measure dividing by the total of the current selection would show 0.67 and 0.33. Name precomputed columns honestly ("Share of all regions") or move the calculation to DAX.
Good reasons to calculate in Power Query
- The value never depends on report filters (a cleaned name, a VAT-inclusive price, a fiscal period).
- It reduces model size or complexity (unpivoting, removing unused columns).
- Several reports or tools consume the same table (Excel and Power BI).
- The logic needs data from several files or sources combined first.
In Excel
The same split applies with Power Pivot: Power Query shapes and loads to the Data Model, DAX measures power PivotTables. Without the Data Model, loaded tables feed worksheet formulas and PivotTables, which play the role DAX plays in Power BI.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- What is Power Query? (Concept, Microsoft Learn)
- Power Query M language specification (Concept, Microsoft Learn)
- Query overview in Power BI Desktop (Concept, Microsoft Learn)
- List.Sum (Function reference, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.