Memory hook: Clean the grain; model the filter; tell the truth.
Reviewed 10 October 2026. Read this once, then answer the last-pass checks without looking.
Must remember by domain
| Domain | Rapid revision |
|---|---|
| Prepare/connect | Import stores model data and requires refresh; DirectQuery asks the source; Direct Lake uses supported Fabric Delta data; live connection reuses a model. Set credentials/privacy levels and parameters deliberately. Power Query uses M; query folding pushes supported operations to the source. |
| Clean/transform | Profile the full relevant dataset, not merely preview rows. NULL, blank text, error and zero differ. Correct types/locale, duplicate keys and missing values. Merge joins columns; append stacks rows; pivot makes columns; unpivot makes attribute/value rows. Reference follows upstream logic; duplicate starts independent logic. Disable load for staging-only queries. |
| Model | Define fact grain and unique dimension keys. Prefer clear one-to-many, single-direction relationships; bridges and many-to-many need explicit design. Role-playing dates use separate dimensions or carefully applied inactive relationships. A date table needs an appropriate complete unique date range. Calculated columns/tables materialize; measures evaluate in context. |
| DAX/performance | SUM aggregates a column; SUMX evaluates per row. CALCULATE changes filter context and performs context transition when appropriate. REMOVEFILTERS clears selected filters; KEEPFILTERS intersects. DIVIDE handles invalid denominator. Balances are often semi-additive over time. Measure totals re-evaluate total context; they need not sum visible cells. Use Performance Analyzer/DAX query view to locate cost. |
| Reports/usability | Bars compare categories; lines show time; scatter shows relationships; paginated reports suit precise multipage output. Filters have visual/page/report scopes. Drilldown follows hierarchy; drillthrough passes context to details. Bookmarks can capture data state and reset slicers unexpectedly. Test interactions, mobile layout, keyboard order, alt text and contrast. |
| Analysis | Bins group ranges; clusters group patterns; forecasts extrapolate assumptions; anomalies are unusual, not automatically erroneous. Reference/error lines need defined meaning. Validate AI visuals/Copilot narratives against model definitions and evidence; correlation does not establish causation. |
| Service/security | Workspaces support collaboration; apps distribute curated audiences. Permissions and licensing/capacity both affect consumption. Import refresh needs credentials and gateways where required. RLS filters eligible consumers; workspace editors/admins are not constrained in the same way. Build permission enables model reuse; labels classify/protect supported handling without replacing RLS. |
Diagnostic sequence and traps
Wrong total: source freshness → row grain → joins/relationships → filter direction/context → DAX. Stale visual: source data → refresh/connection mode → gateway/credentials → model refresh → report filters. Automatic page refresh is not Import model refresh. Multiple RLS role membership can broaden rows; always test as the real consumer.
Last-pass self-check
1. Need slicer-responsive aggregation: column or measure?
Measure; a calculated column does not recalculate per report filter context.
2. Order and shipping dates need different calculations: what matters?
Correct date relationship/role, including USERELATIONSHIP or separate dimensions.
3. A left join doubles revenue: likely issue?
Nonunique right-side keys or mismatched grain multiplied fact rows.
4. Can Contributor be used to test ordinary viewer RLS?
No. Validate using a consumer identity subject to the intended RLS rules.
5. Why can a grand total differ from summed rows?
The measure is evaluated anew in total filter context.
Sources
- Official exam scope and version
- power-query · power-query-what-is-power-query
- power-query · query-folding-basics
- power-bi · connect-data · desktop-directquery-about
- fabric · fundamentals · direct-lake-overview
- power-bi · guidance · star-schema
- power-bi · transform-model · desktop-relationships-understand
- power-bi · guidance · model-date-tables
- dax · userelationship-function-dax
Every topic at a glance
Open any topic to revisit its essential facts, decisions and exam traps. Use the full topic for active recall and supporting references.
01 · Power Query, Data Quality and Connection Modes
Memory hook: Clean before modeling; fold work toward the source.
Must remember
Power Query transforms data using M and an ordered query-step pipeline. Select connectors, credentials and privacy levels deliberately. Parameters make source paths or filters reusable without embedding environment differences everywhere. Privacy levels influence safe combination of sources; disabling them casually can expose private data through another source.
Import stores data in the semantic model and needs refresh. DirectQuery sends supported queries to the source, trading freshness/source governance for source load and latency constraints. Direct Lake reads supported Fabric lake data through its engine/model rather than ordinary Import refresh; availability, capacity and fallback behavior depend on the model/source configuration. A live connection to an existing shared semantic model reuses its governed definitions.
Profile column quality, distribution and statistics over a representative scope; preview profiling can cover only a subset unless changed. Null, empty text, errors and zero mean different things. Resolve types, locale-dependent date/number parsing, duplicate keys and inconsistent values before loading. Replacing every missing value with zero can corrupt averages and business meaning.
Merge joins tables horizontally by keys; append stacks compatible rows. Grouping aggregates; pivot turns values into columns; unpivot turns repeated measure columns into attribute/value rows. Expand nested records/lists to transform semi-structured input. Keep dimension keys unique and validate unmatched fact rows.
A referenced query depends on another query’s transformation logic; a duplicate starts an independent copy. Referencing does not guarantee a single cached source execution. Disable load for staging queries that should not become model tables. Query folding pushes supported transformations to the source; inspect where it stops, especially before filtering a large dataset.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Monthly columns need one date/value pair per row | Unpivot. |
| Combine this year and last year’s same-shaped records | Append. |
| Enrich transactions with customer attributes | Merge on validated keys. |
Traps
- Null is not automatically zero.
- A reference query is not a guarantee of source-result caching.
02 · Star Schemas, Relationships and Date Tables
Memory hook: Facts measure events; dimensions describe them.
Must remember
Define the grain of each fact table before choosing relationships. A sales-line table and a monthly budget table have different grain; blindly joining them can multiply values. Dimensions contain descriptive attributes with a unique key, while fact tables contain foreign keys and measures. A star schema makes filtering and aggregation easier to reason about.
Relationship cardinality describes uniqueness: one-to-many commonly links a dimension’s unique key to repeated fact keys. Cross-filter direction controls propagation. Prefer clear single-direction paths where they meet the need; bidirectional filtering can create ambiguity and surprising totals. Many-to-many designs require careful bridge/grain decisions, not just clicking a convenient setting.
Only one relationship between the same tables can be active for a given straightforward path; inactive relationships can be invoked by appropriate calculations such as USERELATIONSHIP. Role-playing dates such as order date and ship date can use separate dimension instances or carefully designed measures with inactive relationships.
Build a complete common date table with unique, contiguous dates over the needed range and configure it appropriately for time intelligence. Sort month names by month number, hide technical keys from report consumers, set data categories/formats and use hierarchies where they aid navigation. A date column with missing days or duplicate timestamps is not automatically a valid date dimension.
Calculated columns are evaluated for rows and stored in Import models; calculated tables materialize model expressions on refresh. Measures evaluate in query context. Choose a column for a relationship/grouping attribute, and a measure for an aggregation that should respond to slicers.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Slice sales by customer region | Customer dimension filters related fact rows. |
| Compare order and shipment dates | Role-playing dimensions or intentional inactive-relationship measures. |
| Need a reusable filter-responsive total | A measure. |
Traps
- Different fact grain can cause double counting.
- Bidirectional filtering is not a universal fix for a broken model.
03 · DAX Context, Time Intelligence and Performance
Memory hook: A measure answers the current filter context.
Must remember
SUM(Sales[Amount]) aggregates a column; SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) iterates rows and evaluates an expression. Row context identifies a current row; filter context defines the allowed set of rows. They are different, and relationships/filter propagation affect the result.
CALCULATE evaluates an expression with modified filter context and can perform context transition from row context. REMOVEFILTERS removes selected filters; KEEPFILTERS intersects rather than replacing the relevant existing filter. Avoid clearing more context than the denominator requires. DIVIDE handles a zero/blank denominator with a chosen alternate result. Variables improve readability and can avoid repeated expression evaluation within their scope.
Time-intelligence measures require a suitable date model and the correct relationship/date context. Year-to-date sums are useful for flows such as sales; balances are often semi-additive because summing month-end balances across time is meaningless. Decide whether the requirement is closing balance, average balance or a different business definition.
Quick measures generate DAX that still needs review. Calculation groups reuse calculation patterns across measures with precedence/format considerations. Visual calculations operate on the visual’s result structure and are not interchangeable with reusable model measures in every scenario.
Use Performance Analyzer to separate visual rendering and query time, then inspect DAX queries and model design. Remove unnecessary columns/rows, reduce high-cardinality text and use suitable types/grain. Excessive bidirectional relationships, expensive iterators over large tables and overly complex visuals can all contribute. A smaller model often helps, but aggregating away required detail changes analytical capability.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Revenue from quantity times price | SUMX over the appropriate grain. |
| Percent of category while retaining other filters | A carefully scoped CALCULATE denominator. |
| Month-end stock level | A semi-additive balance measure, not sum across all months. |
Traps
- A total is evaluated in its own context; it may not be the sum of visible row results.
- Calculated columns do not recalculate for every slicer interaction in an Import model.
04 · Reports, Navigation and Accessible Storytelling
Memory hook: Choose the comparison before choosing the chart.
Must remember
Use bars for category comparison, lines for time trends, scatter plots for relationships and tables when exact values matter. Choose appropriate scales and sorting; avoid misleading truncated axes or excessive categories. Themes establish consistency, conditional formatting highlights defined conditions, and slicers expose useful filtering without overwhelming the page.
Visual, page and report filters have different scope. Edit interactions to control cross-filtering/highlighting; synchronized slicers can carry selections across pages. Drill down moves within a hierarchy; drillthrough navigates to a detail page with passed filter context. Tooltips add contextual information without replacing essential visible labels.
Bookmarks capture selected report state, including data/display options according to configuration. Use the Selection pane for grouping/layering and deliberate navigation buttons. A bookmark unexpectedly resetting slicers often captured data state. Test back navigation and hidden visuals with a consumer’s workflow.
Design mobile layouts, keyboard/tab order, readable contrast, meaningful titles and alt text. Do not encode meaning only by color. Personalization lets viewers adjust supported visual choices; export settings and item permissions govern data movement. A paginated report suits printable pixel-precise multipage tables/documents, while an interactive report suits exploration.
Automatic page refresh is intended for supported live-query scenarios and depends on capacity/settings; it does not replace Import semantic-model refresh. Copilot can suggest pages and narratives, but validate measures, filters and claimed explanations. Generated commentary can confidently describe a misleading model.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Printable long operational statement | Paginated report. |
| Explore a customer’s detail from a summary | Drillthrough with appropriate filters. |
| Bookmark changes a slicer unexpectedly | Inspect its captured data state. |
Traps
- Drill down and drillthrough are different navigation patterns.
- Page refresh and imported-data refresh are not the same operation.
05 · Patterns, Anomalies and Analytical Claims
Memory hook: A pattern is evidence to investigate, not proof of cause.
Must remember
Grouping combines categories into meaningful sets; binning organizes numerical or time values into ranges; clustering seeks groups with similar characteristics. Choose bins thoughtfully because changing boundaries can alter the apparent distribution. Explain the population, time range and filters behind a chart.
Reference lines mark targets or summary values. Error bars communicate uncertainty or variability under the chosen definition. Forecasting extrapolates a modeled pattern and needs enough suitable history; structural changes can invalidate it. An anomaly is an unusual observation relative to a model, not automatically a data error or fraud.
AI visuals such as key influencers and decomposition trees help explore relationships and contributions. Use the Analyze feature to investigate changes, but validate suggested explanations against domain knowledge and data quality. Correlation does not establish a causal intervention would have the predicted effect.
Copilot summaries of a semantic model depend on meaningful names, definitions and trustworthy measures. Preserve privacy and permissions, and check eligibility/capacity requirements before promising a feature to stakeholders. Good analysis distinguishes observation, hypothesis and verified conclusion.
Recall drill: sales rose after a campaign, but prices and seasonal demand changed at the same time. State what the chart proves, what it cannot prove, and what comparison or experiment would improve the conclusion.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Show spread and uncertainty | Appropriate error bars with a clear definition. |
| Explore drivers of an outcome | Key influencers/decomposition with validation. |
| One extreme transaction | Investigate context and quality before deleting it. |
Traps
- A forecast is not a guaranteed future value.
- A correlation in a filtered dataset may not hold in the full population.
06 · Workspaces, Refresh, Sharing and Row Security
Memory hook: Share the item; secure the rows; verify as the viewer.
Must remember
Workspaces organize collaboration and roles. Admin, Member and Contributor permissions enable different management/editing capabilities; Viewer is a consumption role. Apps package content for audiences, while direct sharing and workspace access serve different distribution needs. Licensing/capacity affects who can consume which content, so access permission alone may not be sufficient.
Publish and update reports/semantic models deliberately; dependent reports may reuse a shared model. Dashboards combine pinned tiles, while reports provide interactive pages. Subscriptions send scheduled snapshots/notifications under supported settings; data alerts have supported visual/data requirements. Promotion indicates useful content; certification follows organizational governance rather than proving every calculation correct.
Scheduled refresh needs valid source credentials and, for appropriate private/on-premises sources, a configured gateway with reachable sources and mappings. Check refresh history, failures and source/schema changes. Gateway installation alone does not make every source available. DirectQuery and Direct Lake have different data-access/refresh behavior from Import.
Row-level security (RLS) filters model rows according to roles and identity, often using a user-to-entity mapping. Define and test roles, assign eligible users/groups, and verify effective behavior in the service. RLS is intended for consumers such as Viewers; workspace users with edit permissions are not constrained in the same way. Multiple role memberships can broaden allowed data.
Sensitivity labels classify/protect supported content and downstream handling; they do not replace all access permissions or RLS. Build permission, item sharing and model access are distinct capabilities. Test exports and Analyze in Excel-style consumption under the actual user identity before declaring data protected.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Publish curated content to many consumers | A suitable app/audience and licensing model. |
| Each salesperson sees only their region | RLS with tested identity mapping and consumer permissions. |
| Refresh cannot reach an on-premises database | Check gateway, source mapping, credentials and network path. |
Traps
- RLS is not a substitute for restricting workspace edit permissions.
- Certification/endorsement is a governance signal, not mathematical proof.