PL-300 Model the data Practice Question
You are a data analyst at a global retail company. You are building a Power BI semantic model to analyze sales performance across 50 countries. The data source is an Azure SQL Database with tables: Sales (SalesID, ProductID, StoreID, DateKey, Quantity, Amount), Products (ProductID, ProductName, CategoryID), Stores (StoreID, StoreName, CountryID), Countries (CountryID, CountryName), and Dates (DateKey, Date, Year, Month, Quarter). The model must support: 1) Hierarchical drill-down from Year to Quarter to Month. 2) Slicers for Country and Product Category. 3) Measures for Total Sales, Year-over-Year growth, and Moving Average (last 12 months). 4) The ability to filter by date range (e.g., last 3 months) while preserving the ability to show YoY growth for the selected period. The database contains 500 million rows in the Sales table. The company has strict performance requirements: report pages must load within 5 seconds. You need to design the model in Power BI Desktop. Which approach should you take?
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
✓
Use Import storage mode for all tables with incremental refresh policy on the Sales table to load only the last 5 years of data.
Option C is correct because Import mode with incremental refresh on the 500-million-row Sales table delivers the sub-5-second report performance required, while the incremental refresh policy limits data loaded to the last 5 years and only refreshes changed partitions. Import mode also fully supports the required Year→Quarter→Month hierarchy, Country and Category slicers, and DAX time-intelligence measures like YoY growth and a 12-month moving average, provided a proper Dates table is related to Sales. Option A is wrong because DirectQuery for all tables pushes every visual query to Azure SQL, which will not reliably meet the 5-second page load requirement at this data volume. Option B is wrong because DirectQuery on the large Sales table still incurs slow remote queries for aggregations and time intelligence. Option D is wrong because using DateKey from Sales without a dedicated date table prevents correct time-intelligence functions such as SAMEPERIODLASTYEAR and DATESINPERIOD.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use DirectQuery storage mode for all tables to ensure real-time data and aggregate queries at the source.
Why it's wrong here
Using DirectQuery for all tables over a 500-million-row Sales table is a performance anti-pattern because every visual interaction sends a query to the source database, and even well-indexed aggregate queries frequently exceed 5 seconds due to network latency and source engine load. Additionally, DirectQuery limits DAX time-intelligence functions and requires the underlying source to stay available for every user interaction, making it a poor fit for a model that also needs a calendar hierarchy and responsive reporting.
- ✗
Use a composite model: Import for dimension tables and DirectQuery for Sales table to balance freshness and performance.
Why it's wrong here
A composite model that imports dimension tables but keeps Sales in DirectQuery still forces every aggregation on the fact table to hit the source, so slicer selections and page-level calculations such as year-to-date totals will be slow with 500M rows. The composite approach also adds overhead by maintaining two storage engines and can introduce ambiguity in relationships, especially when the DirectQuery table participates in measures that reference imported columns.
- ✓
Use Import storage mode for all tables with incremental refresh policy on the Sales table to load only the last 5 years of data.
Why this is correct
Importing all tables into memory and applying an incremental refresh policy to the Sales table to keep only the last 5 years is the correct approach because it reduces the 500M-row table to a manageable subset that leverages the VertiPaq columnstore engine for in-memory aggregations. Incremental refresh uses RangeStart/RangeEnd parameters to filter historical data during each refresh, while keeping the model's date table and time-intelligence functions intact. This delivers fast, consistent performance and satisfies the requirement for a date hierarchy without sacrificing freshness of the most recent data.
- ✗
Use Import mode but do not create a date table; instead use the DateKey column from Sales for time intelligence.
Why it's wrong here
Using the DateKey column from the Sales table as a pseudo-date table will break DAX time-intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR, which require a separate contiguous date table marked with a 'Date table' setting. It also prevents automatic hierarchy creation (Year–Quarter–Month) and limits the ability to filter by calendar days that lack transaction records, so the report would fail the explicit date hierarchy requirement even though import mode is used.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 524-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.