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

Question 1

Which TWO of the following are true about calculated columns in Power BI?

Accepted Answer

Calculated columns are computed using DAX formulas.. Calculated columns in Power BI are defined using Data Analysis Expressions (DAX), a formula language specifically designed for data modeling and analysis. These DAX formulas are evaluated row by row during the data refresh process, not at query time, and the results are physically stored in the model.

Answer

Calculated columns cannot be used in relationships.

Answer

Calculated columns are evaluated at query time.

Answer

Calculated columns are stored on disk and loaded on demand.

Question 2

Refer to the exhibit. You apply the above RLS rule to a semantic model. The rule is intended to restrict sales data by the user's region, which is stored in the user's email domain (e.g., user@west.contoso.com). However, the rule does not filter any rows. What is the most likely issue?

Accepted Answer

The filter expression compares the full UPN to the Region column, which likely contains only the region name.. The USERPRINCIPALNAME() function returns the full user principal name (e.g., user@west.contoso.com), including the domain. If the Region column contains only the region name (e.g., 'West'), the comparison fails because it compares the entire UPN to 'West'. To fix this, you would need to extract the subdomain or use a different approach. Option A is incorrect because the Sales table may have a Region column; the issue is the comparison logic, not the existence of the column. Option B is incorrect because USERPRINCIPALNAME() is available in Power BI for RLS rules. Option D is incorrect because the rule is applied but not filtering as intended due to the mismatch.

Answer

The Sales table does not have a Region column.

Answer

USERPRINCIPALNAME() is not available in the current Power BI version.

Answer

The RLS rule is not applied to the semantic model.

Question 3

You have a Power BI model with a large fact table (100 million rows) and several dimension tables. You need to improve query performance for a report that uses a date slicer. The report currently takes 10 seconds to load. What is the most effective optimization?

Accepted Answer

Create an aggregated table that summarizes data at the month level.. Creating an aggregated table that summarizes data at the month level (Option D) reduces the number of rows processed by the report. Since the report uses a date slicer, the user likely queries at day-level granularity, but the aggregation can be used when monthly totals suffice, or the report can use the detailed table only when needed. This is a common and effective optimization for large fact tables in Power BI, as it directly reduces the data load and query processing time. Partitioning (Option A) primarily improves refresh and load performance, not query response, and is less effective for a single date slicer. Removing columns (B) helps but is not as impactful as aggregation. Increasing capacity (C) is costly and does not address the root cause.

Answer

Partition the fact table by date range.

Answer

Remove unused columns from the fact table.

Answer

Increase the Power BI service capacity.

Question 4

You need to create a calculated column that categorizes sales amounts into 'Low', 'Medium', and 'High' based on thresholds. Which DAX expression should you use?

Accepted Answer

SWITCH(TRUE(), Sales[Amount] < 100, "Low", Sales[Amount] < 500, "Medium", "High"). It uses SWITCH(TRUE(), ...) to evaluate multiple conditions sequentially, returning the first matching result. This is the standard pattern for categorizing continuous values into discrete buckets in DAX, as SWITCH evaluates conditions in order and stops at the first TRUE condition.

Answer

IF(Sales[Amount] < 100, "Low", IF(Sales[Amount] < 500, "Medium", "High"))

Answer

SELECTEDVALUE(Sales[Amount]) & "Category"

Answer

SWITCH(Sales[Amount], 100, "Low", 500, "Medium", "High")

Question 5

You have a star schema with a Sales fact table and a Date dimension. You need to calculate year-to-date sales that reset each fiscal year starting July 1. Which measure is correct?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "6-30")). The correct measure is CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "6-30")). DATESYTD returns the set of dates from the start of the fiscal year (July 1) to the last date in the current filter context, and CALCULATE evaluates SUM in that context. TOTALYTD(..., ALL('Date'), "6-30") incorrectly strips all filters on the Date table, so the YTD would not advance with the selected date. Additionally, the ALL('Date') argument is not required for a simple YTD calculation and actually contradicts the goal of computing a dynamic YTD.

Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], "6-30")

Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], ALL('Date'), "6-30")

Question 6

You have a table with columns 'Date', 'Sales', and 'Quantity'. You need to create a measure that calculates the average sales per transaction. Which DAX measure should you use?

Accepted Answer

AVERAGE(Sales[Sales]). The AVERAGE function in DAX calculates the arithmetic mean of a column's numeric values. Since the question asks for 'average sales per transaction,' you need the average of the 'Sales' column, which represents the sales amount per transaction. AVERAGE(Sales[Sales]) directly computes this mean.

Answer

AVERAGE(Sales[Quantity])

Answer

DIVIDE(SUM(Sales[Sales]), SUM(Sales[Quantity]))

Answer

SUM(Sales[Sales])

Question 7

You are building a Power BI model with a fact table and multiple dimension tables. You want to enforce a rule that each fact row must have a matching row in a related dimension. Which modeling technique should you use?

Accepted Answer

Use a calculated column to check for existence.. To enforce that every fact row has a matching dimension row, you can use a calculated column to check for the existence of a corresponding value in the dimension table. For example, using LOOKUPVALUE or a related function to return a value only if a match exists. This allows you to filter out or flag rows without matches. Option D is incorrect because assuming referential integrity does not enforce the rule; it only tells Power BI to trust that data is already clean, but does not validate or prevent unmatched rows.

Answer

Set the relationship cardinality to many-to-many.

Answer

Set the cross-filter direction to both.

Answer

Assume referential integrity in the relationship settings.

Question 8

Refer to the exhibit. You have a Sales table. You create a measure: TotalSales = SUM(Sales[SalesAmount]). You then create another measure: SalesAboveAverage = CALCULATE([TotalSales], FILTER(Sales, Sales[SalesAmount] > AVERAGE(Sales[SalesAmount]))). What is the value of SalesAboveAverage?

Accepted Answer

550. The average of SalesAmount is (100+200+300+150+250)/5 = 200. The rows with SalesAmount > 200 are: 300 and 250. Sum = 550. Option A is wrong because it includes all rows (1000). Option B is wrong because it is an incorrect sum, not the sum of values above average. Option C is wrong because it sums values less than or equal to 200. Option D is correct.

Answer

1000

Answer

800

Answer

450

Question 9

You have two tables: 'Orders' and 'Customers'. You want to create a relationship where each order is linked to one customer, but a customer can have many orders. Which cardinality should you choose?

Accepted Answer

Many-to-one (*:1) from Orders to Customers. The correct cardinality is Many-to-one (*:1) from Orders to Customers. This means each order is linked to one customer, and a customer can have many orders. Option A (Many-to-many) is incorrect because it would allow an order to have multiple customers, which is not the requirement. Option C (One-to-many from Orders to Customers) reverses the direction; it would mean one order can have many customers, which is not intended. Option D (One-to-one) would restrict each customer to only one order, which is not the case here. Therefore, option B is correct.

Answer

Many-to-many (*:*)

Answer

One-to-many (1:*) from Orders to Customers

Answer

One-to-one (1:1)

Question 10

Refer to the exhibit. A user is a member of both 'Manager' and 'Executive' roles. Which rows will the user see?

Accepted Answer

Rows where Department is 'Sales' or 'Marketing'.. When a user is a member of multiple row-level security roles, the filters are combined with OR logic. The Manager role filters to Department = 'Sales', and the Executive role filters to Department IN ('Sales', 'Marketing'). Therefore, the user sees rows where Department is either 'Sales' or 'Marketing'. Option A is incorrect because it only shows Sales, ignoring the Executive role. Option C is incorrect because role membership does not override filters; instead, filters are combined with OR, so not all rows are shown. Option D is incorrect because it states that the user sees only Sales, but with both roles the user also sees Marketing, so D is wrong.

Answer

Only rows where Department is 'Sales'.

Answer

All rows, because role membership overrides filters.

Answer

Rows where Department is 'Sales' only, but with both role permissions.

Question 11

You have a table with columns 'Product', 'Category', and 'Sales'. You want to create a hierarchy that allows users to drill down from Category to Product. Which is the correct order to create the hierarchy?

Accepted Answer

Category -> Product. In Power BI, a hierarchy is built from the highest (most aggregated) level to the lowest (most granular) level. To allow drill-down from Category to Product, Category must be the top level and Product the child level. Option C correctly orders Category → Product, enabling users to expand from category-level totals to individual product sales.

Answer

Product -> Category -> Sales

Answer

Product -> Category

Answer

Sales -> Product -> Category

Question 12

You are modeling data for a retail company. You have a 'Transactions' fact table with millions of rows. You need to improve query performance for a report that filters by 'Store' and 'Date'. Which action will have the most impact?

Accepted Answer

Ensure the date table is marked as a date table and has a continuous date range.. Marking the date table as a date table and ensuring a continuous date range enables optimized time intelligence functions and improves query performance. A composite model does not improve performance for this specific filter; it may add latency. Removing the date table would break time intelligence capabilities. Merging tables increases table size and may degrade performance.

Answer

Merge the 'Store' and 'Transactions' tables into one.

Answer

Use a composite model with DirectQuery for the fact table.

Answer

Remove the date table and use the date column directly in the fact table.

Question 13

You are designing a star schema for a sales data model. Which table should be defined as a dimension table?

Accepted Answer

Date. Date is a classic dimension in a star schema, used for time-based analysis and filtering. Option A (TransactionID) is typically a fact table key or a degenerate dimension, not a dimension table. Option B (OrderQuantity) is a measure, not a dimension. Option C (SalesAmount) is also a measure.

Answer

TransactionID

Answer

OrderQuantity

Answer

SalesAmount

Question 14

You have a Power BI model with a fact table 'Sales' and dimensions 'Customer', 'Product', 'Date'. You need to enforce that every fact row has a valid CustomerKey. Which approach enforces referential integrity in Power BI?

Accepted Answer

Assume referential integrity. Power BI's 'Assume referential integrity' setting, when enabled on a relationship, instructs the engine to use more efficient INNER JOIN queries by trusting that the relationship integrity holds. It does not actively enforce referential integrity or prevent orphan records; instead, it relies on clean, conformed data to optimize performance. This is the closest option to the scenario, but it technically assumes integrity rather than enforcing it. Option A is wrong because merging tables restructures the model and eliminates the star schema. Option B is wrong because hiding columns has no effect on integrity. Option C is wrong because merely assuming validity is not a Power BI feature and offers no optimization or enforcement.

Answer

Merge Sales and Customer tables into a single table

Answer

Hide CustomerKey columns in both tables

Answer

Assume all CustomerKey values are valid

Question 15

You need to create a calculated column that categorizes products as 'Low', 'Medium', or 'High' based on the 'UnitPrice' column. Which DAX function should you use?

Accepted Answer

SWITCH. (SWITCH) is correct because SWITCH evaluates a list of conditions in order and returns the corresponding value for the first true condition, making it ideal for categorizing numeric ranges like 'UnitPrice' into 'Low', 'Medium', or 'High'. Option A (CALCULATE) modifies filter context and is not for direct categorization. Option B (SUM) is an aggregation function. Option C (IF) can handle multiple conditions but requires nested IF statements, which are less readable and efficient than SWITCH.

Answer

CALCULATE

Answer

SUM

Answer

IF