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

Question 1

You are a data analyst for a manufacturing company. You have a Power BI semantic model with a fact table named Production that contains columns Date, ProductID, PlantID, UnitsProduced, and DefectCount. You need to create a measure that calculates the defect rate as DefectCount divided by UnitsProduced, but only for products that have a UnitsProduced greater than zero. Which DAX expression should you use?

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

Accepted Answer

SUMX(FILTER(Production, Production[UnitsProduced] > 0), DIVIDE(Production[DefectCount], Production[UnitsProduced])). The measure must calculate the defect rate only for products with UnitsProduced greater than zero. Using SUMX with FILTER ensures that the division occurs only for rows that meet the condition, and the results are aggregated correctly. The other options either do not filter appropriately or filter only part of the calculation.

Answer

DIVIDE(SUM(Production[DefectCount]), SUMX(FILTER(Production, Production[UnitsProduced] > 0), Production[UnitsProduced]))

Answer

DIVIDE(SUM(Production[DefectCount]), SUM(Production[UnitsProduced]))

Answer

CALCULATE(DIVIDE(SUM(Production[DefectCount]), SUM(Production[UnitsProduced])), Production[UnitsProduced] > 0)

Question 2

You are a Power BI data analyst for an e-commerce company. Your semantic model includes a fact table named Orders with columns OrderID, CustomerID, OrderDate, and OrderAmount. You need to create a measure that calculates the year-over-year growth of order amounts. You decide to use time intelligence functions. Which two DAX functions should you use to calculate the previous year's order amount? (Choose two.)

Accepted Answer

SAMEPERIODLASTYEAR. Both SAMEPERIODLASTYEAR and DATEADD are time intelligence functions that return a shifted set of dates for the previous year, enabling year-over-year comparisons when used within CALCULATE. TOTALYTD is for year-to-date, PREVIOUSYEAR returns the entire previous year, and PARALLELPERIOD shifts periods but is less precise for same-period comparisons.

Answer

TOTALYTD

Answer

PARALLELPERIOD

Answer

PREVIOUSYEAR

Question 3

You are a data analyst for a telecommunications company. You have a Power BI semantic model that contains a fact table named Calls and a dimension table named Customers. The Calls table has a column CallDuration in seconds. You need to create a measure that calculates the average call duration per customer, but only for customers who have made at least 10 calls. Which DAX expression should you use?

Accepted Answer

CALCULATE(AVERAGE(Calls[CallDuration]), FILTER(Customers, COUNTROWS(RELATEDTABLE(Calls)) >= 10)). The measure must filter the Calls table to only include calls from customers with at least 10 calls, then compute the average duration. Using CALCULATE with AVERAGE and a FILTER on Customers that checks COUNTROWS(RELATEDTABLE(Calls)) >= 10 achieves this. The relationship propagates the customer filter to the Calls table.

Answer

AVERAGEX(Calls, IF(COUNTROWS(RELATEDTABLE(Customers)) >= 10, Calls[CallDuration]))

Answer

AVERAGEX(FILTER(Customers, CALCULATE(COUNTROWS(Calls)) >= 10), CALCULATE(AVERAGE(Calls[CallDuration])))

Answer

AVERAGEX(FILTER(Customers, COUNTROWS(RELATEDTABLE(Calls)) >= 10), AVERAGE(Calls[CallDuration]))

Question 4

You are a Power BI data analyst for a manufacturing company. Your semantic model includes a fact table named Production with columns ProductionID, ProductID, DateKey, and UnitsProduced. You have a dimension table named Products with ProductID, ProductName, and Category. You need to create a measure that calculates the total units produced for a selected category, but you want to ignore any filters on the ProductName column. Which DAX function should you use in the measure?

Accepted Answer

CALCULATE(SUM(Production[UnitsProduced]), REMOVEFILTERS(Products[ProductName])). REMOVEFILTERS is the correct function to clear filters from a specific column within CALCULATE. It allows the measure to ignore any slicer or filter on ProductName while still respecting filters on other columns like Category. ALL can work but is less explicit, KEEPFILTERS preserves filters, and USERELATIONSHIP activates relationships.

Answer

CALCULATE(SUM(Production[UnitsProduced]), ALL(Products[ProductName]))

Answer

CALCULATE(SUM(Production[UnitsProduced]), USERELATIONSHIP(Products[ProductName], Production[ProductID]))

Answer

CALCULATE(SUM(Production[UnitsProduced]), KEEPFILTERS(Products[ProductName]))

Question 5

You are a data analyst for an online retailer. You have a Power BI semantic model with a fact table named Sales and a dimension table named Products. The Products table has a column named Category. You need to create a measure that calculates total sales for a selected category, but you also want to allow users to see sales for all categories when no category is selected. Which two DAX functions should you use in the measure? (Choose two.)

Accepted Answer

CALCULATE. To dynamically show sales for a selected category or all categories when none is selected, you need to detect the selection and conditionally modify filter context. SELECTEDVALUE returns the selected category or BLANK, and CALCULATE applies the appropriate filter. Together, they enable the desired behavior.

Answer

ALL

Answer

FILTER

Answer

HASONEVALUE

Question 6

You are a data analyst for a financial services company. You are designing a Power BI semantic model that includes a fact table named Transactions with columns: TransactionID, AccountID, TransactionDate, Amount, and TransactionTypeID. You also have a dimension table named TransactionTypes with columns: TransactionTypeID, TypeName, and Category. You need to optimize the model for performance and usability. Which two actions should you take? (Choose two.)

Accepted Answer

Create a relationship between Transactions[TransactionTypeID] and TransactionTypes[TransactionTypeID].. The two essential actions are to create the relationship between the fact and dimension tables and to hide the foreign key column in the fact table. The relationship enables filtering and grouping, while hiding the foreign key prevents report authors from using it directly, promoting the use of descriptive attributes. These steps optimize both performance and usability in a star schema.

Answer

Create a hierarchy in the TransactionTypes table with Category and TypeName.

Answer

Set the data type of TransactionDate to Date and mark it as a date table.

Answer

Create a calculated column in Transactions that concatenates AccountID and TransactionTypeID.

Question 7

You are a data analyst for an e-commerce company. You have a Power BI semantic model with a fact table named Orders and a dimension table named Products. The Orders table has a column ProductID, and the Products table has a column ProductID. You need to create a relationship between the two tables. However, the Products table contains duplicate ProductID values. What should you do to create a valid relationship?

Accepted Answer

Remove duplicates from the Products table so that ProductID is unique.. The correct action is to remove duplicates from the Products table to ensure ProductID is unique. This allows a one-to-many relationship from Products to Orders. Many-to-many, composite keys, or one-to-one relationships do not address the root cause and are not suitable for this scenario. Data quality is essential for a valid star schema.

Answer

Create a many-to-many relationship between Orders and Products.

Answer

Set the relationship cardinality to one-to-one.

Answer

Create a relationship using a composite key that includes ProductID and another column.

Question 8

A company has a Power BI dataset that contains a date table with columns: Date, Year, Month, Quarter, Day. The data model also includes a sales fact table with a SalesDate column. To enable time intelligence functions like TOTALYTD, what is the minimum requirement for the relationship between these tables?

Accepted Answer

Create a one-to-many relationship from the date table to the sales table and mark the date table as a date table.. Time intelligence functions like TOTALYTD require a properly configured date table marked as a date table, with a one-to-many relationship from the date table to the sales fact table. This ensures that the date table provides a continuous, unique set of dates that Power BI can use for time-based calculations, and marking it as a date table enables the engine to recognize it as the primary date dimension for time intelligence.

Answer

Create a calculated column in the sales table to extract the date part and relate it to the date table.

Answer

Create a many-to-many relationship between the date table and the sales table.

Answer

Create a one-to-many relationship from the sales table to the date table with bidirectional cross-filtering.

Question 9

A Power BI data model includes a table 'Orders' with columns OrderID, CustomerID, OrderDate, SalesAmount. The model also has a 'Date' table and a 'Customer' table. The relationships are: Orders[CustomerID] -> Customer[CustomerID] (many-to-one, single direction) and Orders[OrderDate] -> Date[Date] (many-to-one, single direction). A user creates a measure that sums SalesAmount and then filters by a slicer on Customer[City]. The slicer works correctly. However, when the user adds another slicer on Date[Year], the measure does not respect both slicers simultaneously. What is the most likely cause?

Accepted Answer

The measure might be using ALL or ALLEXCEPT that removes the filter context from the Date table.. The measure likely uses ALL or ALLEXCEPT, which removes the filter context from the Date table. Even though the relationships are correctly configured and filters from the Date slicer propagate to Orders via the single-direction relationship, if the measure explicitly ignores those filters using a function like ALL(Date[Year]) or ALLEXCEPT(Orders, ...), the Date slicer will have no effect on the measure. This is a common DAX mistake where filter removal functions override slicer selections.

Answer

The Customer and Date tables are not related to each other.

Answer

The relationship between Orders and Date is inactive.

Answer

The relationships are set to single direction, so filters from Date do not propagate to Orders.

Question 10

A Power BI developer needs to model data from two sources: an on-premises SQL Server database and a cloud-based Salesforce instance. The developer wants to create a star schema in Power BI. Which approach should the developer use to combine the data?

Accepted Answer

Use Power Query in Power BI Desktop to import both sources and merge/append queries as needed.. Power Query in Power BI Desktop is the appropriate tool to import data from both an on-premises SQL Server database and a cloud-based Salesforce instance, allowing the developer to merge or append queries as needed to shape the data into a star schema. This approach supports combining disparate sources into a single import model, which is essential for creating a star schema with fact and dimension tables. Using Power Query ensures that all data is loaded into memory, enabling fast query performance and full modeling capabilities.

Answer

Use DirectQuery for both sources and create relationships in the model.

Answer

Use Power BI dataflows to ingest both sources and then reference them in a dataset.

Answer

Create a composite model using DirectQuery for SQL Server and Import for Salesforce.

Question 11

A Power BI developer has a fact table that contains sales data at the transaction level. The table includes columns: TransactionID, ProductID, CustomerID, DateKey, Quantity, UnitPrice, Discount, and SalesAmount. The developer wants to create a measure for total sales after discount. Which approach is best for performance and accuracy?

Accepted Answer

Add a calculated column in Power Query: NetAmount = Quantity * UnitPrice - Discount, then create a measure: SUM(Sales[NetAmount]). It performs the net amount calculation at the row level in Power Query (M), which is computed during data refresh and stored in the table. This avoids runtime row-by-row iteration in DAX, making the measure SUM(Sales[NetAmount]) a simple, highly efficient aggregation. It ensures both performance and accuracy, as the discount is applied per transaction before aggregation.

Answer

Create a measure: SUM(Sales[SalesAmount]) - SUM(Sales[Discount])

Answer

Create a measure: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] - Sales[Discount])

Answer

Create a measure: SUM(Sales[Quantity] * Sales[UnitPrice]) - SUM(Sales[Discount])

Question 12

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model contains a large fact table with sales data. Users report that reports using this model are slow. Which design change would most improve query performance?

Accepted Answer

Remove unnecessary columns from the fact table.. Removing unnecessary columns from the fact table reduces the amount of data that must be transferred from SQL Server to Power BI for each query. In DirectQuery mode, every report interaction sends a query to the source database, so fewer columns mean smaller result sets and faster query execution. This directly addresses the performance bottleneck caused by a large fact table without changing the underlying storage mode.

Answer

Remove all relationships between tables.

Answer

Switch the model to Import mode.

Answer

Disable the 'Reduce queries' option in report settings.

Question 13

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?

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 14

A Power BI developer is troubleshooting a report that uses a calculated table. The calculated table is defined as: 'Sales Summary = SUMMARIZE(Sales, Sales[ProductID], "Total Sales", SUM(Sales[Amount]))'. Users report that the 'Total Sales' column shows incorrect values when slicers are applied to the report. What is the most likely cause?

Accepted Answer

Calculated tables are static and do not respond to slicer selections.. Calculated tables in Power BI are evaluated at data refresh time and stored in the model as static data. They do not respond to slicer selections or any other report-level filters because they are not recalculated in the query context. Therefore, the 'Total Sales' column in the 'Sales Summary' table will always show the same aggregated values regardless of slicer interactions, which is why users see incorrect values when applying slicers.

Answer

The calculated table lacks a relationship to the Sales table.

Answer

The SUMMARIZE function syntax is incorrect.

Answer

The calculated table is not marked as a date table.

Question 15

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.