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

Question 1

You have a data model with a Sales table and a Date table. You create a measure: Total Sales = SUM(Sales[Amount]). When you add a slicer for Date[Year], the measure does not filter correctly. What is the most likely cause?

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

Accepted Answer

The relationship is inactive. The most likely cause is that the relationship between the Sales table and the Date table is inactive. In Power BI, only one active relationship can exist between two tables; any additional relationships must be inactive. A slicer on Date[Year] will only filter through the active relationship. If the active relationship is not the one intended for the measure, the slicer will not affect the measure's result. Using USERELATIONSHIP in the measure or setting the correct relationship as active would resolve this.

Answer

The Date table is not marked as a date table

Answer

The Sales table has no relationship to the Date table

Answer

The measure uses SUM instead of SUMX

Question 2

You need to create a hierarchy in Power BI that allows drilling down from Year to Quarter to Month. What is the correct approach?

Accepted Answer

Create a hierarchy in the Date table using Year, Quarter, Month columns. The correct option is D: create a hierarchy in the Date table using the Year, Quarter, and Month columns. In Power BI, a hierarchy is built by dragging fields onto each other in the Fields pane, so placing Year above Quarter above Month in a date table produces exactly the Year → Quarter → Month drill-down path required. The other options do not fit: A (separate measures) produces independent calculations, not a drillable hierarchy; B (Drillthrough) navigates to a separate report page filtered by a selected value, not down levels within a visual; and C (DATESYTD) is a time-intelligence function that returns year-to-date totals, not a hierarchy.

Answer

Create separate measures for each level

Answer

Use the Drillthrough feature

Answer

Use the DATESYTD function

Question 3

You have a Power BI data model with a table that contains duplicate rows. You want to remove duplicates in Power Query. Which transformation should you use?

Accepted Answer

Remove Duplicates. The correct option is B, Remove Duplicates, because it is the Power Query transformation specifically designed to eliminate duplicate rows from a table by comparing all columns (or selected columns) and keeping only the first occurrence of each unique row. In this scenario, the table contains duplicate rows, so applying Remove Duplicates directly addresses the requirement without altering the underlying data values. Replace Values (A) only substitutes specific text or numbers and does not remove rows. Filter Rows (C) excludes rows based on conditions but cannot identify duplicates across all columns. Group By (D) aggregates rows and would change the table structure rather than simply removing duplicate rows.

Answer

Replace Values

Answer

Filter Rows

Answer

Group By

Question 4

Which TWO of the following are valid DAX functions for time intelligence? (Select two.)

Accepted Answer

DATEADD. DATEADD is a valid DAX time intelligence function that shifts dates forward or backward by a specified number of intervals (days, months, quarters, years). TOTALYTD is also a valid time intelligence function that calculates the year-to-date value of an expression. Both are part of the dedicated time intelligence function set in DAX, which requires a properly marked date table with continuous dates.

Answer

RANKX

Answer

CONCATENATEX

Answer

MINX

Question 5

Which THREE of the following are benefits of using a star schema in Power BI? (Select three.)

Accepted Answer

Simplified DAX formulas. Option A (Simplified DAX formulas) is correct because a star schema separates quantitative facts from descriptive dimensions, so measures typically aggregate a single fact table and filter through one-to-many relationships, avoiding complex bidirectional or multi-table filter logic in CALCULATE and RELATED calls. Option C (Improved query performance) is correct because the Power BI engine (VertiPaq) compresses and scans narrow dimension tables and a single fact table efficiently, and one-to-many relationships let the engine use simpler, faster join paths than snowflaked or normalized models. Option E (Easier for business users to understand) is correct because dimensions map to familiar business entities (Customer, Product, Date) and facts to measurable events, making the model intuitive for self-service report authors. Option B is not correct because star schemas rely on one-to-many relationships; many-to-many relationships are not native to the star design and require bridge tables or special handling. Option D is not correct because increased data redundancy is a drawback of denormalization, not a benefit—star schemas actually reduce redundancy compared with fully normalized models while accepting controlled duplication in dimensions.

Answer

Supports many-to-many relationships natively

Answer

Increased data redundancy

Question 6

You have a Power BI data model with a Sales table and a Product table. You want to create a measure that calculates the percentage of total sales for each product category. Which DAX pattern should you use?

Accepted Answer

DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category]))). It uses DIVIDE for safe division and CALCULATE with ALL(Product[Category]) to remove the filter context on the Product[Category] column, allowing the measure to compute the percentage of total sales for each product category. This pattern ensures that the denominator represents the total sales across all categories, while the numerator respects the current filter context for the specific category.

Answer

SUM(Sales[Amount]) / CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales))

Answer

SUM(Sales[Amount]) / SUM(Sales[Amount])

Answer

DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED()))

Question 7

You have a Power BI model with a table named 'Orders' that contains columns: OrderID, CustomerID, OrderDate, and TotalAmount. You need to create a measure that calculates the total sales amount for orders placed in the last 30 days, but only for customers who have placed more than 5 orders in total. What is the most efficient DAX measure?

Accepted Answer

TotalSalesLast30Days = SUMX(FILTER(Orders, Orders[OrderDate] > TODAY() - 30 && CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5), Orders[TotalAmount]). It iterates over filtered rows where both conditions are met using a single FILTER and SUMX, which is syntactically valid and more efficient than multiple FILTER iterators. Option B is invalid because KEEPFILTERS expects a filter expression, not a scalar boolean result from CALCULATE(...) > 5.

Answer

TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), FILTER(Orders, Orders[OrderDate] > TODAY() - 30), FILTER(Orders, CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5))

Answer

TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), KEEPFILTERS(Orders[OrderDate] > TODAY() - 30), KEEPFILTERS(CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5))

Answer

TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), DATESINPERIOD(Orders[OrderDate], TODAY(), -30, DAY))

Question 8

You are designing a data model for a report that shows sales by region and product category. The source data includes a table 'Sales' with columns: Region, Category, SalesAmount. You also have separate tables 'Regions' and 'Categories' that contain additional attributes. You need to create a star schema. What should you do with the 'Region' and 'Category' columns in the 'Sales' table?

Accepted Answer

Remove them from the Sales table and use foreign keys to link to the dimension tables. In a star schema, dimension tables (Regions, Categories) contain descriptive attributes, and the fact table (Sales) stores foreign keys referencing those dimensions. Removing the Region and Category columns from the Sales table and replacing them with foreign keys (e.g., RegionID, CategoryID) normalizes the model, reduces data redundancy, and enables efficient filtering and slicing by region and category attributes. This approach aligns with best practices for Power BI data modeling, ensuring optimal query performance and maintainability.

Answer

Keep them in the Sales table as attributes for simplicity

Answer

Merge the Regions and Categories tables into the Sales table

Answer

Mark the Sales table as a date table

Question 9

Which THREE considerations are important when implementing row-level security (RLS) in Power BI? (Select exactly 3.)

Accepted Answer

Roles can use DAX expressions to define filters. Option A is correct because RLS roles in Power BI define filters using DAX expressions (for example, [Region] = "West" or USERPRINCIPALNAME()), which is the core mechanism for row-level filtering. Option B is correct because RLS is designed to filter data based on the user's identity, typically via functions like USERNAME() or USERPRINCIPALNAME() mapped to a security table. Option D is correct because in DirectQuery mode, RLS filters are translated and pushed down to the source database as part of the query, so the source must be able to process them. Option C is not correct because RLS is not automatically applied in Analyze in Excel; the connection must use a role or the user must be a member of a role, and behavior depends on the connection method. Option E is not correct because RLS filters rows, not measures; restricting access to specific measures is handled through object-level security (OLS), not RLS.

Answer

RLS is automatically applied when using Analyze in Excel

Answer

RLS can restrict access to specific measures

Question 10

Which THREE are best practices for managing relationships in Power BI? (Select exactly 3.)

Accepted Answer

Hide foreign key columns in dimension tables to prevent misuse. Option B is correct because hiding foreign key columns in dimension tables keeps the model clean and prevents report authors from accidentally using these technical keys in visuals instead of the proper dimension attributes. Option C is correct because single-direction cross-filtering is the default and preferred approach; bidirectional filtering should only be enabled when a specific requirement demands it, since it can introduce ambiguity, performance degradation, and unexpected results. Option E is correct because related columns must share the same data type for the relationship to be created and to filter correctly; mismatched types cause errors or force Power BI to create an inactive relationship. Option A is incorrect because many-to-many relationships should be avoided when possible, as they complicate the model, can produce ambiguous results, and are harder to optimize. Option D is incorrect because setting all relationships to bidirectional cross-filtering is not a best practice; it can create ambiguous filter paths, circular dependencies, and performance issues.

Answer

Use many-to-many relationships whenever possible to simplify the model

Answer

Always set cross-filter direction to both to allow full interactivity

Question 11

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. A customer dimension should contain the customer's unique key (CustomerID) plus descriptive attributes that describe the customer, such as CustomerName, City, and Region, which are appropriate for slicing and filtering sales facts. In a star schema, the dimension holds only attributes about the entity, while transactional and numeric fields stay in the fact table. Options A and B incorrectly include ProductID and OrderID, which are keys belonging to other dimensions or the fact table, not customer attributes. Option C incorrectly includes OrderDate, which is a date attribute that belongs in a separate Date dimension, not the Customer dimension.

Answer

CustomerID, CustomerName, ProductID

Answer

CustomerID, CustomerName, OrderID

Answer

CustomerID, CustomerName, OrderDate

Question 12

You need to create a calculated column in Power BI that categorizes sales amounts as 'Low', 'Medium', or 'High' based on the value. The column should be evaluated row by row. Which DAX function should you use?

Accepted Answer

SWITCH. SWITCH is the correct DAX function because it evaluates an expression (the sales amount) against a series of conditions and returns a corresponding result for each condition. In this scenario, you can use SWITCH(TRUE(), [Sales] < 1000, 'Low', [Sales] < 5000, 'Medium', 'High') to create a calculated column that categorizes values row by row. SWITCH is designed for multiple conditional branches, making it the most efficient and readable choice for this three-tier categorization.

Answer

IF

Answer

CALCULATE

Answer

FORMAT

Question 13

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 correct option is B, DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY), because the DATEDIFF function in DAX returns the count of interval boundaries crossed between two dates, and specifying DAY gives exactly the number of days between OrderDate and ShipDate for each row. This matches the requirement of a calculated column computing the shipping duration per order. Option A, YEAR(Orders[OrderDate]), only extracts the year from OrderDate and does not compare the two dates. Option C, ENDOFMONTH(Orders[OrderDate]), returns the last date of the month for OrderDate, which is unrelated to the interval between OrderDate and ShipDate. Option D, DATEADD(Orders[OrderDate], 1, DAY), shifts OrderDate forward by one day rather than calculating the difference between the two columns.

Answer

YEAR(Orders[OrderDate])

Answer

ENDOFMONTH(Orders[OrderDate])

Answer

DATEADD(Orders[OrderDate], 1, DAY)

Question 14

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.. Option A is correct because a calculated table is created with DAX table expressions (for example, SUMMARIZE, SUMMARIZECOLUMNS, or DISTINCT) and can materialize a new summary table that does not exist in the source system, which is exactly what calculated tables are designed for. Option C is correct because a calculated table can be generated with CALENDAR or CALENDARAUTO to produce a continuous, unbroken date range, which is the standard way to build a dedicated date table for time intelligence functions like TOTALYTD or SAMEPERIODLASTYEAR. Option B is not correct because calculated tables are computed at refresh and stored in the model, so they do not inherently reduce model size by storing only aggregated data; aggregation reduction is achieved through Import mode aggregations, DirectQuery, or composite models, not by the mere choice of a calculated table. Option D is not correct because a column that depends on other columns in the same row is precisely the definition of a calculated column, which is evaluated row by row in the table, whereas a calculated table produces a whole table and cannot serve that row-level purpose.

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 15

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.. Option A is correct because hiding unused columns reduces the model's exposed surface and prevents report authors from accidentally dragging unnecessary fields into visuals, which keeps the VertiPaq engine from scanning and materializing columns that add no analytical value. Option B is correct because bi-directional cross-filtering forces the engine to propagate filter context in both directions, which can create ambiguous filter paths, increase query complexity, and degrade performance; single-direction relationships should be the default unless a specific requirement demands otherwise. Option C is correct because a star schema with dimension and fact tables minimizes relationship hops, keeps filter propagation simple, and lets the VertiPaq engine compress and scan narrow fact tables efficiently, which is the recommended modeling pattern for Power BI performance. Option D is not correct because many-to-many relationships without bridge tables introduce ambiguity and expensive filter propagation; the best practice is to resolve many-to-many with a bridge table and single-direction relationships. Option E is not correct because calculated columns are computed at refresh time and stored in the model, consuming memory and increasing refresh cost, whereas measures are evaluated at query time and are the preferred approach for aggregations in a performant model.

Answer

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

Answer

Use calculated columns instead of measures for aggregations.