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

Question 1

You have the DAX expression shown. What does this expression return?

Accepted Answer

Total sales for the year of the latest date in the current filter context. The DAX expression uses CALCULATE to modify the filter context for 'Sales[Amount]' by applying a filter that selects only dates where the year equals the year of the latest date present in the current filter context. The MAX function returns the maximum date from the Date table within the current filter context, and YEAR extracts its year. This effectively returns total sales for the year containing the most recent date in the current filter context, not necessarily the previous year or the absolute maximum year in the Date table.

Answer

Total sales for the previous year

Answer

Total sales for the maximum year in the Date table

Answer

Total sales for the current year

Question 2

Which TWO of the following are valid methods to handle slowly changing dimensions (SCD) Type 2 in Power Query? (Select two.)

Accepted Answer

Use a lookup table that tracks changes with start and end dates. Option C is correct because SCD Type 2 preserves history by maintaining a lookup/reference table where each version of a dimension record carries start and end dates (validity period), which is exactly how Power Query can model historical tracking. Option E is correct because the defining characteristic of SCD Type 2 is inserting a new row for a changed attribute, assigning a new surrogate key and an effective date range, while the prior row is closed off rather than modified. Options A, B, and D are not valid SCD Type 2 handling methods: merging a dimension with a fact table is a join operation, not a change-tracking technique; updating the existing row in place (B) and overwriting existing attributes (D) describe SCD Type 1 behavior, which destroys history and is the opposite of Type 2.

Answer

Merge the dimension table with a fact table

Answer

Update the existing row with new attributes

Answer

Overwrite the existing attributes

Question 3

You have the Power Query M code shown. What is the result of this query?

Accepted Answer

A table with total sales per product for years 2020 and later. Based on the provided Power Query M code, the query first filters rows where the year is 2020 or later, then groups by product, and finally aggregates the Sales column by sum. This yields a table with one row per product and a column for total sales, matching option A.

Answer

A single row with the total sales for all products

Answer

A table of all sales for years 2020 and later

Answer

A table with total sales per year

Question 4

You have a data model with two relationships between Sales and Date: one active (OrderDateKey) and one inactive (ShipDateKey). The DAX expression uses USERELATIONSHIP(Sales[DateKey], 'Date'[DateKey]). Which date will be used for filtering?

Accepted Answer

Ship dates. The USERELATIONSHIP function temporarily activates the inactive relationship between Sales[ShipDateKey] and 'Date'[DateKey] for the duration of the DAX expression. This overrides the default active relationship (OrderDateKey), so filtering will be based on ship dates, not order dates.

Answer

Both order and ship dates

Answer

Order dates

Answer

No relationship, all dates

Question 5

You are building a Power BI data model with a sales table containing millions of rows. You need to design a date dimension table to support time intelligence calculations. What is the best practice for creating the date table?

Accepted Answer

Create a date table using DAX CALENDARAUTO function. The best practice here is to create a dedicated date table using the DAX CALENDARAUTO function (option B), because it automatically scans all date columns in the model and generates a contiguous, gap-free date range covering the entire fact data, which is essential for reliable time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. A separate date table also lets you mark it as a date table and build a proper one-to-many relationship with the sales table, avoiding the performance and correctness issues of using a fact column directly. Option A is wrong because using the sales date column as the date dimension prevents a proper star schema and can produce incorrect or slow time intelligence. Option C is not best practice since an imported Excel date table must be manually maintained and may not cover the full range of fact dates. Option D is not the recommended approach because hand-writing M code is more error-prone and unnecessary when CALENDARAUTO or CALENDAR already provides a robust solution.

Answer

Use the date column from the sales table as the primary date dimension

Answer

Import a date table from an Excel file

Answer

Create a date table using Power Query M language

Question 6

You are modeling data for a retail company. The fact table contains sales transactions with columns: OrderDate, ProductID, CustomerID, Quantity, and SalesAmount. You also have dimension tables: Date, Product, Customer. The Date table has a relationship to the fact table on OrderDate, and the Customer table has a relationship to the fact table on CustomerID. However, you need to support analysis by both OrderDate and ShipDate. What is the recommended approach?

Accepted Answer

Create a second Date table and add an inactive relationship from ShipDate to the new Date table, then use USERELATIONSHIP in measures. The recommended approach is option B: create a second Date table (often called a role-playing dimension) and add an inactive relationship from the fact table's ShipDate to that new Date table, then activate it per measure using the USERELATIONSHIP DAX function. This works because a single Date table can only have one active relationship to a fact table at a time, so a second Date table is needed to represent the ShipDate role, and USERELATIONSHIP lets you switch the active relationship only for the measures that analyze shipping dates. Option A is wrong because concatenating OrderDate and ShipDate into one column destroys the ability to slice by each date independently and breaks date hierarchies. Option C is wrong because merely adding ShipDate as a fact column does not create a relationship to the Date dimension, so time intelligence and date filtering would not work. Option D is wrong because a single Date table cannot have two active relationships to the same fact table simultaneously; only one relationship can be active at a time.

Answer

Create a calculated column that combines OrderDate and ShipDate into a single date column

Answer

Add ShipDate as a column in the fact table and use it directly in calculations

Answer

Create two active relationships from the fact table to the Date table: one on OrderDate and one on ShipDate

Question 7

You have a Power BI model with a fact table and several dimension tables. You need to ensure that when a filter is applied to a dimension table, it also filters related rows in other dimension tables through the fact table. Which type of relationship direction should you configure?

Accepted Answer

Both. Cross filter direction set to 'Both' allows filters to propagate in both directions, enabling dimension tables to filter each other through the fact table. Option A is wrong because 'Automatic' is a valid setting but typically filters in a single direction based on cardinality, so it does not guarantee the required behavior. Option C is wrong because 'None' disables cross filtering. Option D is wrong because 'Single' only filters from the one side to the many side, which does not allow filters to flow from one dimension to another via the fact table.

Answer

Automatic

Answer

None

Answer

Single

Question 8

You are reviewing a Power BI model definition in JSON. The Sales table contains a ProductID column but the Product table does not have a ProductID column; instead it has a ProductID column with dataType string. What issue will occur when you try to create a relationship?

Accepted Answer

The relationship will fail because the data types are incompatible. The correct answer is A: the relationship will fail because the data types are incompatible. In Power BI, a relationship between two columns requires matching (or compatible) data types; if Sales[ProductID] is numeric (e.g., int64) and Product[ProductID] is string, the model cannot create the relationship and will raise a type mismatch error. Option B is wrong because column names do not need to be different—they can even be identical. Option C is wrong because cross-filter direction is a configuration setting, not an automatic outcome of a type mismatch. Option D is wrong because incompatible data types prevent the relationship from being created at all, not merely turn it into many-to-many.

Answer

The relationship will fail because the column names must be different

Answer

The relationship will create a cross filter direction of single

Answer

The relationship will work but be many-to-many due to different data types

Question 9

You are modeling data for a sales analysis. You have a fact table 'Sales' and a dimension table 'Date'. You need to create a relationship between 'Sales[OrderDate]' and 'Date[Date]'. The 'Date' table contains dates from January 1, 2020 to December 31, 2025. The 'Sales' table contains orders from January 1, 2021 to June 30, 2024. Which relationship cardinality should you use?

Accepted Answer

Many-to-one (*:1). The correct cardinality is Many-to-one (*:1) because the 'Sales' table contains many rows for each date (multiple orders can occur on the same date), while the 'Date' table has unique dates. This creates a classic star-schema relationship where the many side (Sales) points to the one side (Date). In Power BI, the cardinality is defined from the perspective of the filter propagation direction, so *:1 means many rows in Sales match one row in Date.

Answer

Many-to-many (*:*)

Answer

One-to-many (1:*)

Answer

One-to-one (1:1)

Question 10

You have a Power BI data model with a table named 'Sales' and a table named 'Targets'. The 'Sales' table contains daily sales data, and the 'Targets' table contains monthly sales targets for each product category. You need to create a measure that calculates the percentage of target achieved for the current month. The relationship between 'Sales' and 'Targets' is on CategoryID and Month. However, the 'Sales' table has a granularity of day, while 'Targets' has month. What issue might you encounter when creating this measure?

Accepted Answer

The relationship may cause ambiguity because the granularity of the tables is different. The 'Sales' table has daily granularity while the 'Targets' table has monthly granularity, which creates a potential ambiguity in the relationship. In Power BI, when two tables are related on columns (CategoryID and Month) but one table has multiple rows per month (daily sales) and the other has a single row per month, the relationship is valid but can cause unexpected filter propagation. Specifically, the measure may incorrectly aggregate daily sales across multiple months if the month column in 'Sales' is not properly aligned, leading to inaccurate percentage calculations.

Answer

The relationship requires bidirectional cross-filtering to work correctly

Answer

The relationship will create a many-to-many cardinality that slows performance

Answer

The relationship will not work because the columns have different data types

Question 11

You are reviewing a Power BI model definition with incremental refresh policy. The Sales table has an incremental refresh policy that refreshes the last 365 days. However, the report shows data for dates beyond 365 days ago. What is the most likely reason?

Accepted Answer

Incremental refresh only refreshes the data, it does not remove historical data. Incremental refresh policy only affects refresh operations; it does not delete historical data from the model. Data older than 365 days remains in the model unless explicitly removed. Option A is wrong because the policy is defined for the table, not partitions. Option C is wrong because the policy is set to refresh the last 365 days, not filter. Option D is wrong because incremental refresh does not automatically remove old data; it only refreshes recent data.

Answer

The incremental refresh policy is applied at the partition level instead of the table level

Answer

The incrementalPeriodsOffset should be set to a negative value to include historical data

Answer

The refresh policy automatically removes data older than the incremental period

Question 12

Which TWO actions should you take when designing a data model that includes a fact table with a large number of rows and multiple dimension tables? (Select exactly 2.)

Accepted Answer

Use integer-based foreign keys in the fact table to link to dimension tables. Option A is correct because using integer surrogate keys as foreign keys in a large fact table minimizes storage and improves join performance and compression compared to string keys, which is critical when the fact table has a very large number of rows. Option D is correct because hiding the fact table from the report view prevents report authors from accidentally using raw fact columns (such as keys and degenerate dimensions) directly, steering them to the dimension tables and defined measures instead. Option B is not appropriate because calculated columns consume memory and storage and are best avoided in large fact tables; such attributes should generally live in dimensions or be handled via measures. Option C is not appropriate because Auto Date/Time creates hidden date tables per date column, adding model bloat and overhead, which is undesirable in a model with a large fact table; a dedicated date dimension should be used instead. Option E is not appropriate because setting default summarization to 'Sum' on all numeric columns is indiscriminate and can produce misleading results for numeric columns that are not additive, such as keys or rates.

Answer

Create calculated columns in the fact table for additional attributes

Answer

Enable the Auto Date/Time feature for date columns

Answer

Set default summarization to 'Sum' for all numeric columns in the fact table

Question 13

Which TWO are valid reasons to use a star schema design in Power BI? (Select exactly 2.)

Accepted Answer

It improves query performance by reducing the number of joins needed. Option A is correct because a star schema places a single fact table at the center surrounded by denormalized dimension tables, so queries typically require only one join per dimension instead of the multiple joins a snowflake schema demands, which reduces query complexity and improves performance in Power BI. Option D is correct because separating dimension attributes into their own dedicated dimension tables (rather than duplicating them across a wide fact table) avoids redundant storage and enables the VertiPaq engine to compress those columns more efficiently, reducing overall model size. Option B is not a valid reason because star schemas are built on one-to-many relationships between dimensions and the fact table; many-to-many relationships are a separate modeling concern and are not an inherent benefit of star schemas. Option C is incorrect because time intelligence functions in DAX require a properly configured date table marked as a date table, not a star schema specifically. Option E is incorrect because a star schema typically has more tables than a single flat table, not fewer, since each dimension is its own table.

Answer

It allows many-to-many relationships between dimensions

Answer

It is required to use time intelligence functions

Answer

It reduces the number of tables in the model

Question 14

You have a DAX measure that sums the Amount column for rows where Quantity is greater than 10. The Sales table has 10 million rows. What is a potential performance issue with this measure?

Accepted Answer

The SUMX iterator can be slow on large tables; consider using CALCULATE instead. The correct answer is A: the SUMX iterator can be slow on large tables, and CALCULATE should be considered instead. SUMX evaluates its expression row by row across the 10 million rows of the Sales table, which is expensive; rewriting the logic with CALCULATE and a simple filter lets the storage engine handle the aggregation more efficiently. Option B is wrong because FILTER is not necessarily used here and a full table scan is not the specific issue described. Option C is wrong because context transition is triggered by CALCULATE, not by SUMX. Option D is wrong because the condition Quantity > 10 is simple and can be optimized.

Answer

The FILTER function will cause a full table scan each time the measure is evaluated

Answer

The measure uses context transition which can cause performance issues

Answer

The measure uses a complex condition that cannot be optimized

Question 15

You have a Power BI data model that uses a DirectQuery connection to a SQL Server database. The model contains a fact table 'OrderDetails' with 50 million rows and a dimension table 'Products' with 10,000 rows. You need to create a measure that calculates the average order quantity per product. What is the best approach to optimize performance?

Accepted Answer

Use AVERAGEX over the Products table and sum the quantity for each product. Using AVERAGEX with the Products table reduces the number of rows processed; it calculates the average per product, which is more efficient than iterating over millions of rows. Option A is wrong because CALCULATE with FILTER on the fact table can be slow. Option B is wrong because calculated columns in DirectQuery are evaluated row by row and can degrade performance. Option D is wrong because creating an aggregated table in the source is not always feasible and adds complexity.

Answer

Use CALCULATE with a filter on the Products table

Answer

Create a calculated column in the Products table that sums the quantity

Answer

Create a summarized table in the source database and import it