Analytics & BI Tools · Chapter 2 of 18
Power BI, in practice
The volume leader, for reasons that are mostly commercial. What it is genuinely good at and where it hurts.
Power BI's dominance in enterprises is substantially a distribution story — it arrives bundled with Microsoft agreements many organisations already have, which makes the marginal cost look like zero. That is a real advantage and it is not a technical one, so evaluate the technical part separately.
What it genuinely does well
- Cost of entry. Per-user licensing that is very cheap relative to competitors, and often already paid for.
- Power Query for preparation — genuinely capable, and the same engine appears in Excel, which matters because your analysts already know it.
- DAX is powerful for time intelligence and complex measures. Year-on-year, rolling averages and running totals are far easier than in most competitors.
- Excel integration, which is not a joke — the ability for a finance user to pivot against a governed model in the tool they actually use removes an enormous amount of shadow reporting.
- Deep Microsoft integration — Entra ID, Teams, Microsoft Fabric, and increasingly Microsoft 365 Copilot.
Where it hurts
DAX has a steep and unusual learning curve. Filter context and row context are genuinely hard concepts and the error messages are unhelpful. Budget for this rather than assuming SQL skills transfer.
Import mode versus DirectQuery is a real architectural decision. Import is fast and means a copy of the data with a refresh schedule; DirectQuery is live and pushes load onto the warehouse, often with disappointing latency. Getting this wrong is the most common Power BI performance problem.
Governance sprawl. Cheap licensing plus easy publishing produces thousands of workspaces and reports, most abandoned. Without deliberate certification and lifecycle policy this becomes unmanageable within two years.
Version control is awkward. Report files are binary artefacts; treating them like code needs deliberate effort.
The Power BI question you will be asked
“Import or DirectQuery?” The answer that lands: import when the data fits and the freshness requirement is measured in hours, because performance is far better and the warehouse is not hit by every filter click. DirectQuery when the data is too large to import, when freshness must be near real-time, or when the warehouse holds row-level security you must not duplicate.
Then the nuance that shows experience: DirectQuery performance is a warehouse problem, not a Power BI problem — every visual becomes a query, so an unclustered table on the other end makes the dashboard feel broken.
The two contexts, which is the whole language
Nearly every difficult question in this tool reduces to one thing: a measure is evaluated in a context, and there are two kinds of context that behave completely differently.
- Filter context is the set of filters applied when a measure is evaluated — from the visual, the slicers, the page, the report, and row-level security. It is what makes the same measure return a different number in each cell of a matrix, and it is why a measure is not a column.
- Row context exists when iterating rows — in a calculated column, or inside an iterator function. Row context does not automatically filter anything. That is the single most surprising rule in the language and the source of most confusion.
- Context transition is the bridge: wrapping an expression in the aggregation that converts the current row into an equivalent filter. It is why a measure referenced inside an iterator behaves differently from the same expression written inline — and why the advice ‘just wrap it’ sometimes fixes a formula for reasons nobody can explain.
- The filter-modifying function does not simply ‘remove filters’ — it replaces them for the columns you name and leaves the rest. Reading it as ‘ignore all filters’ produces wrong totals that look plausible, which is the worst kind of wrong.
Why the same measure gives different totals in different visuals
This is the question that separates people who use the tool from people who understand it, and it comes up constantly in interviews.
Because a total row is not the sum of the rows above it. It is the same measure evaluated again, in a different filter context — the one with the row grouping removed.
For an additive measure like a plain sum, both give the same answer and nobody notices the distinction. For anything non-additive — a ratio, a distinct count, a rank, an average-of-averages — they differ, and the total is correct while looking wrong.
The classic case: a margin percentage. Each row shows that product's margin. The total shows the overall margin, which is not the average of the rows — it is total profit over total revenue, which is what anyone actually wants.
The wrong fix is to force the total to equal the sum. That makes the number agree with intuition and disagree with the business. The right response is to explain the grain, and to write measures whose totals are meaningful by construction rather than by luck.
On the engine underneath: it is a columnar, in-memory store that compresses each column independently, and compression quality depends overwhelmingly on cardinality. A high-cardinality column — a transaction identifier, a timestamp with seconds — compresses badly and can dominate model size on its own. Splitting a datetime into a date column and a time column is often the single largest model size reduction available, and it takes minutes.
Check yourself