PL-300 Model the data — Timed 10 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?

10 minute time limit — choose an answer to begin.

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.