Every Power BI developer hits this question sooner or later: should I create this column in Power Query or with DAX? Both can add columns, both can calculate, and the result often looks the same in a table. But the choice affects refresh time, file size and whether your numbers respond to slicers.
This guide explains what each tool is for, gives clear rules, and walks through common examples so you can decide in seconds.
Quick answer: Use Power Query to get, clean and shape data before it loads (fix types, split columns, merge tables, fixed lookup columns). Use DAX measures for anything that must change with filters and slicers (totals, ratios, YTD, % of total, rankings). The common rule: do it in Power Query if you can, use DAX when you must.
What each tool does
| Power Query (M) | DAX | |
|---|---|---|
| When it runs | During data refresh | When a visual is shown (measures) or at refresh (calculated columns) |
| Main job | Extract, clean, reshape (ETL) | Calculate and analyse |
| Responds to slicers | No | Yes (measures) |
| Works row by row | Yes | Calculated columns: yes. Measures: no, they aggregate |
| Where you write it | Transform data → Power Query Editor | Modeling → New measure / New column |
Rule 1: Clean and shape data in Power Query
Anything that fixes the data itself belongs in Power Query:
- Changing data types, trimming spaces, fixing text case
- Removing rows, duplicates, errors and unused columns
- Splitting or merging columns (Full Name → First / Last)
- Unpivoting month columns into rows
- Appending files and merging tables
These steps run once per refresh and give the model clean, narrow tables. Removing unused columns here is one of the easiest ways to make a report smaller and faster.
Rule 2: Anything that reacts to filters must be a DAX measure
Power Query does not know what the user selected in a slicer. If a number must change when the user picks a year, region or product, it has to be a measure:
Total Sales = SUM ( Sales[Amount] )
Profit Margin % =
DIVIDE ( SUM ( Sales[Profit] ), SUM ( Sales[Amount] ) )
Sales YTD =
TOTALYTD ( [Total Sales], 'Date'[Date] )Notice the margin: it is sum of profit divided by sum of sales. If you calculated a margin per row in Power Query and then summed or averaged it, the total would be wrong. Ratios are always measures.
Rule 3: Fixed row-level columns: prefer Power Query
Some columns are calculated once per row and never change with filters, for example an age band, a fiscal year, or a full name. You can create these either way:
Power Query custom column (Add Column → Custom Column):
if [Age] < 25 then "18-24"
else if [Age] < 35 then "25-34"
else "35+"DAX calculated column:
Age Band =
SWITCH ( TRUE (),
Customer[Age] < 25, "18-24",
Customer[Age] < 35, "25-34",
"35+"
)Prefer Power Query here. The column is created with the rest of the table, it is available to every report that reuses the query or dataflow, and the model usually compresses it better. Use a DAX calculated column when the logic needs other tables in the model, such as RELATED, or a value that only exists after loading.
Real examples: where should this go?
| Task | Use | Why |
|---|---|---|
| Change "Date" text to a real date | Power Query | Data type fix |
| Remove blank rows and duplicates | Power Query | Cleaning |
| Combine 12 monthly files | Power Query | Append |
| Full Name from First + Last | Power Query | Fixed per row |
| Fiscal year column (April–March) | Power Query or date table | Fixed per row |
| Total sales, average order value | DAX measure | Aggregation |
| Profit margin % | DAX measure | Ratio must be calculated on totals |
| Year-to-date, last year, growth % | DAX measure | Time intelligence |
| % of total, rank, top N | DAX measure | Depends on the current filter |
| Product price from another table, per row | Power Query merge, or DAX RELATED | Either works; merge if the lookup table is not needed in the model |
Performance: why it matters
- Query folding: when the source is a database, Power Query can send many steps back to the database as SQL. Filtering and removing columns early lets the database do the work and speeds up refresh.
- Model size: every column you load uses memory. Removing columns and rows in Power Query keeps the model small.
- Measures cost nothing at refresh: they are calculated when a visual needs them, so add as many as you like. Heavy calculated columns, on the other hand, slow down refresh and increase size.
Common mistakes
1. Creating a calculated column for a total
Total = SUM ( Sales[Amount] ) as a calculated column puts the grand total on every row. It does not respond to slicers. Make it a measure.
2. Calculating percentages per row
A row-level margin column, summed or averaged in a visual, gives the wrong total. Calculate ratios as measures from totals.
3. Doing heavy text cleaning in DAX
Long chains of SUBSTITUTE, TRIM and LEFT in calculated columns are harder to maintain and run after load. Do them in Power Query.
4. Loading every column "just in case"
Unused columns make the file bigger and slower. Remove them in Power Query; you can always add them back later.
Frequently asked questions
Is Power Query faster than DAX?
They do different jobs. Power Query affects refresh time; DAX measures affect how fast visuals respond. Clean data in Power Query and keep measures simple for the best of both.
Can Power Query create measures?
No. Power Query only creates tables and columns. Measures exist only in DAX.
Should I use a calculated column or a measure?
Use a measure for anything you aggregate or that must react to filters. Use a column only for values you need per row, such as a slicer category. See calculated columns vs measures.
Is M harder to learn than DAX?
Most Power Query steps can be done with buttons, so you rarely need to write M by hand. DAX needs more writing, but a small set of functions (SUM, CALCULATE, DIVIDE, time intelligence) covers most reports.
Practice task
Take a sales file with a text date column, a Full Name column and Amount and Profit columns. In Power Query, fix the date type, split the name and remove unused columns. Then, in DAX, create Total Sales, Profit Margin % and Sales YTD measures. Put them in a matrix by month and check that the margin total is correct.
Next: build the date table every time-intelligence measure needs in How to Create a Date Table in Power BI, and learn how CALCULATE changes filters in CALCULATE in DAX: Complete Guide.
.png)
.png)
