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

Question 1

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model includes a large fact table with 100 million rows. Users are experiencing slow report performance. Which TWO actions should the developer take to improve query performance?

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

Accepted Answer

Create indexes on columns used in filters and relationships.. In DirectQuery mode, incremental refresh is not supported (option A is incorrect). Creating indexes on columns used in filters and relationships (option B) speeds up query execution on SQL Server. Removing unused columns from the fact table (option C) reduces the amount of data transferred per query. Hiding columns (option D) does not affect the data retrieved by queries. Adding calculated columns (option E) increases query overhead and degrades performance.

Answer

Configure incremental refresh to limit data retrieved per query.

Answer

Hide columns that are not needed in reports.

Answer

Add calculated columns to precompute aggregations.

Question 2

What is the most likely cause of the error in the DAX query shown in the exhibit?

Accepted Answer

The [Sales Amount] measure is returning multiple values or is not a valid scalar measure.. The error 'A single value for column 'Sales Amount' in table 'Sales' cannot be determined' occurs because the [Sales Amount] measure is being used in a context that expects a scalar value, but the measure is defined to return multiple values (e.g., using SUMX over a table that returns multiple rows without proper aggregation). In DAX, measures used in EVALUATE statements must return a single scalar value; if the measure is not properly aggregated or contains a many-to-many relationship, it can produce multiple values, causing this error.

Answer

There is no relationship between Date and Product tables.

Answer

The SUMMARIZECOLUMNS function does not support multiple group-by columns.

Answer

The ORDER BY clause is not allowed in EVALUATE statements.

Question 3

A company has a fact table with sales data and multiple dimension tables. They want to create a measure that calculates the total sales amount for the current year, but the measure returns incorrect results when used in a visual with a date hierarchy. What is the most likely cause?

Accepted Answer

The relationship between the date table and the fact table is inactive.. If the relationship between the date table and the fact table is inactive, measures that rely on time intelligence functions (like TOTALYTD, SAMEPERIODLASTYEAR, or a simple SUM with date filtering) will not automatically propagate filters from the date hierarchy to the fact table. In Power BI, only one active relationship can exist between two tables; inactive relationships require explicit activation via USERELATIONSHIP in DAX. Without that, the measure ignores the date filter and returns incorrect or blank results.

Answer

The date table is not marked as a date table in Power BI.

Answer

The fact table is not in a star schema; it is snowflaked.

Answer

The relationship between the date table and the fact table is set to bidirectional cross-filtering.

Question 4

A data model contains a table 'Sales' with columns: Date, ProductID, Quantity, Amount. There is a 'Products' table with columns: ProductID, ProductName, CategoryID. A measure 'Total Sales' = SUM(Sales[Amount]) returns correct values. However, when a user creates a visual with CategoryID from 'Products' and 'Total Sales', some categories show blank. What is the most likely cause?

Accepted Answer

There are ProductID values in Sales that do not exist in Products table.. When ProductID values in the Sales table do not have matching entries in the Products table, the relationship between the two tables will result in blank CategoryID values for those unmatched rows. In Power BI, a many-to-one relationship (the default) filters from the 'one' side (Products) to the 'many' side (Sales), but if a Sales row has a ProductID not present in Products, it cannot be matched, and any column from Products (like CategoryID) will appear as blank in visuals. This is a classic data integrity issue where the fact table contains orphaned foreign keys.

Answer

The 'Total Sales' measure is not properly referencing the Sales table.

Answer

The relationship between Sales and Products is set to many-to-one, single direction.

Answer

The relationship is set to both directions (bidirectional).

Question 5

A data model has a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. There is a 'Customers' table with columns: CustomerID, CustomerName. To analyze orders by customer, what is the best practice for modeling the relationship?

Accepted Answer

Create a one-to-many relationship from Customers to Orders with single direction.. In a star schema, the Customers table (dimension) should have a one-to-many relationship to the Orders table (fact) filtered from the dimension side. This single-direction filter propagation ensures that when a customer is selected, only their orders are shown, while preventing unwanted cross-filtering from orders back to customers. This is the standard best practice for modeling dimension-to-fact relationships in Power BI.

Answer

Create a one-to-one relationship between Customers and Orders based on CustomerID.

Answer

Create an inactive relationship and use USERELATIONSHIP in measures.

Answer

Create a many-to-one relationship from Orders to Customers with both directions.

Question 6

Which THREE of the following are valid reasons to create a calculated table in Power BI?

Accepted Answer

To create a date table that is not available in the data source.. Option C is correct because calculated tables are commonly used to generate a date table with DAX functions like CALENDAR or CALENDARAUTO when no date dimension exists in the source, enabling time intelligence. Option D is correct because calculated tables can create disconnected tables (e.g., via GENERATESERIES or DATATABLE) that serve as slicers or inputs for what-if parameters, which have no relationship to the model. Option E is correct because calculated tables can materialize pre-aggregated summaries using SUMMARIZE or GROUPBY, reducing query-time computation and improving report performance. Option A is not a valid reason because adding a computed column to an existing table is done with a calculated column, not a calculated table. Option B is not a valid reason because merging columns from one table into another is accomplished in Power Query (Merge Queries) or via relationships, not by creating a calculated table.

Answer

To add a column that computes a value based on other columns in the same table.

Answer

To combine two tables by merging columns from one table into another.

Question 7

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model includes a large fact table with 100 million rows. Users are experiencing slow report performance. Which TWO actions should the developer take to improve query performance?

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

Accepted Answer

CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7). Options A and C contain the same valid DAX expression: CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7). This expression correctly uses CALCULATE to modify the filter context, applying DATEDIFF to compute the day difference between OrderDate and ShipDate, and filtering for orders shipped within 7 days. Both are correct because they are identical. Option B uses SUMX with FILTER, but direct date subtraction (Sales[ShipDate] - Sales[OrderDate]) in DAX treats dates as serial numbers with time components, which can lead to inaccurate day counts and is not recommended. Option D also uses direct date subtraction in a filter argument, which is similarly incorrect. Therefore, A and C are correct.

Answer

SUMX(FILTER(Sales, Sales[ShipDate] - Sales[OrderDate] <= 7), Sales[Revenue])

Answer

CALCULATE(SUM(Sales[Revenue]), Sales[ShipDate] - Sales[OrderDate] <= 7)

Question 8

You are reviewing a Power Query M script used to create a table in Power BI. The script imports data from SQL Server, filters for orders in 2022, groups by ProductID to sum revenue, sorts descending, and takes the top 10. However, the table loads slowly. You need to improve performance. Which change should you make?

Accepted Answer

Modify the script to use a native SQL query that performs the filtering and aggregation on the server side.. Pushing filtering, grouping, and aggregation to SQL Server via a native query reduces the volume of data transferred to Power BI and leverages the database engine's optimized execution. This minimizes memory and processing overhead in Power Query, directly addressing the slow load time caused by performing these operations on imported data.

Answer

Add a Table.Buffer step before the filter to speed up subsequent operations.

Answer

Remove the sorting step because it is unnecessary for the final table.

Answer

Combine the filter, group, and sort into a single step using Table.Buffer.

Question 9

You have a Power BI model with a table named Orders that contains columns OrderDate, ShipDate, and CustomerID. You need to create a calculated column that computes the number of days between OrderDate and ShipDate. Which DAX expression should you use?

Accepted Answer

DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY). The DATEDIFF function in DAX calculates the interval between two dates in the specified unit (DAY). This directly computes the number of days between OrderDate and ShipDate, which is the required result for the calculated column.

Answer

DATEADD(Orders[OrderDate], 1, DAY)

Answer

DAY(Orders[ShipDate] - Orders[OrderDate])

Answer

NETWORKDAYS(Orders[OrderDate], Orders[ShipDate])

Question 10

You are modeling data in Power BI that includes a table named SurveyResponses with columns: ResponseID, QuestionID, RespondentID, and AnswerText. Each respondent answers multiple questions. You need to create a measure that counts the number of unique respondents who answered a specific question. Which DAX measure should you use?

Accepted Answer

DISTINCTCOUNT(SurveyResponses[RespondentID]). DISTINCTCOUNT(SurveyResponses[RespondentID]) counts the number of unique RespondentID values in the table, which directly gives the count of unique respondents who answered a specific question when used in a filter context (e.g., with a slicer or visual grouping by QuestionID). This is the standard DAX pattern for counting distinct entities in a column.

Answer

COUNT(SurveyResponses[RespondentID])

Answer

COUNTROWS(SurveyResponses)

Answer

COUNTA(SurveyResponses[RespondentID])

Question 11

You have a Power BI model with a table named Sales that includes columns: OrderDate, Amount, and CustomerID. You need to create a measure that returns the total sales amount for the previous month based on the current filter context. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date])). PREVIOUSMONTH returns a single month period shifted back by one month from the last date in the current filter context, which directly gives the total sales for the previous month. This measure respects the current filter context and works correctly when a proper date table is used.

Answer

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

Answer

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

Answer

CALCULATE(SUM(Sales[Amount]), NEXTMONTH('Date'[Date]))

Question 12

You are reviewing the relationships in a Power BI data model as shown in the exhibit. The model has tables: Sales, Product, Customer, and Category. You need to evaluate the performance impact of the current configuration. Which relationship is most likely to cause performance issues?

Accepted Answer

The relationship between Product and Category. The relationship between Product and Category is most likely to cause performance issues because it is a many-to-many relationship without a bridge table. In Power BI, many-to-many relationships require the engine to materialize cross-join-like intermediate tables in memory, increasing query complexity and reducing performance. This is especially problematic when filtering or aggregating across these tables, as the VertiPaq engine must resolve ambiguity by creating additional internal tables.

Answer

All relationships are equally efficient

Answer

The relationship between Sales and Customer

Answer

The relationship between Sales and Product

Question 13

You are analyzing a DAX query as shown in the exhibit. You need to determine the result set. The model contains tables: Date, Product, and Sales with relationships. Which statement accurately describes the output?

Accepted Answer

The query returns total sales per year and category for Amount > 100. The DAX query uses SUMMARIZECOLUMNS to group sales by 'Year' from the Date table and 'Category' from the Product table, then filters the Sales table to include only rows where Amount > 100. The result is a table of total sales (sum of Amount) for each combination of year and category that meets the filter condition.

Answer

The query returns total sales for each year, ignoring category

Answer

The query returns sales amounts only for products with Amount > 100

Answer

The query returns total sales for each category, ignoring year

Question 14

You are reviewing the partition configuration for a Power BI Import model as shown in the exhibit. The table Sales is partitioned by year. You need to modify the model to improve incremental refresh performance. What change should you make?

Accepted Answer

Configure incremental refresh policy. Configuring an incremental refresh policy (Option B) is the correct approach because it automatically manages partition creation and refresh for the Sales table based on a date/time column. This improves performance by refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions unchanged, reducing refresh time and resource consumption compared to manual yearly partitions.

Answer

Increase the number of partitions to monthly

Answer

Remove all partitions and load data as a single table

Answer

Change the storage mode to DirectQuery

Question 15

You are designing a data model in Power BI that includes a fact table called 'Sales' and dimension tables 'Customer', 'Product', and 'Date'. The 'Sales' table contains columns: 'SalesID', 'CustomerID', 'ProductID', 'DateKey', 'Quantity', and 'Amount'. You need to ensure that the model follows star schema best practices and that filters from the 'Customer' table propagate correctly to the 'Sales' table. What should you do?

Accepted Answer

Create a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID.. In a star schema, the dimension table (Customer) should have a one-to-many relationship to the fact table (Sales) based on the common key (CustomerID). This ensures that filters applied to the Customer table propagate correctly to the Sales table, maintaining referential integrity and enabling efficient query performance.

Answer

Merge the Customer and Sales tables into a single flat table.

Answer

Set the cross-filter direction to Both on the relationship between Customer and Sales.

Answer

Create a many-to-many relationship between Customer and Sales using SalesID.