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 — 217 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: Options A and D are correct. Option A: Using integer-based foreign keys reduces storage requirements and improves join performance between fact and dimension tables. Option D: Hiding the fact table from report view prevents users from inadvertently querying it directly, which can cause performance issues; instead, users should use measures. Option B is incorrect because adding calculated columns to a large fact table increases model size and degrades performance. Option C is incorrect because enabling Auto Date/Time can create hidden date tables that increase model size unnecessarily; it is a general best practice but not specifically about large fact tables. Option E is incorrect because setting default summarization to 'Sum' on numeric columns in a fact table can lead to incorrect aggregations; measures should be used instead.
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.