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

Question 1

A Power BI developer creates a star schema with a fact table Sales and dimension tables Customer, Product, Date. The relationship between Sales and Date is active. The developer wants to create a measure that calculates the total sales for the previous month relative to any selected month. Which DAX expression should the developer use?

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

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Date[Date])). PREVIOUSMONTH(Date[Date]) returns a set of dates for the full previous month relative to the last date in the current filter context. When wrapped in CALCULATE, it shifts the filter on the Date dimension to the prior month, correctly computing total sales for the previous month regardless of the selected month.

Answer

CALCULATE(SUM(Sales[Amount]), DATESMTD(Date[Date]))

Answer

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

Answer

CALCULATE(SUM(Sales[Amount]), DATEADD(Date[Date], -1, MONTH))

Question 2

A Power BI data model includes a table 'Orders' with columns OrderID, CustomerID, OrderDate, SalesAmount. The model also has a 'Date' table and a 'Customer' table. The relationships are: Orders[CustomerID] -> Customer[CustomerID] (many-to-one, single direction) and Orders[OrderDate] -> Date[Date] (many-to-one, single direction). A user creates a measure that sums SalesAmount and then filters by a slicer on Customer[City]. The slicer works correctly. However, when the user adds another slicer on Date[Year], the measure does not respect both slicers simultaneously. What is the most likely cause?

Accepted Answer

The measure might be using ALL or ALLEXCEPT that removes the filter context from the Date table.. The measure likely uses ALL or ALLEXCEPT, which removes the filter context from the Date table. Even though the relationships are correctly configured and filters from the Date slicer propagate to Orders via the single-direction relationship, if the measure explicitly ignores those filters using a function like ALL(Date[Year]) or ALLEXCEPT(Orders, ...), the Date slicer will have no effect on the measure. This is a common DAX mistake where filter removal functions override slicer selections.

Answer

The Customer and Date tables are not related to each other.

Answer

The relationship between Orders and Date is inactive.

Answer

The relationships are set to single direction, so filters from Date do not propagate to Orders.

Question 3

A Power BI developer is designing a data model for sales analysis. The model includes a Sales fact table and dimension tables: Product, Customer, Date, and Store. Which TWO design considerations are best practices for optimizing query performance?

Accepted Answer

Create a separate date table and mark it as a date table to enable time intelligence functions.. Creating a separate date table and marking it as a date table enables Power BI to use built-in time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) that rely on a continuous, contiguous date range. This design ensures optimal performance by allowing the engine to generate efficient DAX queries and leverage date-based relationships without ambiguity.

Answer

Configure bidirectional cross-filtering between all dimension tables and the fact table.

Answer

Use multiple inactive relationships between fact and dimension tables to support different analyses.

Answer

Use many-to-many relationships between dimension tables and the fact table to simplify the model.

Question 4

A Power BI developer is building a data model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, ShipDate, SalesAmount. The developer wants to analyze orders by both order date and ship date. Which THREE actions should the developer take to properly model this scenario?

Accepted Answer

Use the USERELATIONSHIP function in DAX measures to specify which relationship to use for a given calculation.. The USERELATIONSHIP function in DAX allows the developer to temporarily activate an inactive relationship for a specific calculation. In this scenario, one relationship (e.g., between Orders[OrderDate] and a date table) is active, while the other (e.g., between Orders[ShipDate] and the same or another date table) is inactive. By using USERELATIONSHIP in measures, the developer can specify which relationship to use for filtering or aggregation, enabling analysis by both order date and ship date without altering the model's default behavior.

Answer

Merge the OrderDate and ShipDate columns into a single column and use a flag to differentiate.

Answer

Use a single date table with an active relationship to OrderDate and an inactive relationship to ShipDate.

Question 5

A Power BI developer is troubleshooting a report that uses a calculated table. The calculated table is defined as: 'Sales Summary = SUMMARIZE(Sales, Sales[ProductID], "Total Sales", SUM(Sales[Amount]))'. Users report that the 'Total Sales' column shows incorrect values when slicers are applied to the report. What is the most likely cause?

Accepted Answer

Calculated tables are static and do not respond to slicer selections.. Calculated tables in Power BI are evaluated at data refresh time and stored in the model as static data. They do not respond to slicer selections or any other report-level filters because they are not recalculated in the query context. Therefore, the 'Total Sales' column in the 'Sales Summary' table will always show the same aggregated values regardless of slicer interactions, which is why users see incorrect values when applying slicers.

Answer

The calculated table lacks a relationship to the Sales table.

Answer

The SUMMARIZE function syntax is incorrect.

Answer

The calculated table is not marked as a date table.

Question 6

A data modeler is creating a Power BI semantic model for a retail company. The model includes a 'Products' dimension table with columns ProductID, ProductName, Category, and Subcategory. The 'Sales' fact table has columns ProductID, Date, Quantity, and Revenue. The modeler wants to ensure that users can filter by Category and Subcategory. Which relationship type should be created between Products and Sales?

Accepted Answer

One-to-many (Products to Sales). In a Power BI semantic model, a one-to-many relationship from Products (the dimension table) to Sales (the fact table) is the standard star schema design. This allows users to filter Sales data by any attribute in Products, such as Category and Subcategory, because each ProductID in Products is unique (the 'one' side) and can be associated with many rows in Sales (the 'many' side). This relationship type ensures proper cross-filtering and aggregation behavior.

Answer

Many-to-one (Sales to Products)

Answer

Many-to-many

Answer

One-to-one

Question 7

A Power BI developer is designing a semantic model that will be used by multiple departments. The developer wants to ensure that the model follows best practices for performance and usability. Which TWO actions should the developer take?

Accepted Answer

Use a star schema with dimension and fact tables.. A star schema organizes data into dimension and fact tables, reducing data redundancy and improving query performance by enabling efficient aggregations and filter propagation. This design aligns with Power BI best practices for semantic models, as it minimizes the number of tables and relationships, leading to faster DAX calculations and better compression.

Answer

Use calculated columns instead of measures for aggregations.

Answer

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

Answer

Enable bidirectional cross-filtering between all tables.

Question 8

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.. The GROUP BY in Power Query pre-aggregates the data before loading. Once aggregated, report-level filters (like OrderDate) cannot re-aggregate the underlying detail since it no longer exists in the model. Option A is wrong: TotalDue being numeric is fine; the issue is aggregation level, not data type. Option C is wrong: refresh status is irrelevant to filtering behavior. Option D is wrong: the Date table relationship would matter if detail rows existed, but here the data is already aggregated, so relationship may be unnecessary or broken due to aggregated granularity.

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 9

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

Remove the RELATED function and merge tables in the source query.. In DirectQuery, calculated columns are evaluated row-by-row at query time, causing slow performance. Calculated tables are not supported in DirectQuery, so D is not a valid option. The best design change is to remove the calculated column and merge the tables at the data source (e.g., using a view or SQL join) so the related value is available directly without per-row lookups. This preserves functionality and avoids the performance penalty without changing the storage mode.

Answer

Convert the model to Import mode.

Answer

Use a measure instead of a calculated column.

Answer

Replace the calculated column with a calculated table that includes the related column.

Question 10

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.. 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 11

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 create inactive relationships for ShipDate and DueDate. Use USERELATIONSHIP in DAX measures.. The recommended approach is to use a single date dimension table with inactive relationships for ShipDate and DueDate, and use USERELATIONSHIP in DAX measures to activate the appropriate relationship. This method is efficient, maintainable, and supports fiscal year analysis by adding calculated columns (e.g., FiscalYear) to the date table. Option A's use of CALCULATE with FILTER is less efficient and can lead to complex filtering logic, whereas USERELATIONSHIP leverages the model relationships directly.

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.

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.

Question 12

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.. 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 13

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.. In Power BI, an active one-to-many relationship from a lookup table (Date) to a fact table (Sales) propagates filters from the lookup table to the fact table by default. Marking the Date table as a date table is only required for time intelligence functions, not for basic filtering. Bidirectional cross-filtering is not needed for this scenario. Therefore, the measure should correctly change when slicing by month. The most likely cause of the total not changing is that the relationship is either inactive, has cross-filter direction set to 'None', or the Date table is not actually related to Sales as specified. Since none of the options describe this, all options are incorrect.

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 14

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.. The correct approach is to create separate dimension tables for Origin and Destination, each with an active relationship to Shipments, and a disconnected AllCities table for the slicer. Measures should use FILTER/OR logic (or TREATAS) to check for matches in either origin or destination, not USERELATIONSHIP.

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 15

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.. A separate, marked date table is required for time intelligence functions and proper date hierarchies. Additionally, connecting the Sales fact table to Product and Store dimensions with many-to-one relationships creates a star schema that supports filtering and grouping by product and store attributes. Both actions are necessary for the required reporting.

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.