Power BI is Microsoft's BI stack, and from a data-engineering seat it's less a charting tool than a query and modeling layer that sits on top of whatever warehouse you feed it. The parts an engineer actually owns are the semantic model (tables, relationships, measures), the transformation layer (Power Query/M), and the connection mode that decides whether data is cached in the engine or queried live. The dashboards are downstream; the modeling and refresh behavior are where pipelines meet the report.
The semantic model and DAX measures
A Power BI dataset is a tabular model running on the VertiPaq columnar engine - the same engine behind Analysis Services. You define relationships between tables and write DAX measures that compute at query time against the filter context the user's slicers produce. Measures are not stored columns; they're expressions evaluated per cell of a visual. The pattern that trips up newcomers is that aggregation respects whatever filters are active, so a measure has to be written to be context-aware.
Total Revenue = SUMX(Sales, Sales[Qty] * Sales[UnitPrice])
Revenue YoY % =
VAR Current = [Total Revenue]
VAR Prior =
CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(Current - Prior, Prior)CALCULATE modifies filter context, which is the core mechanic of DAX; SUMX iterates row by row before aggregating. Getting these right is most of the work of a clean model.
Power Query and M for the transform layer
Power Query is the ETL front end, and its formula language is M. It handles source connection, type casting, joins, unpivots, and column shaping before the data lands in the model. The detail that matters for performance is query folding: when a step can be translated into native SQL, Power Query pushes it down to the source instead of pulling rows and transforming locally. Folding breaks the moment you hit a non-translatable step (some custom M functions, certain merges), after which everything runs in the local mashup engine. Order your steps so folding survives as long as possible, and check the "View Native Query" option to confirm what actually reaches the warehouse.
Import vs. DirectQuery, and Fabric
The connection mode is an architecture decision. Import caches a compressed snapshot in VertiPaq - fast queries, but data is only as fresh as the last scheduled refresh. DirectQuery sends a SQL query to the source on every interaction - always current, but report speed is now bounded by your warehouse and concurrency. Composite models mix both per table.
| Mode | Data location | Freshness | Best for |
|---|---|---|---|
| Import | Cached in VertiPaq | At refresh time | Most reports; sub-second interactivity |
| DirectQuery | Source warehouse | Live | Large or real-time tables |
| Composite | Mixed per table | Mixed | Big fact + small dims cached |
Inside Microsoft Fabric, Power BI reads directly from OneLake via Direct Lake mode - querying Delta/Parquet files in place with Import-like speed and no separate refresh, which collapses the warehouse-to-report gap for teams already on the Fabric lakehouse.
Frequently asked questions
What's the difference between a measure and a calculated column? A calculated column is computed during refresh and stored per row; a measure is evaluated at query time against filter context and stored nowhere. Use columns for slicing/grouping, measures for aggregations.
Why is my report slow in DirectQuery? Every visual fires SQL at the source, so report speed equals warehouse speed under concurrency. Reduce visuals per page, aggregate large tables, or move hot tables to Import via a composite model.
What is query folding and why care? Folding pushes Power Query steps down as native SQL so the source does the work. When it breaks, transformation moves to the local engine and refreshes slow down. Keep foldable steps early in the query.
How does Direct Lake differ from DirectQuery? Direct Lake (Fabric only) reads Delta/Parquet from OneLake directly into the engine - no row-by-row SQL and no scheduled refresh, giving Import-like performance against live lakehouse files.