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

Question 1

A data analyst is designing a star schema in Power BI. The model includes a table named 'Orders' with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount. Which column should NOT be included in the fact table to maintain a proper star schema?

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

Accepted Answer

OrderID. In a proper star schema, fact tables should contain quantitative measures and foreign keys to dimension tables. Columns like CustomerID and ProductID serve as foreign keys linking to dimension tables, so they should remain in the fact table. OrderID is a natural key that typically belongs in an Order dimension table; the fact table should use a surrogate OrderKey instead. Including OrderID directly would duplicate dimensional data and reduce modeling flexibility.

Answer

Quantity

Answer

SalesAmount

Answer

CustomerID

Question 2

A company has a Power BI semantic model that uses Import mode. The model contains a table with 10 million rows. The data source is a SQL Server view that takes 5 minutes to execute. The scheduled refresh is set to every hour. What is the likely impact on refresh performance?

Accepted Answer

Refresh will take at least 5 minutes plus data loading time.. The refresh process must first execute the SQL Server view to retrieve data, which takes at least 5 minutes, and then load that data into the Import mode model. The total refresh time is the sum of the query execution time and the data loading time, so it will be at least 5 minutes plus additional time for loading 10 million rows.

Answer

Refresh will fail due to timeout on the gateway.

Answer

The model will automatically use incremental refresh to split the load.

Answer

Refresh will complete in parallel with the view execution.

Question 3

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?

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 4

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 5

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 6

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 7

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 8

A company has a fact table 'Sales' with a column 'SalesAmount' and a dimension table 'Date'. They want to create a measure that calculates the running total of sales over time. The Date table is marked as a date table. Which DAX expression is correct?

Accepted Answer

Running Total = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD(Date[Date])). DATESYTD returns a set of dates from the start of the year to the latest date in the current filter context, and when wrapped in CALCULATE, it correctly computes a year-to-date running total. Since the Date table is marked as a date table, DATESYTD works seamlessly with the time intelligence functions in DAX.

Answer

Running Total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Date), Date[Date] <= EARLIER(Date[Date])))

Answer

Running Total = TOTALYTD(SUM(Sales[SalesAmount]), Date[Date])

Answer

Running Total = SUMX(Sales, Sales[SalesAmount])

Question 9

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.. Calculated tables in Power BI are created using DAX and stored in memory, allowing you to generate a date table when no suitable date table exists in the data source. This is a common pattern to ensure a complete date range for time intelligence functions, as Power BI requires a continuous date table for functions like TOTALYTD or SAMEPERIODLASTYEAR.

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 10

A company has a Power BI semantic model with a table named 'Sales' that contains columns: OrderDate, ShipDate, Quantity, and Revenue. The company wants to create a measure that calculates the total revenue for orders shipped within 7 days of the order date. Which DAX expression should be used?

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 11

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 12

You are designing a Power BI data model that includes a table named Sales with 10 million rows. You need to create a relationship between Sales and a Product dimension table. The Product table has 10,000 rows. Which configuration will provide the best query performance?

Accepted Answer

Create a one-to-many relationship from Product to Sales. A one-to-many relationship from Product (the dimension table with unique values) to Sales (the fact table with many rows) is the standard star schema design. This configuration allows Power BI to use the smaller Product table to filter the larger Sales table efficiently, leveraging in-memory columnar storage and automatic aggregations for optimal query performance.

Answer

Create a many-to-one relationship from Sales to Product

Answer

Do not create a relationship; use LOOKUPVALUE in measures

Answer

Create a relationship with cross filter direction set to Both

Question 13

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 14

You have a Power BI data model with a table named Employees that includes columns: EmployeeID, ManagerID, and EmployeeName. You need to create a hierarchy that shows the reporting structure. Which type of relationship is required?

Accepted Answer

A self-referencing relationship. A self-referencing relationship (Option C) is required because the Employees table contains both EmployeeID and ManagerID, where ManagerID references EmployeeID within the same table. This allows Power BI to create a parent-child hierarchy that accurately represents the reporting structure, such as an org chart. In Power BI, this is implemented by creating a relationship from ManagerID to EmployeeID within the same table.

Answer

Many-to-many relationship

Answer

Bidirectional cross-filtering

Answer

One-to-many relationship to a separate table

Question 15

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