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

Question 1

You are building a star schema in Power BI. Your fact table contains sales data at the transaction level. Which of the following is the best practice for modeling the date dimension to support time intelligence functions like year-to-date (YTD) calculations?

Accepted Answer

Create a date table with a contiguous range of dates and mark it as a date table. In a star schema, a dedicated date dimension table with a contiguous range of dates is essential for proper time intelligence. A single date column in the fact table is not sufficient because it lacks attributes like year, month, and quarter. Option C is correct. Option A is wrong because multiple date columns in the fact table would violate normalization. Option B is wrong because a date table covering only sales dates would miss dates with no sales, causing incorrect YTD totals. Option D is wrong because the date table should have a unique date key.

Answer

Use the fact table's date column directly in measures without a date table

Answer

Create a date table with only the dates that appear in the fact table

Answer

Use multiple date columns (e.g., OrderDate, ShipDate) in the fact table

Question 2

You have a Power BI model with tables: Sales (DateKey, Amount) and Date (DateKey, Year, Month, Day). The measure above is intended to show sales for the current year ignoring any filters on date. However, when placed in a matrix with Month on rows, it returns the same value for every month. What is the most likely reason?

Accepted Answer

The FILTER function removes month context from the Date table. The FILTER function uses ALL(Date) which removes all filters from the Date table, including the month filter from the matrix row context. Therefore, the measure always returns the total sales for the entire current year regardless of the month. Option B is correct. Option A is wrong because the expression is valid syntactically. Option C is wrong because there is no relationship ambiguity issue. Option D is wrong because the table name is used correctly.

Answer

The CALCULATE function is misused and should be replaced with SUMX

Answer

The Date table is not marked as a date table

Answer

The relationship between Sales and Date is inactive

Question 3

You are designing a data model that will support self-service analytics. You have a Sales table with over 100 million rows. Which of the following modeling approaches would provide the best query performance while maintaining a user-friendly experience?

Accepted Answer

Design a star schema with a fact table and dimension tables. Star schema design separates facts and dimensions, optimizing query performance by reducing table scans and enabling efficient aggregations. Option C is correct. Option A is wrong because a single flat table with many columns can cause performance issues and is harder to maintain. Option B is wrong because a snowflake schema can increase query complexity and reduce performance due to additional joins. Option D is wrong because importing all tables into a single table duplicates data and loses normalization benefits.

Answer

Create a single flat table with all columns

Answer

Import all tables into a single table using Power Query merge

Answer

Use a snowflake schema with multiple levels of dimensions

Question 4

You have a Power BI model where the Sales table is filtered by a Customer dimension. Users report that when they filter a measure from the Customer table (e.g., Customer Count), it does not affect the Sales visual. What is the most likely cause?

Accepted Answer

The cross-filtering direction is set to one direction (Customer -> Sales), but the filter is applied on Customer. The crossFilteringBehavior is set to 'oneDirection', which means filters flow only from Customer to Sales, not the other way. To allow Customer filters to affect Sales, it should be 'bothDirections' or use bidirectional cross-filtering. Option A is correct. Option B is wrong because security filtering does not impact user filtering. Option C is wrong because the relationship is active. Option D is wrong because the cardinality is not specified but oneDirection is the key.

Answer

The relationship is inactive

Answer

The relationship cardinality is many-to-many

Answer

The security filtering behavior is set to oneDirection

Question 5

You are creating a Power BI report that uses a composite model (DirectQuery for large tables and Import for small dimension tables). You want to ensure that measures referencing the DirectQuery tables are responsive. Which of the following design choices should you avoid?

Accepted Answer

Use complex time intelligence measures that iterate over the fact table. Using many DAX functions that require iteration over large tables can cause performance degradation in DirectQuery mode. Option D is correct. Option A is acceptable because dimension tables are small. Option B is a best practice. Option C is acceptable because aggregations can improve performance.

Answer

Use measures that aggregate columns rather than rows

Answer

Create a date dimension table imported from the source

Answer

Create user-defined aggregations for the DirectQuery table

Question 6

You are modeling data for a sales analysis. The Sales table includes a column 'ProductID' and a related Products table. You need to create a measure that calculates the total sales amount only for products in the 'Electronics' category. Which DAX measure should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Products[Category] = "Electronics")). CALCULATE with KEEPFILTERS ensures that the filter context from the Products table is preserved while adding the category filter. Option A is correct. Option B is wrong because FILTER would work but is less efficient and might not respect existing filters. Option C is wrong because ALL removes filters. Option D is wrong because it uses a table name incorrectly.

Answer

CALCULATE(SUM(Sales[Amount]), ALL(Products[Category] = "Electronics"))

Answer

CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Category] = "Electronics"))

Answer

SUMX(Products, IF(Products[Category]="Electronics", SUM(Sales[Amount])))

Question 7

You are loading data into Power BI. The above Power Query script connects to a SQL Server database and loads aggregated sales by customer. However, the report requires the ability to drill down to individual transactions for a selected customer. What is the best approach to meet this requirement without losing the performance benefit of aggregation?

Accepted Answer

Use a composite model with a DirectQuery connection to the source for detail data. Use a composite model: import the aggregated table for high-level queries and use DirectQuery for the full detail table when drilling down. Option D is correct. Option A is wrong because it loses performance. Option B is wrong because the script filters out data before 2023. Option C is wrong because the script groups data, removing transaction details.

Answer

Remove the Grouped step and add a measure to aggregate at query time

Answer

Modify the filter date to include all years

Answer

Remove the Grouped step and import all transactions

Question 8

You have a Power BI data model with a table 'Orders' containing columns: OrderID, CustomerID, OrderDate, Amount. You want to create a measure that calculates the running total of sales over time, ordered by OrderDate. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Orders[Amount]), FILTER(ALL(Orders), Orders[OrderDate] <= EARLIER(Orders[OrderDate]))). CALCULATE with FILTER and EARLIER is a common pattern for running totals. Option D is correct. Option A is wrong because TOTALYTD is for year-to-date, not running total across all dates. Option B is wrong because SUM with FILTER and ALLSELECTED may not preserve order correctly. Option C is wrong because RANKX is not needed.

Answer

CALCULATE(SUM(Orders[Amount]), FILTER(ALLSELECTED(Orders), Orders[OrderDate] <= MAX(Orders[OrderDate])))

Answer

SUMX(Orders, RANKX(ALL(Orders), Orders[OrderDate], , ASC) * Orders[Amount])

Answer

TOTALYTD(SUM(Orders[Amount]), Orders[OrderDate])

Question 9

You are designing a Power BI model for a multinational company. The fact table contains sales data in multiple currencies. You need to convert all amounts to a single reporting currency (USD) using exchange rates from a separate table. What is the recommended approach?

Accepted Answer

Create a relationship between the fact table and the exchange rate table on date and currency, then create a measure using SUMX with rate lookup. Create a relationship between the fact table and the exchange rate table using date and currency, then create a measure that multiplies the amount by the exchange rate. Option B is correct. Option A is wrong because it does not scale. Option C is wrong because calculated columns are less flexible. Option D is wrong because Power Query conversion would be static.

Answer

Add a calculated column to the fact table that multiplies by the exchange rate from a separate table

Answer

Hard-code the exchange rate in a measure

Answer

Use Power Query to merge the exchange rate table into the fact table during data load

Question 10

Which TWO of the following are best practices for modeling many-to-many relationships in Power BI?

Accepted Answer

Use a bridge table to resolve the many-to-many relationship. Option A and C are correct. Option A is correct because a bridge table normalizes many-to-many relationships. Option C is correct because using a measure with SUMX and a bridge table is a common pattern. Option B is wrong because bidirectional filtering can cause ambiguity. Option D is wrong because composite models are not specifically for many-to-many. Option E is wrong because ignoring the relationship is not a solution.

Answer

Set cross-filter direction to both for all relationships

Answer

Use a composite model with DirectQuery for one of the tables

Answer

Create a calculated table that combines the two tables

Question 11

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

Accepted Answer

The value is needed in a row-level security rule. Option A, B, and D are correct. Option A is correct because calculated columns are evaluated during data refresh and can be used as slicers. Option B is correct because calculated columns can be used in relationships. Option D is correct because calculated columns are available in the data model for row-level security. Option C is wrong because measures are more efficient for dynamic aggregations. Option E is wrong because measures are better for time intelligence.

Answer

The value is an aggregation (e.g., SUM) that changes with user interaction

Answer

The value is a time intelligence calculation that depends on the current filter context

Question 12

Which TWO of the following are true about using the 'Mark as Date Table' feature in Power BI?

Accepted Answer

The date table must have a column with unique date values. Option A and D are correct. Option A is correct because marking a date table enables time intelligence functions. Option D is correct because the date table must have a unique date column. Option B is wrong because the date column must contain unique dates. Option C is wrong because marking a date table is not required for relationships, but it enables automatic time intelligence. Option E is wrong because marking a date table does not automatically add all date columns.

Answer

It is required for any relationship involving a date column

Answer

The date column can contain duplicate dates

Answer

It automatically creates all date hierarchy columns (Year, Quarter, Month, Day)

Question 13

You run the above DAX query in DAX Studio. The result set shows a row for each Category and Year combination, but the Total Sales and Total Cost columns are both blank. What is the most likely reason?

Accepted Answer

There is no active relationship between the Sales table and the Product and Date tables. SUMMARIZECOLUMNS automatically adds a filter context. If there is no relationship between Sales and Product or Date, the columns will be blank. Option B is correct. Option A is wrong because SUMMARIZECOLUMNS can compute multiple expressions. Option C is wrong because BLANK treatment does not affect sum. Option D is wrong because ordering does not affect calculation.

Answer

The Sales[Amount] column contains only BLANK values

Answer

SUMMARIZECOLUMNS cannot compute two aggregate columns

Answer

The ORDER BY clause is causing the aggregation to fail

Question 14

You are modeling a data source that includes a table with columns: EmployeeID, ManagerID. You need to create a hierarchy that shows the reporting structure. Which Power BI feature should you use?

Accepted Answer

Use DAX PATH and PATHITEM functions to create a calculated hierarchy table. Parent-child hierarchy functions, such as PATH, can be used to flatten a hierarchy. Option C is correct. Option A is wrong because relationships are for separate tables. Option B is wrong because calculated columns alone cannot create a hierarchy. Option D is wrong because grouping is for binning values.

Answer

Create a self-referencing relationship

Answer

Add a calculated column using IF statements

Answer

Use the 'Group' feature in Power Query

Question 15

You are reviewing a Power BI model definition. The Sales table has a partition with an M expression. You need to ensure that the table can be refreshed incrementally. Which additional property must be set?

Accepted Answer

Set the 'Mode' property of the table to 'Import' and define a range partition. For incremental refresh, the table must have range partitions defined, and the partition source must be configured with a range start and end. Option B is correct. Option A is wrong because the table already has one partition. Option C is wrong because data type conversion is not needed. Option D is wrong because annotations are optional.

Answer

Add an annotation to enable incremental refresh

Answer

Change the data type of OrderDate to Date

Answer

Add more partitions to the table