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

Question 1

After loading the data using the Power Query M code shown in the exhibit, the model contains a table with 10,000 rows. However, when users filter by OrderDate in a report, the filter does not affect the aggregated TotalDue values. What is the most likely reason?

Accepted Answer

The data is pre-aggregated in Power Query, so report-level filters cannot re-aggregate.. Option C is correct because the GROUP BY and aggregation happen in Power Query before loading, so the data is pre-aggregated and static. Filters applied in the report cannot re-aggregate because the underlying detail is lost. Option A is wrong because the Date table relationship is not the issue; the data is already aggregated. Option B is wrong because the data type is fine. Option D is wrong because the refresh is not related.

Answer

The TotalDue column is not a numeric type.

Answer

The dataset is not refreshing properly.

Answer

The Date table has no relationship to the Sales table.

Question 2

A company has a fact table with sales data and multiple dimension tables. They want to create a measure that calculates the total sales amount for the current year, but the measure returns incorrect results when used in a visual with a date hierarchy. What is the most likely cause?

Accepted Answer

The relationship between the date table and the fact table is inactive.. Option C is correct because if the relationship between the date table and the fact table is inactive, measures that rely on time intelligence functions (like TOTALYTD, SAMEPERIODLASTYEAR, or a simple SUM with date filtering) will not automatically propagate filters from the date hierarchy to the fact table. In Power BI, only one active relationship can exist between two tables; inactive relationships require explicit activation via USERELATIONSHIP in DAX. Without that, the measure ignores the date filter and returns incorrect or blank results.

Answer

The date table is not marked as a date table in Power BI.

Answer

The fact table is not in a star schema; it is snowflaked.

Answer

The relationship between the date table and the fact table is set to bidirectional cross-filtering.

Question 3

A data model contains a table 'Sales' with columns: Date, ProductID, Quantity, Amount. There is a 'Products' table with columns: ProductID, ProductName, CategoryID. A measure 'Total Sales' = SUM(Sales[Amount]) returns correct values. However, when a user creates a visual with CategoryID from 'Products' and 'Total Sales', some categories show blank. What is the most likely cause?

Accepted Answer

There are ProductID values in Sales that do not exist in Products table.. Option A is correct because when ProductID values in the Sales table do not have matching entries in the Products table, the relationship between the two tables will result in blank CategoryID values for those unmatched rows. In Power BI, a many-to-one relationship (the default) filters from the 'one' side (Products) to the 'many' side (Sales), but if a Sales row has a ProductID not present in Products, it cannot be matched, and any column from Products (like CategoryID) will appear as blank in visuals. This is a classic data integrity issue where the fact table contains orphaned foreign keys.

Answer

The 'Total Sales' measure is not properly referencing the Sales table.

Answer

The relationship between Sales and Products is set to many-to-one, single direction.

Answer

The relationship is set to both directions (bidirectional).

Question 4

A Power BI report uses a DirectQuery data source. The model includes a calculated column that uses the RELATED function to bring a value from another table. The report is performing slowly. What design change would most improve performance without compromising functionality?

Accepted Answer

Replace the calculated column with a calculated table that includes the related column.. Option D is correct because replacing the calculated column with a calculated table that includes the related column moves the join logic to query time, reducing the per-row overhead of the RELATED function in DirectQuery mode. In DirectQuery, calculated columns are evaluated row-by-row for each query, which can cause significant performance degradation; a calculated table is materialized once during refresh (or in DirectQuery, it is resolved as a single query to the source), avoiding repeated row-level lookups. This change preserves the functionality of having the related value available in the table while improving query performance.

Answer

Remove the RELATED function and merge tables in the source query.

Answer

Convert the model to Import mode.

Answer

Use a measure instead of a calculated column.

Question 5

A data model has a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. There is a 'Customers' table with columns: CustomerID, CustomerName. To analyze orders by customer, what is the best practice for modeling the relationship?

Accepted Answer

Create a one-to-many relationship from Customers to Orders with single direction.. Option A is correct because in a star schema, the Customers table (dimension) should have a one-to-many relationship to the Orders table (fact) filtered from the dimension side. This single-direction filter propagation ensures that when a customer is selected, only their orders are shown, while preventing unwanted cross-filtering from orders back to customers. This is the standard best practice for modeling dimension-to-fact relationships in Power BI.

Answer

Create a one-to-one relationship between Customers and Orders based on CustomerID.

Answer

Create an inactive relationship and use USERELATIONSHIP in measures.

Answer

Create a many-to-one relationship from Orders to Customers with both directions.

Question 6

A company has a fact table 'Sales' with a column 'SalesAmount' and a dimension table 'Date'. They want to create a measure that calculates the running total of sales over time. The Date table is marked as a date table. Which DAX expression is correct?

Accepted Answer

Running Total = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD(Date[Date])). Option B is correct because DATESYTD returns a set of dates from the start of the year to the latest date in the current filter context, and when wrapped in CALCULATE, it correctly computes a year-to-date running total. Since the Date table is marked as a date table, DATESYTD works seamlessly with the time intelligence functions in DAX.

Answer

Running Total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Date), Date[Date] <= EARLIER(Date[Date])))

Answer

Running Total = TOTALYTD(SUM(Sales[SalesAmount]), Date[Date])

Answer

Running Total = SUMX(Sales, Sales[SalesAmount])

Question 7

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

Accepted Answer

Use surrogate keys (integer IDs) for relationships instead of natural keys.. Option C is correct because using surrogate keys (integer IDs) instead of natural keys improves relationship performance and reduces storage overhead. Integer joins are faster than string or composite key joins, and surrogate keys avoid issues with changing natural key values, ensuring referential integrity and stable model relationships.

Answer

Use calculated columns to perform all transformations.

Answer

Set all relationships to bidirectional cross-filtering to avoid ambiguity.

Answer

Reference other queries as a single source to avoid duplication.

Question 8

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

Accepted Answer

To create a date table that is not available in the data source.. Option C is correct because calculated tables in Power BI are created using DAX and stored in memory, allowing you to generate a date table when no suitable date table exists in the data source. This is a common pattern to ensure a complete date range for time intelligence functions, as Power BI requires a continuous date table for functions like TOTALYTD or SAMEPERIODLASTYEAR.

Answer

To add a column that computes a value based on other columns in the same table.

Answer

To combine two tables by merging columns from one table into another.

Question 9

A company has a large fact table with sales data. The Sales table contains columns: OrderDate, ShipDate, DueDate, LineTotal, ProductKey, CustomerKey, TerritoryKey. The company needs to analyze sales by fiscal year (April 1 to March 31) and by calendar year. What is the recommended approach to model the date dimension?

Accepted Answer

Use a single date dimension table and keep only one active relationship (e.g., to OrderDate). For other dates, create measures that filter the date table using CALCULATE and FILTER.. Option A is correct because it uses a single date dimension table with one active relationship (typically to OrderDate) and leverages CALCULATE with FILTER or USERELATIONSHIP for other date roles. This is the recommended star schema design in Power BI, as it avoids unnecessary table duplication and maintains model simplicity while supporting fiscal year analysis via a calculated column in the date table.

Answer

Create a separate date dimension table for each date column in the Sales table and relate each to the fact table.

Answer

Use a single date dimension table and create multiple active relationships using bridging tables or by duplicating the dimension table for each role.

Answer

Use a single date dimension table and create inactive relationships for ShipDate and DueDate. Use USERELATIONSHIP in DAX measures.

Question 10

You are a data analyst for a retail company. You are building a Power BI model to analyze inventory levels across multiple warehouses. The source data is a SQL Server database with two tables: Inventory (WarehouseID, ProductID, StockOnHand, ReorderPoint) and Product (ProductID, ProductName, Category, UnitPrice). The Inventory table has 500,000 rows, and Product has 10,000 rows. You import both tables into Power BI. You need to create a measure that calculates the total value of inventory (StockOnHand * UnitPrice) for products that are below their reorder point. You create the following measure:

TotalValueBelowReorder = 
CALCULATE(
    SUMX(Inventory, Inventory[StockOnHand] * RELATED(Product[UnitPrice])),
    Inventory[StockOnHand] < Inventory[ReorderPoint]
)

However, the measure returns an incorrect total. You suspect an issue with the filter context. What is the most likely cause of the incorrect result?

Accepted Answer

The filter condition is applied to the entire Inventory table, but the row context from SUMX is not being considered, causing the filter to apply incorrectly.. Option B is correct because the filter condition `Inventory[StockOnHand] < Inventory[ReorderPoint]` is applied as a table-level filter on the entire Inventory table, not within the row context of SUMX. SUMX iterates over each row of Inventory, but the CALCULATE filter overrides the row context, causing the filter to be evaluated against the whole table rather than per row. This leads to an incorrect total because the condition is not evaluated for each individual product as intended.

Answer

SUMX cannot iterate over Inventory directly because it is a table reference; you need to use VALUES or a measure.

Answer

The measure needs to be written using SUM instead of SUMX because StockOnHand and UnitPrice are both numeric columns.

Answer

The RELATED function fails because the relationship between Inventory and Product is inactive.

Question 11

You are building a Power BI report for a sales team. You have a table named 'Sales' with columns: SalesDate, ProductID, Quantity, UnitPrice, Discount. You also have a 'Date' table with continuous date range. You create a relationship from Sales[SalesDate] to Date[Date]. You need to calculate the total sales amount (Quantity * UnitPrice - Discount). You write the following measure:

TotalSales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] - Sales[Discount])

The measure returns the correct total. However, when you slice by month from the Date table, the total does not change. What is the most likely cause?

Accepted Answer

The Date table is not marked as a date table, and the relationship is not set to filter in both directions.. Option C is correct because when a Date table is not marked as a date table, Power BI does not automatically enable bidirectional cross-filtering between the Date and Sales tables. Without this, slicers on the Date table (e.g., month) do not propagate filters to the Sales table, so the measure, which iterates over Sales rows, remains unaffected. Marking the Date table as a date table ensures that date hierarchies and slicers correctly filter related fact tables.

Answer

The Discount column might have negative values causing the total to be incorrect.

Answer

The measure should use SUM instead of SUMX because there are no row-level calculations.

Answer

The measure does not use the Date table; it references only the Sales table, so it ignores filters from other tables.

Question 12

You are building a Power BI model for a logistics company. You have a table named 'Shipments' with columns: ShipmentID, OriginCity, DestinationCity, Weight, Cost, ShipDate, DeliveryDate. You also have a 'City' table with columns: CityName, State, Region. You create relationships: Shipments[OriginCity] to City[CityName] and Shipments[DestinationCity] to City[CityName]. Both relationships are active and many-to-one. You create a measure to calculate total cost:

TotalCost = SUM(Shipments[Cost])

When you use a slicer on City[State], you expect to filter shipments where either the origin or destination city is in that state. However, the filter only applies to the origin city due to the active relationship. You need to modify the model so that a single slicer on City[State] filters shipments where either origin or destination is in the selected state. What is the best approach?

Accepted Answer

Create two separate dimension tables: 'Origin' and 'Destination', each containing a copy of the City data. Then create active relationships from Shipments to each. Finally, create a new calculated table 'AllCities' that unions the city names and use that for the slicer, with measures that use USERELATIONSHIP to switch between relationships.. Option A is correct because it resolves the ambiguity of having two active relationships from Shipments to City by creating separate dimension tables for origin and destination, each with an active relationship. A calculated 'AllCities' table unions all city names for the slicer, and measures use USERELATIONSHIP to activate the appropriate relationship context, enabling a single slicer to filter shipments where either origin or destination city matches the selected state.

Answer

Remove the relationship for DestinationCity and use a DAX measure with LOOKUPVALUE to get the destination state.

Answer

Create a new table 'Route' that contains all unique combinations of origin and destination cities, and use that as a filter table.

Answer

Keep both relationships active and set the cross-filter direction to both.

Question 13

A company has a Power BI semantic model with a table named 'Sales' that contains columns: OrderDate, ShipDate, Quantity, and Revenue. The company wants to create a measure that calculates the total revenue for orders shipped within 7 days of the order date. Which DAX expression should be used?

Accepted Answer

CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7). Option A is correct because it uses CALCULATE to modify the filter context, applying a filter condition that uses DATEDIFF to compute the number of days between OrderDate and ShipDate. The condition `<= 7` correctly identifies orders shipped within 7 days, and SUM aggregates the Revenue column under that filtered context.

Answer

SUMX(FILTER(Sales, Sales[ShipDate] - Sales[OrderDate] <= 7), Sales[Revenue])

Answer

CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7)

Answer

CALCULATE(SUM(Sales[Revenue]), Sales[ShipDate] - Sales[OrderDate] <= 7)

Question 14

You are designing a Power BI semantic model for a retail company. The model must support reporting on sales by product, store, and date. You need to decide on the modeling approach. Which TWO actions should you take? (Select exactly two.)

Accepted Answer

Create a separate date dimension table and mark it as a date table.. Option A is correct because creating a separate date dimension table and marking it as a date table enables time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) to work correctly in DAX. This also ensures that the date column is recognized as a continuous date hierarchy, allowing proper filtering and slicing by year, month, and quarter across all fact tables.

Answer

Add calculated columns for year, month, and quarter to the Sales table.

Answer

Create relationships between tables using the foreign key columns.

Answer

Combine all data into a single flat table to simplify the model.

Question 15

You are reviewing a Power Query M script used to create a table in Power BI. The script imports data from SQL Server, filters for orders in 2022, groups by ProductID to sum revenue, sorts descending, and takes the top 10. However, the table loads slowly. You need to improve performance. Which change should you make?

Accepted Answer

Modify the script to use a native SQL query that performs the filtering and aggregation on the server side.. Option C is correct because pushing filtering, grouping, and aggregation to SQL Server via a native query reduces the volume of data transferred to Power BI and leverages the database engine's optimized execution. This minimizes memory and processing overhead in Power Query, directly addressing the slow load time caused by performing these operations on imported data.

Answer

Add a Table.Buffer step before the filter to speed up subsequent operations.

Answer

Remove the sorting step because it is unnecessary for the final table.

Answer

Combine the filter, group, and sort into a single step using Table.Buffer.

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

Exhibit

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

Exhibit