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

Question 1

Which TWO are valid reasons to use a star schema design in Power BI? (Select exactly 2.)

Accepted Answer

It improves query performance by reducing the number of joins needed. The correct answers are A and D. A is correct because star schema reduces the number of joins in queries by organizing data into fact and dimension tables, improving performance. D is correct because storing dimension attributes in separate tables avoids duplication, reducing overall model size. B is incorrect: star schema uses one-to-many relationships, not many-to-many. C is incorrect: while time intelligence requires a date table, star schema is not a prerequisite. E is incorrect: star schema typically increases the number of tables compared to a flat table.

Answer

It allows many-to-many relationships between dimensions

Answer

It is required to use time intelligence functions

Answer

It reduces the number of tables in the model

Question 2

You have a DAX measure that sums the Amount column for rows where Quantity is greater than 10. The Sales table has 10 million rows. What is a potential performance issue with this measure?

Accepted Answer

The SUMX iterator can be slow on large tables; consider using CALCULATE instead. The SUMX iterator processes each row individually, which can be slow on large tables like Sales with 10 million rows. It is more efficient to use CALCULATE with a filter argument to aggregate the data without row-by-row iteration. Option B is incorrect because while FILTER can be slow, it is often used with CALCULATE and does not inherently cause a full table scan if the condition is simple. Option C is incorrect because context transition is not the primary performance issue in this scenario. Option D is incorrect because the condition 'Quantity > 10' is simple and can be optimized by the query engine.

Answer

The FILTER function will cause a full table scan each time the measure is evaluated

Answer

The measure uses context transition which can cause performance issues

Answer

The measure uses a complex condition that cannot be optimized

Question 3

You have a Power BI data model that uses a DirectQuery connection to a SQL Server database. The model contains a fact table 'OrderDetails' with 50 million rows and a dimension table 'Products' with 10,000 rows. You need to create a measure that calculates the average order quantity per product. What is the best approach to optimize performance?

Accepted Answer

Use AVERAGEX over the Products table and sum the quantity for each product. Using AVERAGEX with the Products table reduces the number of rows processed; it calculates the average per product, which is more efficient than iterating over millions of rows. Option A is wrong because CALCULATE with FILTER on the fact table can be slow. Option B is wrong because calculated columns in DirectQuery are evaluated row by row and can degrade performance. Option D is wrong because creating an aggregated table in the source is not always feasible and adds complexity.

Answer

Use CALCULATE with a filter on the Products table

Answer

Create a calculated column in the Products table that sums the quantity

Answer

Create a summarized table in the source database and import it

Question 4

You are building a star schema in Power BI. A fact table contains sales transactions with columns: OrderID, CustomerID, ProductID, Quantity, UnitPrice, Discount, and OrderDate. You need to create a dimension table for customers. Which columns should be included in the Customer dimension?

Accepted Answer

CustomerID, CustomerName, City, Region. In a star schema, dimension tables contain descriptive attributes and a unique surrogate key. Option D (CustomerID, CustomerName, City, Region) is correct because it includes the key (CustomerID) and descriptive attributes (CustomerName, City, Region) without linking to other dimensions or fact grain identifiers. Option A includes ProductID, which belongs to the Product dimension. Option B includes OrderID, which is a fact identifier. Option C includes OrderDate, which should be in a Date dimension.

Answer

CustomerID, CustomerName, ProductID

Answer

CustomerID, CustomerName, OrderID

Answer

CustomerID, CustomerName, OrderDate

Question 5

You have a Power BI model with a table 'Sales' and a table 'Date'. The relationship between them is many-to-one (Sales[OrderDate] -> Date[Date]). You notice that when you filter by Date[Year], some sales rows are not included. What is the most likely cause?

Accepted Answer

The relationship is inactive.. An inactive relationship does not automatically propagate filters between tables. Even though the relationship is defined, it must be explicitly activated using a DAX function like USERELATIONSHIP for filters to flow from Date to Sales. Without that, filtering by Date[Year] will not restrict Sales rows, causing some rows to appear missing if the user expects the filter to apply.

Answer

The relationship has Referential Integrity enforced.

Answer

The cross filter direction is set to Single.

Answer

The Date table has blank rows.

Question 6

You are reviewing a Power BI model definition in TMSL. The model has incremental refresh configured with two partitions. When the model refreshes on July 1, 2024, which data will be refreshed in Partition1?

Accepted Answer

All rows where OrderDate is between 2024-01-01 and 2024-04-01.. In TMSL, incremental refresh partitions are defined by date range parameters like RangeStart and RangeEnd. On July 1, 2024, Partition1 covers data from January 1, 2024 to April 1, 2024. Even though this range is in the past relative to the refresh date, the partition will be fully refreshed for that date range. Option A is incorrect because incremental refresh refreshes all rows in the partition, not just new rows. Option B is incorrect because past partitions are still refreshed. Option C is incorrect because it implies only modified data is refreshed, but the entire partition is refreshed.

Answer

Only new rows added since last refresh.

Answer

No data, because the partition is in the past.

Answer

Only data modified since the last refresh.

Question 7

You are designing a Power BI data model that includes a Sales table and a Product table. The Product table contains a column 'Category' with values like 'Electronics', 'Clothing', etc. You need to create a measure that calculates the total sales amount for the 'Electronics' category only. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), Product[Category] = 'Electronics'). It uses CALCULATE to apply a filter condition directly on the Product[Category] column, which automatically performs a context transition along the existing relationship between Sales and Product. This efficiently computes the sum of Sales[Amount] only for rows where the related Product category is 'Electronics'.

Answer

SUM(Sales[Amount])

Answer

SUMX(FILTER(Sales, RELATED(Product[Category]) = 'Electronics'), Sales[Amount])

Answer

CALCULATE(SUM(Sales[Amount]), FILTER(Product, Product[Category] = 'Electronics'))

Question 8

You have a Power BI dataset that includes a table 'Orders' with columns: OrderDate, ShipDate, CustomerID, and SalesAmount. You need to create a calculated column that shows the number of days between OrderDate and ShipDate. Which DAX expression should you use?

Accepted Answer

DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY). The DATEDIFF function calculates the difference between two dates in the specified interval (DAY). Option A (YEAR) returns the year from a date, not a difference. Option C (ENDOFMONTH) returns the last date of the month, not a difference. Option D (DATEADD) returns a date shifted by a specified interval, not a numeric difference.

Answer

YEAR(Orders[OrderDate])

Answer

ENDOFMONTH(Orders[OrderDate])

Answer

DATEADD(Orders[OrderDate], 1, DAY)

Question 9

You are examining a Power BI model definition. The Date table is generated via Power Query M code. The partition mode is 'import'. What will happen when the dataset refreshes after December 31, 2025?

Accepted Answer

The table will be refreshed with the same date range (2020-2025).. Since the partition mode is 'import' (not incremental), the entire table is replaced on each refresh. The M code generates dates from 2020-01-01 to 2025-12-31, so after 2025, no new dates will be added unless the code is updated. Option A is wrong because incremental refresh is not configured. Option B is wrong because the table is fully reloaded, not appended. Option D is wrong because the refresh will succeed, but no new dates beyond 2025 will appear.

Answer

The existing table will be appended with new rows.

Answer

The refresh will fail because EndDate is in the past.

Answer

New dates after 2025-12-31 will be added incrementally.

Question 10

You need to create a hierarchy in Power BI that allows users to drill down from Year to Quarter to Month. Which of the following actions is required?

Accepted Answer

In the Fields pane, right-click a column and select 'Create hierarchy', then add child columns.. A hierarchy is created by selecting multiple columns in the same table and defining a parent-child relationship. Option A is incorrect because relationships are between tables, not within a hierarchy. Option B is incorrect because hierarchies can be created in the model view, not only in report view. Option D is incorrect because you cannot create a hierarchy in Power Query.

Answer

Create a relationship between the Year, Quarter, and Month columns.

Answer

Create the hierarchy in the report view by grouping fields.

Answer

Use Power Query to merge the columns into one.

Question 11

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

Accepted Answer

To create a summary table that is not present in the source.. Calculated tables are used to create new tables based on DAX expressions, allowing you to store summary data (A) or create specialized tables like a date table with a continuous range of dates (C). Option B is incorrect because calculated tables do not inherently reduce model size; they store rows and can increase size. Option D is incorrect because creating a column that depends on other columns in the same row is the purpose of a calculated column, not a calculated table.

Answer

To reduce model size by storing only aggregated data.

Answer

To create a column that depends on other columns in the same row.

Question 12

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

Accepted Answer

Hide columns that are not needed in reports.. Correct answers are A, B, and C. A: Hiding columns that are not needed reduces model size and improves clarity. B: Avoiding bi-directional cross-filtering prevents performance degradation caused by complex filter propagation. C: Using a star schema design with dimension and fact tables optimizes query performance. Option D is incorrect because using many-to-many relationships without bridge tables can cause ambiguity and poor performance. Option E is incorrect because measures are generally more efficient than calculated columns for dynamic aggregations.

Answer

Use many-to-many relationships directly without bridge tables.

Answer

Use calculated columns instead of measures for aggregations.

Question 13

You are analyzing a DAX query. What is the purpose of the 'Filter Context' column in the result?

Accepted Answer

It returns the total number of sales rows regardless of product category.. The 'Filter Context' column in a DAX query represents the set of filters applied to the data at the time of evaluation. When a measure like COUNTROWS(Sales) is used, it counts all rows in the Sales table that are not filtered out by the current filter context. Option B is correct because COUNTROWS returns the total number of rows in the Sales table, regardless of product category, unless a filter on category is explicitly applied.

Answer

It returns the total sales for the current year, ignoring category.

Answer

It returns the number of product categories in the current context.

Answer

It counts the number of distinct products in the current filter context.

Question 14

You have a Power BI model with a table 'Sales' and a separate 'Calendar' table. The relationship between Sales[OrderDate] and Calendar[Date] is active. You need to create a measure that calculates the total sales for the previous month relative to the current filter context. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Calendar[Date])). Both options C and D are correct DAX expressions for calculating total sales for the previous month. PREVIOUSMONTH returns all dates in the previous month based on the current filter context, and when used with CALCULATE, it correctly filters the sales to the previous month. DATEADD with -1 month also shifts the date range back one month, achieving the same result. While PREVIOUSMONTH requires the calendar table to be marked as a date table, it is a standard and valid time intelligence function. Option A returns the same period last year, option B returns next month, so they are incorrect.

Answer

CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))

Answer

CALCULATE(SUM(Sales[Amount]), NEXTMONTH(Calendar[Date]))

Question 15

You need to create a relationship between two tables in Power BI. Table A has a column 'ProductID' with unique values. Table B has a column 'ProductID' with duplicate values. Which relationship cardinality should you choose?

Accepted Answer

One-to-many (Table A to Table B). Table A has unique values and Table B has duplicates, so the relationship should be one-to-many from Table A to Table B. Option A (many-to-one) would be the reverse direction and is incorrect. Option C (one-to-one) requires unique values on both sides, which is not the case. Option D (many-to-many) would require a junction table and is unnecessary here.

Answer

Many-to-one (Table A to Table B)

Answer

One-to-one

Answer

Many-to-many