Best Practices for Optimizing Power BI Data Models
Which TWO actions are best practices for optimizing Power BI data models?
Quick Answer
The answer is to remove columns that are not used in reports and to replace text-based relationship columns with integer keys. The first action directly reduces the data model’s memory footprint by eliminating unnecessary data from the VertiPaq engine, while the second leverages integer compression—VertiPaq compresses integers far more efficiently than text, which speeds up join performance and shrinks storage. On the PL-300 exam, this topic appears in the “Optimize Model Performance” objective, often as a multiple-select question where one distractor suggests keeping all columns “just in case.” A common trap is assuming that hiding a column is equivalent to removing it; hidden columns still consume memory. For a quick memory tip, think “drop the dead weight and key the text”—meaning drop unused columns and convert text joins to integer surrogate keys for a leaner, faster model.
⚠ Common exam trap
Many candidates confuse 'hiding' columns (which only affects report visibility) with 'removing' columns (which actually reduces model size and improves performance), leading them to select Option C instead of D.
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
✓
Replace text-based relationship columns with integer keys.
Replacing text-based relationship columns with integer keys (surrogate keys) reduces storage size and improves join performance. Power BI's VertiPaq engine compresses integer columns far more efficiently than text columns, leading to faster query execution and smaller memory footprint.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Replace text-based relationship columns with integer keys.
Why this is correct
Integer keys improve join performance.
- ✗
Use calculated columns instead of measures where possible.
Why it's wrong here
Calculated columns increase model size and are evaluated at refresh.
- ✗
Hide columns that are not used in reports.
Why it's wrong here
Hiding does not remove data from the model.
- ✓
Remove columns that are not used in reports.
Why this is correct
Reduces model size and refresh time.
- ✗
Use many-to-many relationships instead of bridge tables.
Why it's wrong here
Many-to-many can introduce ambiguity and performance issues.
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
1 more way 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 TWO actions should you take when designing a data model that includes a fact table with a large number of rows and multiple dimension tables? (Select exactly 2.)
medium- ✓ A.Use integer-based foreign keys in the fact table to link to dimension tables
- B.Create calculated columns in the fact table for additional attributes
- C.Enable the Auto Date/Time feature for date columns
- ✓ D.Hide the fact table from the report view
- E.Set default summarization to 'Sum' for all numeric columns in the fact table
Why A: Option A is correct because using integer surrogate keys as foreign keys in a large fact table minimizes storage and improves join performance and compression compared to string keys, which is critical when the fact table has a very large number of rows. Option D is correct because hiding the fact table from the report view prevents report authors from accidentally using raw fact columns (such as keys and degenerate dimensions) directly, steering them to the dimension tables and defined measures instead. Option B is not appropriate because calculated columns consume memory and storage and are best avoided in large fact tables; such attributes should generally live in dimensions or be handled via measures. Option C is not appropriate because Auto Date/Time creates hidden date tables per date column, adding model bloat and overhead, which is undesirable in a model with a large fact table; a dedicated date dimension should be used instead. Option E is not appropriate because setting default summarization to 'Sum' on all numeric columns is indiscriminate and can produce misleading results for numeric columns that are not additive, such as keys or rates.
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.