PL-300 Model the data Practice Test 12 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use Import storage mode for all tables with incremental refresh policy on the Sales table to load only the last 5 years of data.. Using Import mode with incremental refresh on the Sales table (e.g., last 5 years) reduces data volume significantly, leverages Power BI's in-memory compression and VertiPaq engine for fast queries, and allows creating a proper date table for hierarchical drill-down and time intelligence measures like YoY growth and moving average. Option A is wrong because DirectQuery on a 500-million-row Sales table would result in slow query performance, especially for complex measures and drill-downs, and does not leverage Power BI's caching. Option B is wrong because a composite model with DirectQuery on the large fact table still requires round trips to the source for aggregations, which can be slow and may not meet the 5-second load requirement. Option D is wrong because relying on the DateKey column alone prevents creating a proper date hierarchy (Year → Quarter → Month) and limits the use of built-in time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR), which require a continuous date table.

Answer

Use DirectQuery storage mode for all tables to ensure real-time data and aggregate queries at the source.

Answer

Use a composite model: Import for dimension tables and DirectQuery for Sales table to balance freshness and performance.

Answer

Use Import mode but do not create a date table; instead use the DateKey column from Sales for time intelligence.

Question 2

You are a Power BI developer for a healthcare organization. You are building a semantic model to analyze patient readmission rates. The data warehouse contains: Admissions (AdmissionID, PatientID, AdmitDate, DischargeDate, DiagnosisCode, HospitalID) and Readmissions (ReadmissionID, PatientID, ReadmitDate, Reason). You need to create a measure that calculates the readmission rate within 30 days of discharge. The measure must consider only the first readmission per patient per admission. You also need to create a visual that shows the trend of readmission rates over time, allowing users to filter by diagnosis. The model will be used by 200 analysts and must refresh daily. Which DAX measure should you create?

Accepted Answer

Readmission Rate = DIVIDE( COUNT(Readmissions[ReadmissionID]), COUNT(Admissions[AdmissionID]) ). None of the listed options satisfies the stated requirements. Option D only computes a simple readmission-to-admission ratio and does not enforce the 30-day window or the first-readmission-per-admission rule; it should not be marked correct. Option C attempts the window but contains an invalid DAX row-context reference. The question requires a corrected DAX measure or corrected choices.

Answer

Readmission Rate = DIVIDE( COUNTROWS(Readmissions), COUNTROWS(Admissions) )

Answer

Readmission Rate = DIVIDE( COUNTROWS(Admissions), DISTINCTCOUNT(Admissions[PatientID]) )

Answer

Readmission Rate = VAR ReadmitPatients = CALCULATETABLE( VALUES(Readmissions[PatientID]), Readmissions[ReadmitDate] <= Admissions[DischargeDate] + 30 ) RETURN DIVIDE( COUNTROWS(ReadmitPatients), DISTINCTCOUNT(Admissions[AdmissionID]) )

Question 3

You are a data analyst for a university. You need to build a Power BI report to analyze student enrollment by program and year. Data sources: a CSV file with student enrollment records (StudentID, Program, EnrollmentYear, Status) and an Excel file with program details (ProgramCode, ProgramName, Faculty). The CSV file contains 1 million rows. The report must allow users to filter by faculty and program, and see a bar chart of enrollment counts by year. You need to design the data model. What should you do?

Accepted Answer

Import both tables into Power BI, create a relationship between Enrollment[Program] and Programs[ProgramCode], use Star schema design.. Import both tables into Power BI, create a relationship between Enrollment[Program] and Programs[ProgramCode], and use a Star schema design. This approach allows efficient filtering by faculty and program through the dimensional table (Programs), while the fact table (Enrollment) supports the bar chart of enrollment counts by year. Option A is incorrect because DirectQuery is not supported for CSV files; importing both tables is the correct approach. Option B is incorrect because without a relationship, visual-level filters would not propagate correctly between tables, leading to incorrect aggregations. Option C is incorrect because merging the tables would lose the normalization benefits of a star schema and could cause data duplication and performance issues. Therefore, D is the best design.

Answer

Import the CSV file only and use DirectQuery for the Excel file to save memory.

Answer

Import both tables but do not create relationships; use visual-level filters to synchronize selections.

Answer

Merge the two tables in Power Query into a single table to simplify the model.

Question 4

You have a Power BI data model with a table named 'Sales' that has a many-to-many relationship with 'Product' through a bridge table. When creating a measure to calculate total sales by product, you notice the totals are double-counting. What is the most likely cause?

Accepted Answer

The cross-filter direction is set to single instead of both. In a many-to-many relationship via a bridge table, a single cross-filter direction can cause double-counting because filters from the Product table do not propagate correctly to the Sales table. Setting the cross-filter direction to both (bidirectional) allows filters to flow in both directions, resolving the issue. Option A is incorrect because a missing date dimension does not cause double-counting in this context. Option B is incorrect because incorrect data types would lead to errors or unexpected results, not specifically double-counting. Option C is incorrect because while duplicate rows in the bridge table could cause double-counting, the most common cause in Power BI with a properly structured bridge table is the cross-filter direction setting.

Answer

The model lacks a date dimension

Answer

The Product table has incorrect data types

Answer

The bridge table has duplicate rows

Question 5

You need to create a calculated column in Power BI that shows the full name by combining 'FirstName' and 'LastName' columns with a space. Which DAX expression should you use?

Accepted Answer

FullName = [FirstName] & " " & [LastName]. The DAX concatenation operator is the ampersand (&). Option B is wrong because CONCATENATE function only takes two arguments. Option C is wrong because CONCATENATEX is for tables. Option D is wrong because the plus sign is for addition.

Answer

FullName = CONCATENATE([FirstName], " ", [LastName])

Answer

FullName = CONCATENATEX(Table, [FirstName] & " " & [LastName])

Answer

FullName = [FirstName] + " " + [LastName]

Question 6

You are modeling data from a SQL database that has a table with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and Price. You want to create a star schema in Power BI. Which columns should you move to dimension tables?

Accepted Answer

CustomerID, ProductID, OrderDate. CustomerID, ProductID, and OrderDate are foreign keys that should be moved to dimension tables to create a star schema. Option A is wrong because OrderDate should be in a dimension table, and Quantity and Price are measures (numeric values) that should remain in the fact table. Option B is wrong because Quantity and Price are measures, and OrderID is the primary key of the fact table. Option D is wrong because OrderID is the primary key, and Quantity and Price are measures.

Answer

OrderDate, Quantity, Price

Answer

Quantity, Price, OrderID

Answer

OrderID, Quantity, Price

Question 7

You have a Power BI data model with a table 'Orders' related to 'Customers' (one-to-many). A measure calculates total sales per customer. Some customers have no orders, but they still appear in the report with blank sales. What should you do to hide these customers?

Accepted Answer

Remove customers with no orders from the Customers table using Power Query. Removing customers with no orders from the Customers table ensures they are not present in the model, thus they won't appear in reports. Option B is incorrect because changing cross-filter direction does not hide customers without orders; it affects filtering behavior but does not remove rows. Option C is incorrect because a visual-level filter to exclude blanks is a cosmetic workaround that does not address the model design; it can be overridden by user interactions. Option D is incorrect because marking a date table is irrelevant to hiding customers with no sales.

Answer

Change the relationship cross-filter direction to both

Answer

Add a visual-level filter to exclude blanks

Answer

Mark the date table as a date table

Question 8

You are creating a calculated table in Power BI that contains a list of unique products from the 'Sales' table. Which DAX function should you use?

Accepted Answer

DISTINCT(Sales[Product]). The DISTINCT function returns a single-column table of unique values from the specified column, removing duplicates. It is preferred over VALUES in this scenario because, unlike VALUES, it does not add a blank row when there are referential integrity gaps, ensuring a cleaner product list.

Answer

SUMMARIZE(Sales, Sales[Product])

Answer

VALUES(Sales[Product])

Answer

ALL(Sales[Product])

Question 9

You have a Power BI report that shows sales by region. The data model contains a 'Sales' fact table and a 'Region' dimension table. When you filter by a specific region, the total sales for that region is correct, but the grand total shows all sales. What is the likely cause?

Accepted Answer

The relationship between Sales and Region is inactive. An inactive relationship does not propagate filters by default. In this scenario, when you filter by a specific region, a measure might use USERELATIONSHIP to activate the relationship and correctly calculate the region's sales. However, the grand total is calculated without that context, so it shows all sales. Option A is incorrect because single cross-filter direction would propagate filters from Region to Sales, making both totals correct. Option B is irrelevant; SUM vs SUMX does not affect filter propagation. Option C is not a valid concept in Power BI.

Answer

The relationship is set to single cross-filter direction

Answer

The measure is using SUM instead of SUMX

Answer

The Region table is not marked as a dimension table

Question 10

You need to design a data model for a sales analysis that includes measures for total sales, sales by product, and sales by customer. The source data has a 'Transactions' table with columns: TransactionID, Date, CustomerID, ProductID, Quantity, Amount. What is the recommended star schema design?

Accepted Answer

Create a fact table and separate dimensions for Date, Customer, and Product. It follows the star schema design: a central fact table (Transactions) with separate dimension tables for Date, Customer, and Product. This allows for efficient querying and analysis of measures like total sales, sales by product, and sales by customer. Option A is wrong because creating two fact tables (one for sales and one for customers) is unnecessary and does not represent a star schema; it would complicate queries and lead to redundancy. Option C is wrong because a single flat table with all columns is not a star schema; it results in data redundancy and poor performance. Option D is wrong because placing Customer and Product in the same dimension violates the principle of having one dimension per business entity, leading to a junk dimension that is difficult to manage and query.

Answer

Create two fact tables: one for sales and one for customers

Answer

Create a single table with all columns

Answer

Create a fact table and one dimension containing Customer and Product

Question 11

You want to create a measure that calculates the total sales for the current year. Which DAX expression should you use?

Accepted Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]). TOTALYTD calculates the year-to-date total for a measure. Option A is incorrect because DATESYTD returns a table of dates, not a scalar value. Option C is incorrect because while CALCULATE can be used, the expression YEAR('Date'[Date]) = YEAR(TODAY()) would return a single year, not year-to-date. Option D is incorrect because SAMEPERIODLASTYEAR returns dates from the previous year, not the current year's total.

Answer

DATESYTD('Date'[Date])

Answer

CALCULATE(SUM(Sales[Amount]), YEAR('Date'[Date]) = YEAR(TODAY()))

Answer

SAMEPERIODLASTYEAR(SUM(Sales[Amount]), 'Date'[Date])

Question 12

Which TWO of the following are best practices when designing a Power BI data model?

Accepted Answer

Use a star schema design. Options B and E are correct because a star schema design improves query performance and simplifies the model, and hiding foreign key columns from report view ensures a user-friendly report interface. Option A is incorrect because measures are generally preferred over calculated columns for dynamic aggregations. Option C is incorrect because data should be normalized into dimension and fact tables rather than denormalized into a single flat table. Option D is incorrect because bidirectional relationships can introduce ambiguity and performance issues and should not be used as the default.

Answer

Use calculated columns instead of measures

Answer

Denormalize all tables into a single flat table

Answer

Use bidirectional relationships as default

Question 13

Which THREE of the following are valid reasons to create a calculated table in Power BI?

Accepted Answer

Create a summary table that aggregates data from another table. Options B, D, and E are correct. Calculated tables can be used to create summary tables via aggregation (B), to create crossjoins of dimension tables (D), and to generate a date table not present in the source data (E). Option A is incorrect because incremental refresh is a data loading feature, not something a calculated table can replace; calculated tables are static and refreshed along with the model. Option C is incorrect because calculated tables are derived from data already loaded; they cannot modify source data before loading.

Answer

Replace incremental refresh

Answer

Modify the source data before loading

Question 14

Which TWO of the following are valid DAX functions for filtering data?

Accepted Answer

FILTER. Options B and D are correct because FILTER and ALL are both DAX filter functions. Option A (CALCULATE) is a function that modifies filter context but is not a filter function itself. Option C (SUM) is an aggregation function. Option E (DISTINCT) is a table function that returns unique values but is not specifically a filter function.

Answer

CALCULATE

Answer

SUM

Answer

DISTINCT

Question 15

You are building a Power BI report for a logistics company. The data source is a SQL Server database with tables: 'Shipments' (columns: ShipmentID, OrderDate, ShipDate, CustomerID, Origin, Destination, Weight, Cost, Revenue), 'Customers' (CustomerID, CustomerName, Segment, Country), and 'Calendar' (Date, Year, Month, Quarter). You need to create a star schema in Power BI. You have already created the relationships. When you try to create a measure to calculate the average delivery time (ShipDate - OrderDate), you get an error because the data types are datetime. You want to create a calculated column in the Shipments table that shows the delivery time in days. Which DAX expression should you use?

Accepted Answer

DeliveryDays = DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY). DATEDIFF calculates the difference between two dates in the specified interval (DAY). Option A is incorrect because subtracting datetime values directly may not yield the number of days reliably; it could return a duration object that needs conversion. Option B is incorrect because CALCULATE is not needed for a simple column calculation, and the arguments to DATEDIFF are swapped (OrderDate should be first). Option C is incorrect because FORMAT returns a string, not a numeric value.

Answer

DeliveryDays = Shipments[ShipDate] - Shipments[OrderDate]

Answer

DeliveryDays = CALCULATE(DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY))

Answer

DeliveryDays = FORMAT(Shipments[ShipDate] - Shipments[OrderDate], "d")