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

Question 1

You have a Power BI data model that contains a table 'Sales' with columns: Date, ProductID, and Revenue. You also have a 'Products' table with columns: ProductID, ProductName, and Category. You want to create a measure that calculates total revenue for the current year. Which DAX expression should you use?

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

Accepted Answer

TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date]). TOTALYTD is a time intelligence function that calculates the year-to-date total of an expression, given a date column. It requires a date table marked as a date table for proper functionality. Option A is incorrect because while DATESYTD can be used directly as a filter in CALCULATE, it is less straightforward and not the optimal choice; TOTALYTD is specifically designed for this task. Option B is incorrect: SUMX with a logical comparison does not filter the date context; it multiplies revenue by 1 or 0 per row, which does not produce a correct YTD total. Option C could technically work using FILTER, but it is less efficient and not the recommended time-intelligence approach; TOTALYTD is purpose-built for this task.

Answer

CALCULATE(SUM(Sales[Revenue]), DATESYTD('Date'[Date]))

Answer

SUMX(Sales, Sales[Revenue] * (YEAR(Sales[Date]) = YEAR(TODAY())))

Answer

CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY())))

Question 2

You are modeling data for a retail company. The source data contains a table 'Transactions' with columns: TransactionID, StoreID, ProductID, Quantity, and SalesAmount. You need to create a star schema in Power BI. What should you do with the TransactionID column?

Accepted Answer

Keep TransactionID in the fact table. TransactionID uniquely identifies each transaction and should remain in the fact table as a degenerate dimension (a key with no other attributes). This preserves the granularity of the fact table. Option A is incorrect because creating a separate dimension for TransactionID would be unnecessary and wasteful, as it has no descriptive attributes. Option C is incorrect because TransactionID is not an attribute of the Product dimension. Option D is incorrect because removing TransactionID would lose the ability to reference individual transactions, reducing the model's granularity.

Answer

Create a separate dimension table for transactions

Answer

Move TransactionID to the Product dimension

Answer

Remove the TransactionID column to reduce model size

Question 3

You are creating a Power BI report that requires a many-to-many relationship between 'Product' and 'Customer' tables. What is the recommended way to implement this?

Accepted Answer

Create a bridge table with ProductID and CustomerID and create relationships to both tables. In Power BI, a many-to-many relationship between two tables (e.g., Product and Customer) is best implemented using a bridge table that contains the foreign keys (ProductID and CustomerID) from both tables. This bridge table resolves the many-to-many cardinality by creating two one-to-many relationships: one from Product to the bridge table and one from Customer to the bridge table. This approach ensures proper filtering and avoids ambiguity in the data model, which is a standard best practice in dimensional modeling.

Answer

Combine Product and Customer into a single table

Answer

Create a direct many-to-many relationship using the 'many-to-many' cardinality option in Power BI

Answer

Use a calculated column to concatenate IDs

Question 4

You have a Power BI model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, and TotalAmount. You have a 'Customers' table with columns: CustomerID, CustomerName, and Country. You need to create a measure that calculates the total sales amount for customers who have placed orders in the last 30 days. Which DAX measure should you use?

Accepted Answer

CALCULATE(SUM(Orders[TotalAmount]), Orders[OrderDate] >= TODAY() - 30). The CALCULATE function applies a filter on the Orders table to include only rows where OrderDate is within the last 30 days, then sums the TotalAmount. Option A is incorrect because although SUMX with FILTER could work, it is not the optimal approach; the exam expects the use of CALCULATE for filter context modification. Option C is incorrect because it incorrectly filters the Customers table instead of the Orders table. Option D is incorrect because ALL removes all filters, including the date filter.

Answer

SUMX(FILTER(Orders, Orders[OrderDate] >= TODAY() - 30), Orders[TotalAmount])

Answer

CALCULATE(SUM(Orders[TotalAmount]), FILTER(Customers, Customers[CustomerID] IN VALUES(Orders[CustomerID]) && Orders[OrderDate] >= TODAY() - 30))

Answer

CALCULATE(SUM(Orders[TotalAmount]), ALL(Orders), Orders[OrderDate] >= TODAY() - 30)

Question 5

You are designing a data model for a financial analysis report. The source data includes a 'Budget' table with columns: Department, Account, Month, and BudgetAmount. The 'Actuals' table has the same structure. You need to create a combined measure that shows the variance (Actual - Budget) for each Department and Account. What is the best approach?

Accepted Answer

Create separate dimension tables for Department and Account, and create fact tables for Budget and Actuals with relationships. It follows the star schema design pattern, with separate dimension tables for Department and Account that allow filtering and slicing across both Budget and Actual fact tables. This enables a simple DAX measure like `Variance = SUM(Actuals[Amount]) - SUM(Budget[Amount])` using relationships. Option B is incorrect because merging Budget and Actuals into a single denormalized table loses the ability to treat them as separate fact tables and makes variance calculations more complex. Option C is incorrect because using SUMMARIZE to create a calculated table is inefficient and does not leverage proper relationships. Option D is incorrect because appending with a 'Type' column turns both datasets into a single table, requiring filters or nested calculations instead of straightforward measures.

Answer

Create a single table by merging Budget and Actuals on Department, Account, and Month

Answer

Create a calculated table using SUMMARIZE and then use DAX measures

Answer

Use Power Query to append Budget and Actuals with a 'Type' column

Question 6

You have a Power BI model with a table 'Sales' that contains a column 'OrderDate'. You need to create a calculated column that extracts the year from OrderDate. Which DAX expression should you use?

Accepted Answer

YEAR(Sales[OrderDate]). The YEAR function returns a four-digit integer representing the year from a date. Option A (DATEPART) is not a valid DAX function; it is a T-SQL function and will cause an error in Power BI. Option C (CALENDAR) returns a table of dates, not a scalar value, and cannot be used for a calculated column. Option D (FORMAT) returns a text string, not an integer, and may affect sorting and filtering behavior.

Answer

DATEPART("year", Sales[OrderDate])

Answer

CALENDAR(YEAR(Sales[OrderDate]), YEAR(Sales[OrderDate]))

Answer

FORMAT(Sales[OrderDate], "yyyy")

Question 7

Which TWO of the following are valid reasons to use a calculated table in Power BI instead of a table from the source?

Accepted Answer

To create a bridge table for many-to-many relationships. Options A and C are correct. A calculated table can be used to create a bridge table for resolving many-to-many relationships between tables. It can also be used to create a date table for time intelligence functions, which requires a continuous date range not necessarily present in the source. Option B is incorrect because aggregated data from the source is better handled with Power Query transformations that push computations to the source. Option D is incorrect because calculated tables are fully recalculated on each refresh and do not support incremental refresh. Option E is incorrect because calculated tables increase model size by storing additional data in memory.

Answer

To aggregate data from the source before loading

Answer

To enable incremental data refresh

Answer

To reduce the overall storage size of the model

Question 8

Which THREE of the following are best practices for designing a Power BI data model?

Accepted Answer

Implement business logic in measures rather than calculated columns. Options C, D, and E are correct. For Power BI data models, it is a best practice to implement business logic in measures rather than calculated columns (C), use a star schema design with dimension and fact tables (D), and use surrogate keys for dimension tables (E). Option A is incorrect because composite keys can complicate relationships and negatively impact performance; surrogate keys are preferred. Option B is incorrect because bidirectional cross-filtering should be used sparingly and only when necessary, not by default, as it can lead to ambiguous filter propagation and performance issues.

Answer

Use composite keys in relationships for better performance

Answer

Enable bidirectional cross-filtering by default

Question 9

Which TWO of the following are valid DAX functions for creating a calculated table?

Accepted Answer

FILTER. Options C and D are correct. FILTER returns a table. CALENDAR returns a table. Option A is wrong because SUM returns a scalar. Option B is wrong because COUNT returns a scalar. Option E is wrong because SELECTEDVALUE returns a scalar.

Answer

SUM

Answer

COUNT

Answer

SELECTEDVALUE

Question 10

You have the above measure in a Power BI model. The 'Date' table is marked as a date table and has a relationship to Sales[OrderDate]. The measure returns blank for all months. What is the most likely cause?

Accepted Answer

The Date table does not contain all dates in the continuous range. The most likely cause is that the Date table does not contain all dates in the continuous range required by the DATESYTD function. DATESYTD expects a continuous set of dates to compute the year-to-date total correctly. If the Date table is missing any dates (e.g., weekends or holidays), the function may return blank for months that include those missing dates. Option B is incorrect because the relationship direction is from Date to Sales[OrderDate], which is correct. Option C is incorrect because the syntax of DATESYTD is likely correct; the issue is data-related. Option D is incorrect because the Date table is marked as a date table, as stated in the scenario.

Answer

The relationship is set to filter in the wrong direction

Answer

The measure uses incorrect syntax for DATESYTD

Answer

The 'Date' table is not marked as a date table

Question 11

You have the above Power Query M script. What is the result of this transformation?

Accepted Answer

A table with one row per ProductID and columns: ProductID, TotalQty. The script filters rows where OrderDate >= Jan 1 2023, removes the ProductDescription column, and groups by ProductID summing Quantity. The result is a table with one row per ProductID and a TotalQty column. Option A is wrong because it does not retain all columns. Option B is wrong because it aggregates. Option C is wrong because it does not keep individual rows.

Answer

A table with one row per ProductID and columns: ProductID, Quantity

Answer

A table with all sales rows for 2023 and columns: ProductID, Quantity

Answer

A table with all sales rows for 2023 and columns: OrderDate, ProductID, Quantity, TotalQty

Question 12

You have the above calculated column in a Power BI model. Some rows show blank values for Profit Margin even though Profit and SalesAmount are not blank. What is the most likely cause?

Accepted Answer

SalesAmount is zero for those rows. SalesAmount is zero for those rows. The DIVIDE function in Power BI returns BLANK when the denominator is zero or BLANK, preventing division by zero errors. Since Profit and SalesAmount are not blank, Profit Margin being blank indicates that SalesAmount is zero for those rows. Option A is incorrect because the syntax of the calculated column is correct. Option B is incorrect because the columns are numeric. Option C is incorrect because DIVIDE can handle large numbers.

Answer

The calculated column syntax is incorrect

Answer

The columns are not numeric

Answer

The DIVIDE function cannot handle large numbers

Question 13

You are building a star schema in Power BI. Which table design best supports filtering a sales fact table by product category?

Accepted Answer

Create a separate Product dimension table with category, related to Sales by ProductID.. In a star schema, dimension tables like Product are separate from fact tables. Relating Product to Sales by a common key (ProductID) enables filtering of sales by product category. Option A is wrong because a single table with all data is a flat table, not a star schema, and leads to redundancy and poor performance. Option B is wrong because merging tables by appending rows (union) is not appropriate; instead, a relational model with a dimension table is needed. Option D is wrong because storing product category directly in the Sales fact table violates star schema normalization and causes redundancy.

Answer

Use a single table containing all sales and product attributes.

Answer

Merge Sales and Product tables into one by appending rows.

Answer

Store product category in the Sales fact table.

Question 14

You have a date table with columns: Date, Year, Month, Quarter, Day. To enable time intelligence functions like TOTALYTD, what is the minimum requirement?

Accepted Answer

A separate date table with a continuous range of dates and a relationship to the fact table.. Time intelligence functions like TOTALYTD require a separate date table with a continuous range of dates that has a relationship to the fact table. This allows Power BI to properly evaluate time-based calculations across years.

Answer

Mark the date table as a date table in Power BI.

Answer

Combine date and fact tables into one table.

Answer

A single date column in the fact table.

Question 15

You need to create a calculated column that categorizes products based on price: 'Low' (<$10), 'Medium' ($10-$50), 'High' (>$50). Which DAX expression should you use?

Accepted Answer

SWITCH(TRUE(), Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High"). It uses the SWITCH function with TRUE() as the first argument, allowing a series of Boolean conditions to be evaluated in order. Option A is incorrect because SWITCH without TRUE() expects exact value matches, not relational comparisons. Option B is wrong because LOOKUPVALUE is used for retrieving values from another table, not for conditional categorization. Option D is wrong because the IF function can only handle two outcomes; for three categories, nested IFs would be needed, and the syntax shown is invalid.

Answer

SWITCH(Product[Price], <10, "Low", <=50, "Medium", "High")

Answer

LOOKUPVALUE(Category, Product[Price], Product[Price])

Answer

IF(Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High")