Home › DAX or Power Query
Where does your logic belong: Power Query, DAX or the source?
As far left as you can. Whatever is always true belongs in the source or the load step, so it lives in one place and everyone sees the same thing. Whatever depends on what the user clicks belongs in a measure. Calculated columns sit in between and are usually a sign that something should have happened a step earlier.
The four layers, and what belongs where
| Layer | Belongs there | Why |
|---|---|---|
| Source system | Rules that apply to everyone | Every system reading the source sees the same |
| Data layer | Cleaning, joining, history | A place to recover from when something breaks |
| Power Query | Shaping, columns that are always true | Done once, at refresh time |
| DAX measure | Anything depending on the selection | Calculated at the moment of looking |
The rule of thumb
Ask one question: does this outcome change if someone picks a different period or department? If no, it belongs further left, towards the source. If yes, it belongs in a measure. That single question settles most of the doubtful cases.
Query folding, in one paragraph
Power Query tries to translate your steps into an instruction the source can execute itself. Filtering, grouping and dropping columns then happen in the database, and only the result travels. Use a step halfway that cannot be translated and that stops: Power BI pulls everything in anyway and the refresh becomes a multiple of what it was. So put non-foldable steps as late in the chain as possible.
He delivered a high-quality Power BI template and added value with strong visual storytelling and a custom function for column-level checks.
Half an hour is enough to know whether we fit
No slide deck and no quote at the end. We walk through your sources, your definitions and your biggest frustration.
Book half an hourFrequently asked questions
Should logic live in Power Query, in DAX or in the source?
As far left as you can. Whatever is always true belongs in the source or the load step, so it exists in one place. Whatever depends on what the user clicks belongs in a measure. Calculated columns sit in between and are usually a sign that something should have happened a step earlier.
When is a calculated column the right choice?
When you want to filter or group on it and the value does not depend on the selection, a category grouping for instance. If the outcome changes as soon as someone picks a period or a department, it belongs in a measure.
Why not simply put the logic in the report?
Because the report then stops being transferable. If the definition of margin lives in a measure that only exists in that one file, margin only exists there. A second report gets a slightly different definition and the argument starts again.
What is query folding and why does it matter?
Power Query can push operations down to the source, so the database does the work and less data travels. The moment you use a step that cannot be folded, that stops and Power BI pulls everything in to do it itself. That is often the real reason a refresh takes so long.
Can you move existing logic?
Yes. We first map where each calculation lives, then move it layer by layer. Outcomes are compared against the old situation along the way, so you can see nothing changes except the location.