Best Practices for Designing a Power BI Data Model
Which TWO of the following are best practices for designing a star schema in Power BI?
⚠ Common exam trap
Candidates often confuse calculated columns with measures, thinking that placing business logic in fact tables is acceptable, but Power BI best practices dictate that measures (calculated at query time) should be used instead to avoid inflating the model size and degrading performance.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Dimension tables should have a primary key and descriptive columns.
Option A is correct because in a star schema, dimension tables serve as the lookup/reference tables, so they must have a primary key that uniquely identifies each row (e.g., ProductKey, DateKey) plus descriptive attributes such as product name, category, or color used for slicing and grouping in Power BI visuals. Option C is correct because fact tables store the measurable events (sales, quantities, amounts) and must contain foreign keys that relate back to the primary keys of the dimension tables, forming the one-to-many relationships that Power BI's VertiPaq engine and DAX rely on for efficient filtering and aggregation. Option B is not a best practice because calculated columns in fact tables consume memory and storage, and business logic is better handled via measures or in the source/ETL layer to keep the fact table lean. Option D is wrong because flattening all tables into a single wide table defeats the star schema's purpose, causing redundancy, larger model size, and degraded performance. Option E is incorrect because many-to-many relationships between fact and dimension tables are an anti-pattern in star schema design; relationships should be one-to-many from dimension to fact to ensure correct filter propagation and predictable results.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Dimension tables should have a primary key and descriptive columns.
Why this is correct
A well-formed star schema requires each dimension table to have a primary key (surrogate or natural) that uniquely identifies every row, paired with descriptive text columns such as product category or region name. These descriptive attributes provide the context for slicing and dicing in Power BI, while the primary key ensures referential integrity from the fact table and enables efficient row reduction during query execution. Without a unique key, filter propagation from the dimension to the fact becomes ambiguous and can produce duplicate or misleading results.
- ✗
Fact tables should contain calculated columns for business logic.
Why it's wrong here
Calculated columns added to fact tables are computed row-by-row at data refresh time and stored physically on disk, which increases model size and degrades columnar compression efficiency in the VertiPaq engine. Business logic such as 'profit = revenue - cost' should instead be written as DAX measures that are evaluated at query time under the current filter context. Measures avoid materializing derived data, keep the fact table lean, and guarantee that calculations respond dynamically to user selections, whereas calculated columns are static and ignore filter context.
- ✓
Fact tables should have foreign keys that relate to dimension tables.
Why this is correct
Fact tables must contain foreign keys that reference the primary keys of the dimension tables, establishing the one-to-many relationships that define the star schema topology. These keys are the only mechanism through which a fact row can be correctly linked to its descriptive dimensions; without them, Power BI cannot build relationships and cross-filtering fails. Maintaining foreign keys also enforces grain integrity, ensuring every measure row belongs to exactly one member of each related dimension, which is fundamental to accurate aggregate calculations.
- ✗
Merge all tables into a single flat table for simplicity.
Why it's wrong here
Merging all source data into a single flat table eliminates the distinction between process metrics and descriptive attributes, creating massive redundancy because dimension fields like customer name or product label are repeated on every fact row. This redundancy inflates storage, slows refresh, and breaks the optimized columnar storage that Power BI relies on, while also making granularity ambiguous when the same table contains rows at different levels. The star schema purposefully separates these concerns so that dimensions remain narrow, stable, and reusable across multiple fact tables, enabling faster compression and simpler DAX filter propagation.
- ✗
Use many-to-many relationships between fact and dimension tables.
Why it's wrong here
Many-to-many relationships between fact and dimension tables are an anti-pattern in star schema design because they introduce ambiguous row context and can cause double-counting or nonsensical aggregates when a fact row matches multiple dimension rows. Although Power BI supports many-to-many through bridge tables or CROSSFILTER, those scenarios require careful DAX modeling and often degrade performance; the canonical model uses one-to-many relationships where each dimension primary key maps to a fact foreign key. This ensures each fact row joins to exactly one dimension row, preserving measure accuracy and filtering predictability without extra overhead.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 524 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
4 more ways this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. Which THREE of the following are best practices when designing a Power BI data model for performance?
hard- ✓ A.Hide columns that are not needed in reports.
- ✓ B.Avoid bi-directional cross-filtering unless necessary.
- ✓ C.Use star schema design with dimension and fact tables.
- D.Use many-to-many relationships directly without bridge tables.
- E.Use calculated columns instead of measures for aggregations.
Why A: Option A is correct because hiding unused columns reduces the model's exposed surface and prevents report authors from accidentally dragging unnecessary fields into visuals, which keeps the VertiPaq engine from scanning and materializing columns that add no analytical value. Option B is correct because bi-directional cross-filtering forces the engine to propagate filter context in both directions, which can create ambiguous filter paths, increase query complexity, and degrade performance; single-direction relationships should be the default unless a specific requirement demands otherwise. Option C is correct because a star schema with dimension and fact tables minimizes relationship hops, keeps filter propagation simple, and lets the VertiPaq engine compress and scan narrow fact tables efficiently, which is the recommended modeling pattern for Power BI performance. Option D is not correct because many-to-many relationships without bridge tables introduce ambiguity and expensive filter propagation; the best practice is to resolve many-to-many with a bridge table and single-direction relationships. Option E is not correct because calculated columns are computed at refresh time and stored in the model, consuming memory and increasing refresh cost, whereas measures are evaluated at query time and are the preferred approach for aggregations in a performant model.
Variation 2. Which TWO of the following are best practices when designing star schemas in Power BI? (Select two.)
medium- ✓ A.Store numeric measures in fact tables.
- B.Use calculated columns in fact tables for row-level security.
- ✓ C.Place descriptive attributes in dimension tables.
- D.Include many columns in fact tables for filtering.
- E.Normalize dimension tables to reduce redundancy.
Why A: Option A is correct because fact tables should contain numeric, additive measures (such as Sales Amount or Quantity) that can be aggregated by the Power BI engine, which is the core purpose of a star schema fact table. Option C is correct because descriptive attributes (such as Product Name, Category, or Customer City) belong in dimension tables, where they serve as the "by" fields for slicing and filtering the numeric measures in the fact table. Option B is not a best practice because row-level security should be implemented with DAX filter expressions on dimension tables (or via roles), not by adding calculated columns to fact tables, which bloats the model and hurts performance. Option D is wrong because fact tables should stay narrow and contain only keys and measures; adding many columns for filtering increases model size and degrades compression and query performance. Option E is incorrect because star schemas deliberately use denormalized, flattened dimension tables to reduce the number of joins and improve query performance in Power BI.
Variation 3. Which THREE of the following are best practices for designing a Power BI data model?
hard- A.Use composite keys in relationships for better performance
- B.Enable bidirectional cross-filtering by default
- ✓ C.Implement business logic in measures rather than calculated columns
- ✓ D.Use a star schema design with dimension and fact tables
- ✓ E.Use surrogate keys for dimension tables
Why C: Option C is correct because measures are evaluated at query time and do not consume memory or storage in the model, whereas calculated columns are materialized during refresh, increasing model size and refresh time; pushing business logic into measures therefore improves performance and flexibility. Option D is correct because a star schema with dimension and fact tables is the recommended Power BI modeling pattern: it produces simpler relationships, more efficient DAX and VertiPaq compression, and better query performance than snowflaked or flat designs. Option E is correct because surrogate keys (meaningless integer keys) in dimension tables keep relationships narrow and integer-based, which compresses better and performs faster than natural or string keys, and they insulate the model from changes in source business keys. Option A is not a best practice because composite keys in relationships are not supported for all cardinalities and generally add complexity and overhead; a single surrogate key is preferred. Option B is not a best practice because bidirectional cross-filtering by default can introduce ambiguous filter paths, degrade performance, and cause unexpected results; it should be enabled only when a specific requirement demands it.
Variation 4. Which TWO of the following are best practices when designing a Power BI data model?
medium- A.Use calculated columns instead of measures
- ✓ B.Use a star schema design
- C.Denormalize all tables into a single flat table
- D.Use bidirectional relationships as default
- ✓ E.Hide foreign key columns from report view
Why B: Option B is correct because a star schema — a central fact table surrounded by dimension tables — is the recommended Power BI model design: it produces simpler DAX, better compression, and faster query performance than snowflaked or flat models. Option E is correct because foreign key columns in dimension tables are implementation details used only for relationship joins; hiding them from report view keeps the field list clean and prevents report authors from accidentally grouping or filtering by meaningless surrogate keys. Option A is wrong because measures (evaluated at query time, not stored) are generally preferred over calculated columns, which consume memory and are computed during refresh. Option C is wrong because collapsing everything into one flat table causes massive redundancy, poor compression, and slow aggregations. Option D is wrong because bidirectional relationships can introduce ambiguity and unexpected filter propagation; they should be used sparingly and only when a specific cross-filtering requirement demands it, not as the default.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.