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

Question 1

You are modeling data from a source that includes a column 'FullName' (e.g., 'John Doe'). You want to create separate 'FirstName' and 'LastName' columns for analysis. What is the most efficient way?

Accepted Answer

In Power Query, split the column by delimiter (space) into two columns.. Splitting a column by delimiter in Power Query is the most efficient, native method for transforming data at the query level. It leverages Power Query's M language to perform the split in a single step, which is optimized for performance and can be refreshed automatically. This approach avoids the overhead of DAX calculated columns, which are computed in the storage engine and can slow down report rendering.

Answer

Create calculated columns using DAX functions LEFT, RIGHT, and FIND.

Answer

Use the 'Replace Values' feature to manually separate names.

Answer

Use Excel formulas in a source query.

Question 2

You need to create a relationship between two tables in Power BI. Both tables contain a column named 'ProductID', but the values in one table are integers and in the other are text. What should you do first?

Accepted Answer

Ensure both columns have the same data type, either by changing the data type in Power Query or in the model view.. In Power BI, relationships require matching data types on both sides of the key columns. If one 'ProductID' column is integer and the other is text, the relationship engine cannot resolve the join because the data types are incompatible. Changing both columns to the same data type—either in Power Query (recommended for performance) or in the model view—resolves this mismatch and allows a valid relationship to be created.

Answer

Merge the two tables into one in Power Query.

Answer

Create a new calculated column that converts the integer to text using FORMAT.

Answer

Set the relationship to 'Many-to-many' to bypass the type mismatch.

Question 3

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 choice is B, many-to-one (*:1) from Orders to Customers, because each individual order row references exactly one customer row, while the same customer key can appear in many order rows — which is precisely the *:1 direction when viewed from Orders toward Customers. In relational terms this is implemented by placing a foreign key in Orders pointing to the Customers primary key, enforcing referential integrity without duplicating customer data. Option A (*:*) is wrong because many-to-many requires a junction table and would let one order map to multiple customers, contradicting the scenario. Option C (1:* from Orders to Customers) reverses the direction, implying one order relates to many customers. Option D (1:1) is wrong because it would restrict each customer to at most one order.

Answer

Many-to-many (*:*)

Answer

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

Answer

One-to-one (1:1)

Question 4

You have a Power BI model with a 'Date' table marked as a date table. You need to create a measure that calculates the running total of sales over the last 12 months. Which DAX function should you use?

Accepted Answer

DATESINPERIOD. DATESINPERIOD, is correct because it allows you to define a dynamic window of dates—specifically, the last 12 months ending with the latest date in the current filter context. When used with a measure like CALCULATE(SUM(Sales[Amount]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)), it shifts the date range backward by 12 months from the last visible date, making it ideal for a rolling 12-month total. The 'Date' table being marked as a date table ensures that time intelligence functions respect the continuous date range.

Answer

PREVIOUSYEAR

Answer

DATESYTD

Answer

DATEADD

Question 5

Which TWO actions are best practices for optimizing Power BI data models?

Accepted Answer

Replace text-based relationship columns with integer keys.. Replacing text-based relationship columns with integer keys (surrogate keys) reduces storage size and improves join performance. Power BI's VertiPaq engine compresses integer columns far more efficiently than text columns, leading to faster query execution and smaller memory footprint.

Answer

Use calculated columns instead of measures where possible.

Answer

Hide columns that are not used in reports.

Answer

Use many-to-many relationships instead of bridge tables.

Question 6

Which THREE factors should you consider when designing a star schema in Power BI?

Accepted Answer

A separate date table should be created for time intelligence.. A separate date table is required for time intelligence functions in Power BI because DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) rely on a continuous, contiguous date range with no gaps. Power BI automatically marks a table as a date table only if it contains a complete set of dates from the earliest to the latest transaction, enabling functions like DATEADD and DATESBETWEEN to work correctly across all granularities.

Answer

Use natural keys instead of surrogate keys in dimension tables.

Answer

Use a snowflake schema to reduce data redundancy.

Question 7

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 8

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

Accepted Answer

Date. Option D, Date, is correct because a dimension table stores descriptive attributes used to filter, group, and label facts, and a Date dimension provides calendar attributes such as year, quarter, month, and day for slicing sales measures. In a star schema, the fact table holds numeric measures and foreign keys, while dimensions like Date supply the context for analysis. TransactionID, OrderQuantity, and SalesAmount are not dimension tables: TransactionID is typically a fact table key or degenerate dimension, and OrderQuantity and SalesAmount are numeric measures that belong in the fact table. Therefore, Date is the only appropriate dimension table among the listed options.

Answer

TransactionID

Answer

OrderQuantity

Answer

SalesAmount

Question 9

You are modeling a many-to-many relationship between 'Student' and 'Class' tables. Which approach should you use in Power BI to handle this?

Accepted Answer

Add a bridge table with composite keys. A many-to-many relationship in Power BI requires a bridge (or junction) table that contains composite keys (e.g., StudentID and ClassID) to resolve the relationship into two one-to-many relationships. This allows Power BI to properly filter and aggregate data across both tables without ambiguity.

Answer

Merge both tables into one

Answer

Use bidirectional cross-filtering

Answer

Create a single one-to-many relationship

Question 10

You have a Power BI model with a table 'Sales' and a related 'Product' table. You want to count the number of distinct products sold. Which DAX expression should you use?

Accepted Answer

DISTINCTCOUNT(Sales[ProductID]). DISTINCTCOUNT(Sales[ProductID]) returns the number of unique ProductID values in the Sales table, which directly answers the requirement to count distinct products sold. This function counts each distinct value in the specified column, ignoring duplicates, and is the standard DAX measure for distinct count calculations.

Answer

COUNTROWS(Sales)

Answer

COUNT(Sales[ProductID])

Answer

COUNTA(Sales[ProductID])

Question 11

Which TWO actions can improve performance of a Power BI DirectQuery model?

Accepted Answer

Ensure proper indexing in the source database. Proper indexing in the source database reduces the query execution time for DirectQuery models. DirectQuery sends queries to the source database in real-time, so efficient indexes on columns used in filters, joins, and aggregations minimize table scans and improve retrieval speed.

Answer

Switch the model to Import mode

Answer

Enable bidirectional cross-filtering on all relationships

Answer

Use calculated columns with complex logic

Question 12

Which TWO DAX functions can be used to create a calculated table in Power BI?

Accepted Answer

FILTER. FILTER (B) is correct because it is a table-returning DAX function that produces a filtered table, which can be used as the expression of a calculated table (e.g., a table defined as FILTER(Sales, Sales[Amount] > 1000)). SUMMARIZECOLUMNS (D) is also correct because it returns a table grouped by specified columns with aggregated values, making it a valid expression for creating a calculated table. Both functions return table values, which is the requirement for a calculated table's definition. SELECTEDVALUE (A) returns a scalar value from a single-column context, not a table, so it cannot define a calculated table. CALCULATE (C) modifies filter context and returns a scalar value, not a table, so it is invalid here. SUMX (E) is an iterator that returns a scalar aggregate, not a table, so it also cannot create a calculated table.

Answer

SELECTEDVALUE

Answer

CALCULATE

Answer

SUMX

Question 13

A data model contains a Date table and a Sales table. You need to create a measure that calculates total sales for the previous year. Which DAX function should you use?

Accepted Answer

SAMEPERIODLASTYEAR. SAMEPERIODLASTYEAR is the correct DAX function for calculating total sales for the previous year because it returns a set of dates shifted back by exactly one year while preserving the current filter context (e.g., month, quarter). This function is specifically designed for year-over-year comparisons and works seamlessly with a Date table marked as a date table in the model. Option B: DATEADD can also shift dates by one year but requires specifying the interval and number of intervals, making it less direct for this specific requirement. Option C: PARALLELPERIOD returns the entire parallel period (e.g., full year) regardless of current granularity, which does not preserve the same relative period. Option D: PREVIOUSYEAR returns the entire previous year, not the same period shifted back, so it does not maintain month-over-month or quarter-over-quarter comparisons.

Answer

DATEADD

Answer

PARALLELPERIOD

Answer

PREVIOUSYEAR

Question 14

You have a Power BI data model with a fact table and multiple dimension tables. You notice that many-to-many relationships cause ambiguous results. What is the best practice to resolve this?

Accepted Answer

Add a bridge table with appropriate relationships. In Power BI, many-to-many relationships between fact and dimension tables can produce ambiguous results because the model cannot determine a unique filter propagation path. The best practice is to introduce a bridge table that resolves the many-to-many relationship into two one-to-many relationships, ensuring unambiguous filter context and correct aggregations.

Answer

Change the relationship to one-to-one

Answer

Use a bidirectional cross-filter direction

Answer

Create a calculated table to merge the dimensions

Question 15

You are designing a star schema for a sales analysis report. The source data includes Order Details, Products, Customers, and Dates. Which table should be the fact table?

Accepted Answer

Order Details. In a star schema, the fact table stores quantitative, measurable data (metrics) and foreign keys linking to dimension tables. Order Details contains sales transactions with measures like quantity and revenue, making it the correct fact table for sales analysis.

Answer

Dates

Answer

Customers

Answer

Products