PL-300 Visualize and analyze the data Practice Question
You are a Power BI analyst for a multinational retail company. The company's sales data is stored in an Azure SQL Database with tables: Sales (SalesID, Date, ProductID, Quantity, Amount), Products (ProductID, ProductName, Category), and Calendar (Date, Year, Month, Day). The Sales table contains 500 million rows. You are creating a Power BI report to analyze daily sales trends over the past 5 years. The report must allow users to drill from year to month to day using a hierarchy. The performance of the report is critical; users expect visuals to load within 5 seconds. The current model imports all tables without any optimization, and the date hierarchy visual takes over 30 seconds to render. You need to redesign the data model to meet the performance requirement. What should you do?
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
✓
Create an aggregated table in Power Query that groups sales by date, and create a date hierarchy in the Calendar table.
Pre-aggregating sales data by date in Power Query reduces the granularity from 500 million rows to at most 1,825 rows (one per day for 5 years), which drastically improves query performance for the date hierarchy. Creating a date hierarchy in the Calendar table allows drill-down. Option B is wrong because calculated columns do not reduce row count and still require processing all 500 million rows, so performance remains poor. Option C is wrong because DirectQuery without aggregation still pushes large queries to the database, which can be slow. Option D is wrong because incremental refresh only reduces the amount of data loaded during refresh, but does not improve query performance on the existing data; the full model still contains 500 million rows.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Create an aggregated table in Power Query that groups sales by date, and create a date hierarchy in the Calendar table.
Why this is correct
Creating an aggregated table in Power Query reduces the 500M-row fact table to daily totals, so slicers, visuals, and DAX queries scan far fewer rows and return faster. A date hierarchy in the Calendar table then lets users drill from year to month to day without adding storage-heavy calculated columns. This preserves the star schema and enables query folding during refresh.
- ✗
Add calculated columns for Year, Month, and Day in the Sales table to avoid using the Calendar table.
Why it's wrong here
Adding Year, Month, and Day calculated columns to the Sales table bloats the model because every row must store redundant text or numeric values, and Power BI cannot compress repeated values as effectively after materialization. These columns do not reduce row count and they do not accelerate aggregation; they force the engine to scan all rows for every expression, so they are slower than using a separate Calendar table with a relationship and proper date columns.
- ✗
Switch the import mode to DirectQuery to avoid loading all data into memory.
Why it's wrong here
Switching to DirectQuery does not automatically fix performance: each visual can generate multiple queries against the source, and a 500M-row table still requires the relational engine to aggregate on the fly, often without the columnstore compression and caching benefits of Import mode. Network latency, source indexing, and query folding all become bottlenecks, so DirectQuery is usually chosen for real-time needs, not for speed on huge historical tables.
- ✗
Implement incremental refresh policy on the Sales table to reduce the amount of data loaded.
Why it's wrong here
Incremental refresh limits how many new or changed rows are loaded on each refresh, which reduces refresh duration and memory pressure, but it does not reduce the row count of the already-loaded fact table or the time Power BI takes to evaluate measures against it. A DAX query will still scan the full loaded date partition set unless you also implement user-defined aggregations or pre-summarized tables, so this option addresses loading, not query performance.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
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.