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

Question 1

Which TWO of the following are valid reasons to create a separate date table in a Power BI semantic model? (Select two.)

Accepted Answer

To enable time intelligence functions like TOTALYTD.. Option B is correct because time intelligence functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD require a dedicated date table marked as a date table with a contiguous, unbroken date column; without it these functions return incorrect or blank results. Option C is correct because a dedicated date table provides a clean, reusable calendar hierarchy (Year > Quarter > Month > Day) that can be used consistently across all fact tables for slicing and filtering, rather than relying on auto-generated date hierarchies in each fact table. Option A is not the reason to create a separate date table, since date arithmetic can be performed with DAX functions like DATEDIFF or DATEADD on existing date columns without a dedicated table. Option D is incorrect because many-to-many relationships between fact tables are handled by bridge tables or relationship configuration, not by a date table. Option E is incorrect because a date table can only have one active relationship to a given fact table; additional relationships must be inactive and activated via USERELATIONSHIP, so a separate date table does not enable multiple active relationships.

Answer

To calculate measures that require date arithmetic.

Answer

To create many-to-many relationships between fact tables.

Answer

To allow multiple active relationships from the same fact table to the date table.

Question 2

Which THREE of the following are valid ways to model a many-to-many relationship between tables in Power BI? (Select three.)

Accepted Answer

Add a calculated column to one table to create a unique key.. Adding a calculated column to create a unique key is a common technique used in bridge tables to resolve many-to-many relationships. Option D is correct because a bridge table with many-to-many cardinality directly models the relationship. Option E is correct because setting cross filter direction to Both with Many-to-Many cardinality is a built-in feature in Power BI. Option A is incorrect because LOOKUPVALUE is a scalar function that returns a single value, not a modeling method for relationships. Option C is incorrect because creating two one-to-many relationships with USERELATIONSHIP is used for handling multiple active relationships between the same two tables, not for many-to-many scenarios.

Answer

Use the LOOKUPVALUE function in a measure.

Answer

Create two one-to-many relationships and use USERELATIONSHIP in measures.

Question 3

Which THREE of the following are considerations when implementing row-level security (RLS) in Power BI? (Select three.)

Accepted Answer

RLS does not apply to data accessed via the XMLA endpoint unless using dynamic security.. Option A is correct because connections made through the XMLA endpoint (for example, by tools like Excel, SSMS, or Tabular Editor) bypass RLS unless dynamic security is implemented, since such clients connect as a service principal or admin rather than as an interactive user identity. Option B is correct because RLS in Power BI is not a static data truncation; the DAX filter expressions are evaluated and applied at query time for each user request, so the same dataset returns different rows depending on the effective identity. Option C is correct because row-level security roles are defined with DAX filter expressions (for example, [Region] = "West" or USERPRINCIPALNAME() = [UserEmail]) on tables in the semantic model. Option D is not correct because RLS applies to any user assigned to a role, including Admin, Member, and Contributor roles, not only Viewers. Option E is not correct because there is no 'Show all' option in visuals that bypasses RLS; RLS filters are enforced by the engine and cannot be overridden from report visuals.

Answer

RLS is enforced only for users with the Viewer role.

Answer

RLS can be bypassed by using the 'Show all' option in visuals.

Question 4

Which TWO of the following are valid DAX functions for modifying filter context within a measure? (Select two.)

Accepted Answer

CALCULATE. Options B (CALCULATE) and E (ALL) are correct. CALCULATE is the primary function used to modify filter context within a measure. It evaluates an expression in a modified filter context. ALL is a filter modifier that removes filters from a table or column, thereby altering the filter context when used as a filter argument inside CALCULATE. Option A (FILTER) is a table function that returns a filtered table; it does not directly modify filter context but is used as an argument. Option C (VALUES) returns a single-column table of unique values and does not modify filter context. Option D (SUM) is an aggregation function and does not affect filter context.

Answer

FILTER

Answer

VALUES

Answer

SUM

Question 5

You are designing a data model for a sales analysis report. The source data includes a table named 'Sales' with columns: OrderDate, CustomerID, ProductID, Quantity, and SalesAmount. You need to create a date table to support time intelligence calculations. Which approach should you use?

Accepted Answer

Create a date table using CALENDAR function with start date = MIN(Sales[OrderDate]) and end date = MAX(Sales[OrderDate]). Using CALENDAR with MIN(Sales[OrderDate]) as start and MAX(Sales[OrderDate]) as end generates a continuous date range covering all sales dates. After marking this table as a date table using the 'Mark as Date Table' feature, DAX time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR work correctly. Option A is incorrect because it requires manually specifying start and end dates, which may include unnecessary dates outside the sales period, leading to extra data and potential performance issues. Options B and C are incorrect as explained.

Answer

Create a date table using CALENDAR function with a continuous date range covering all possible dates, then use Mark as Date Table in the model view

Answer

Use the CALENDARAUTO function to automatically generate dates and rely on auto date/time

Answer

Use the DISTINCT function on Sales[OrderDate] to create a date table

Question 6

You have a Power BI data model that contains a table 'Sales' with columns: Date, ProductID, and Revenue. You also have a 'Products' table with columns: ProductID, ProductName, and Category. You want to create a measure that calculates total revenue for the current year. Which DAX expression should you use?

Accepted Answer

TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date]). TOTALYTD is a time intelligence function that calculates the year-to-date total of an expression, given a date column. It requires a date table marked as a date table for proper functionality. Option A is incorrect because while DATESYTD can be used directly as a filter in CALCULATE, it is less straightforward and not the optimal choice; TOTALYTD is specifically designed for this task. Option B is incorrect: SUMX with a logical comparison does not filter the date context; it multiplies revenue by 1 or 0 per row, which does not produce a correct YTD total. Option C could technically work using FILTER, but it is less efficient and not the recommended time-intelligence approach; TOTALYTD is purpose-built for this task.

Answer

CALCULATE(SUM(Sales[Revenue]), DATESYTD('Date'[Date]))

Answer

SUMX(Sales, Sales[Revenue] * (YEAR(Sales[Date]) = YEAR(TODAY())))

Answer

CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY())))

Question 7

You have a Power BI model with a table 'Sales' that contains columns: Date, SalespersonID, and Amount. You have a 'Salespeople' table with columns: SalespersonID, Name, and Region. You need to create a measure that calculates the total sales amount for the current region, but only for salespeople who have made at least one sale in the current month. Which DAX expression achieves this?

Accepted Answer

SUMX(FILTER(Salespeople, CALCULATE(COUNTROWS(Sales), Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) > 0), CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]))). The correct DAX expression must filter salespeople to those who have at least one sale in the current month, then calculate total sales for the current region. Option B uses SUMX with FILTER to iterate over Salespeople, checks if the count of Sales rows for that salesperson in the current month is greater than zero, and then calculates the sum of Sales[Amount] for the current region. This correctly applies both conditions: salespeople with at least one sale this month and the selected region.

Answer

CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]))

Answer

SUMX(Salespeople, CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]), Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))

Answer

CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]), Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Question 8

You are creating a Power BI report that requires a many-to-many relationship between 'Product' and 'Customer' tables. What is the recommended way to implement this?

Accepted Answer

Create a bridge table with ProductID and CustomerID and create relationships to both tables. In Power BI, a many-to-many relationship between two tables (e.g., Product and Customer) is best implemented using a bridge table that contains the foreign keys (ProductID and CustomerID) from both tables. This bridge table resolves the many-to-many cardinality by creating two one-to-many relationships: one from Product to the bridge table and one from Customer to the bridge table. This approach ensures proper filtering and avoids ambiguity in the data model, which is a standard best practice in dimensional modeling.

Answer

Combine Product and Customer into a single table

Answer

Create a direct many-to-many relationship using the 'many-to-many' cardinality option in Power BI

Answer

Use a calculated column to concatenate IDs

Question 9

You have a Power BI model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, and TotalAmount. You have a 'Customers' table with columns: CustomerID, CustomerName, and Country. You need to create a measure that calculates the total sales amount for customers who have placed orders in the last 30 days. Which DAX measure should you use?

Accepted Answer

CALCULATE(SUM(Orders[TotalAmount]), Orders[OrderDate] >= TODAY() - 30). CALCULATE modifies the filter context of SUM(Orders[TotalAmount]) with a simple boolean filter predicate on Orders[OrderDate] >= TODAY() - 30, which is valid DAX syntax and returns total sales for orders in the last 30 days. Option A uses SUMX with FILTER, which is valid but is a row-iteration pattern rather than the CALCULATE-based measure requested, and it does not leverage filter context modification. Option C is invalid because it attempts to reference Orders[OrderDate] inside a FILTER over Customers, mixing tables incorrectly and producing a filter that cannot be evaluated as written. Option D uses ALL(Orders), which removes existing filters on Orders and then applies the date predicate, but ALL is unnecessary and can produce incorrect results by stripping other relevant filters on the Orders table.

Answer

SUMX(FILTER(Orders, Orders[OrderDate] >= TODAY() - 30), Orders[TotalAmount])

Answer

CALCULATE(SUM(Orders[TotalAmount]), FILTER(Customers, Customers[CustomerID] IN VALUES(Orders[CustomerID]) && Orders[OrderDate] >= TODAY() - 30))

Answer

CALCULATE(SUM(Orders[TotalAmount]), ALL(Orders), Orders[OrderDate] >= TODAY() - 30)

Question 10

You have the above Power Query M script. What is the result of this transformation?

Accepted Answer

A table with one row per ProductID and columns: ProductID, TotalQty. The script filters rows where OrderDate >= Jan 1 2023, removes the ProductDescription column, and groups by ProductID summing Quantity. The result is a table with one row per ProductID and a TotalQty column. Option A is wrong because it does not retain all columns. Option B is wrong because it aggregates. Option C is wrong because it does not keep individual rows.

Answer

A table with one row per ProductID and columns: ProductID, Quantity

Answer

A table with all sales rows for 2023 and columns: ProductID, Quantity

Answer

A table with all sales rows for 2023 and columns: OrderDate, ProductID, Quantity, TotalQty

Question 11

Which TWO actions are valid for improving query performance in DirectQuery mode?

Accepted Answer

Create aggregated tables to pre-summarize data.. Option C is correct because creating aggregated tables in DirectQuery mode lets Power BI query pre-summarized data from the source instead of pushing complex aggregations down to the underlying database, dramatically reducing the volume of rows and computation required per query. Option E is correct because DirectQuery translates each visual into a SQL query against the source, so trimming the table to only the columns actually needed reduces the SELECT list, lowers data transfer, and helps the source engine use narrower indexes or columnstore scans. Option B is not valid here because converting to Import mode changes the storage mode entirely rather than improving performance within DirectQuery, and it is often impossible for very large or real-time sources. Option A is wrong because disabling relationship enforcement does not speed up joins and instead removes the model's ability to rely on referential integrity, which can degrade query plans. Option D is wrong because calculated columns are computed row-by-row and, in DirectQuery, are either pushed as expressions that prevent index use or force expensive per-row evaluation, so they typically hurt rather than help performance.

Answer

Disable relationship enforcement to speed up joins.

Answer

Convert the model to Import mode.

Answer

Use calculated columns in the fact table.

Question 12

Which THREE considerations are important when designing a date table for time intelligence?

Accepted Answer

The date table must be marked as a date table in Power BI.. Option A is correct because marking the table as a date table in Power BI (via Mark as Date Table) ensures that Power BI's built-in time intelligence functions, such as TOTALYTD and SAMEPERIODLASTYEAR, operate correctly and that the table's date column is treated as a proper date dimension. Option C is correct because the date table must be related to the fact table's date column; this relationship is what allows time intelligence calculations to filter fact data by the date dimension. Option D is correct because the date table must contain a contiguous range of dates with no gaps, since time intelligence functions rely on a complete, unbroken sequence of dates to compute periods correctly. Option B is not required because date columns in a date table can be regular columns or calculated columns; there is no rule that all date columns must be calculated. Option E is not required because a model can contain more than one date table, although only one can be marked as the date table per relationship, and having multiple date tables is allowed in certain designs.

Answer

All date columns must be calculated columns.

Answer

The model must have exactly one date table.

Question 13

You need to create a measure that calculates the average sales per transaction. The 'Sales' table has columns 'TransactionID' and 'Amount'. Which DAX expression is correct?

Accepted Answer

DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales)). Option B is correct because DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales)) computes the average sales per transaction by dividing total sales amount by the number of rows (transactions) in the Sales table, and DIVIDE safely handles division by zero by returning BLANK. In this scenario, each row represents a transaction, so COUNTROWS(Sales) gives the transaction count needed for the per-transaction average. Option A is wrong because DISTINCTCOUNT(Sales[TransactionID]) counts unique transaction IDs, which may not equal the number of transaction rows and could produce an incorrect denominator. Option C is wrong because AVERAGEX(Sales, Sales[Amount]) averages the Amount values row by row, which is equivalent to a simple average of Amount and does not explicitly divide total sales by transaction count. Option D is wrong because AVERAGE(Sales[Amount]) also computes the mean of the Amount column only, ignoring the transaction-count denominator logic.

Answer

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

Answer

AVERAGEX(Sales, Sales[Amount])

Answer

AVERAGE(Sales[Amount])

Question 14

Which THREE factors can cause a DAX measure to return BLANK unexpectedly?

Accepted Answer

Division by zero.. Division by zero in DAX returns BLANK (A). FILTER with a false condition returns an empty table; when used in an aggregate measure (e.g., within CALCULATE), the measure returns BLANK (C). Using BLANK in arithmetic operations, particularly multiplication or division, propagates BLANK (E). SUM ignores blanks, so it does not cause an unexpected BLANK unless all values are blank (B). Referencing a nonexistent column produces a DAX error, not BLANK (D).

Answer

Using SUM on a column with blanks.

Answer

Referencing a column name that does not exist.

Question 15

You have a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. You want to create a measure that shows the total number of orders. Which DAX function should you use?

Accepted Answer

COUNTROWS(Orders). COUNTROWS(Orders) is correct because it counts the number of rows in the Orders table, which directly represents the total number of orders regardless of any column values or blanks. DISTINCTCOUNT(Orders[OrderID]) would count unique OrderID values, which usually matches the row count only if OrderID is unique and non-blank, but it is not the canonical way to count orders. SUM(Orders[OrderID]) is wrong because summing an ID column is meaningless and would produce an arbitrary numeric total. COUNT(Orders[OrderID]) counts non-blank OrderID values, so it would undercount if any OrderID were blank and does not strictly count table rows.

Answer

DISTINCTCOUNT(Orders[OrderID])

Answer

SUM(Orders[OrderID])

Answer

COUNT(Orders[OrderID])