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

Question 1

You have a Power BI data model with a table that contains duplicate rows. You want to remove duplicates in Power Query. Which transformation should you use?

Accepted Answer

Remove Duplicates. The 'Remove Duplicates' transformation in Power Query removes duplicate rows based on selected columns. Option A, 'Replace Values', substitutes one value for another. Option C, 'Filter Rows', keeps or removes rows based on conditions. Option D, 'Group By', aggregates rows by grouping columns.

Answer

Replace Values

Answer

Filter Rows

Answer

Group By

Question 2

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 3

Which THREE of the following are benefits of using a star schema in Power BI? (Select three.)

Accepted Answer

Simplified DAX formulas. Star schemas reduce the number of table joins and filter context complexity, which simplifies DAX formulas. Measures and calculated columns in Power BI rely on unambiguous relationships; a star schema ensures each fact table connects to dimension tables via one-to-many relationships, allowing DAX to propagate filters automatically without complex cross-filtering logic.

Answer

Supports many-to-many relationships natively

Answer

Increased data redundancy

Question 4

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. In Power Query, a common method to implement SCD Type 2 is to use a lookup table that tracks changes with start and end dates. This allows you to maintain historical versions of dimension attributes by recording the effective date range for each row, enabling accurate time-based analysis.

Answer

Merge the dimension table with a fact table

Answer

Update the existing row with new attributes

Answer

Overwrite the existing attributes

Question 5

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 6

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 7

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 CALENDARAUTO function in DAX automatically generates a contiguous set of dates that covers all dates present in the data model, ensuring accurate time intelligence calculations. Option A is incorrect because using the date column directly from the sales table can lead to gaps if data is missing, and it limits time intelligence functions. Option C is incorrect because importing from an Excel file is static, may not cover all dates, and requires manual updates. Option D is incorrect because while Power Query M can create date tables, DAX is generally more efficient and integrates seamlessly with time intelligence measures.

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 8

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. It allows analysis by ShipDate without breaking the existing relationship on OrderDate. Creating a second Date table and adding an inactive relationship to ShipDate lets you use the USERELATIONSHIP function in DAX measures to activate the ShipDate relationship when needed. Option A is wrong because combining OrderDate and ShipDate into a single column loses the original data and prevents separate analysis. Option C is wrong because using ShipDate directly without a date table limits time intelligence functions and star schema design. Option D is wrong because a fact table cannot have two active relationships to the same dimension table; only one active relationship is allowed, and multiple active relationships would cause ambiguity.

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 9

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 10

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. Power BI requires that the data types of columns used in a relationship match. In this scenario, Sales.ProductID is likely of a numeric type (int64) while Product.ProductID is of type string, so the relationship will fail. Option B is incorrect because column names do not need to be different; they can match. Option C is irrelevant because the relationship cannot be created at all due to data type mismatch. Option D is incorrect because the relationship does not work at all; it fails, not becomes 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 11

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 12

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 13

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 14

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. Options A and D are correct. Option A: Using integer-based foreign keys reduces storage requirements and improves join performance between fact and dimension tables. Option D: Hiding the fact table from report view prevents users from inadvertently querying it directly, which can cause performance issues; instead, users should use measures. Option B is incorrect because adding calculated columns to a large fact table increases model size and degrades performance. Option C is incorrect because enabling Auto Date/Time can create hidden date tables that increase model size unnecessarily; it is a general best practice but not specifically about large fact tables. Option E is incorrect because setting default summarization to 'Sum' on numeric columns in a fact table can lead to incorrect aggregations; measures should be used instead.

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 15

Which THREE considerations are important when implementing row-level security (RLS) in Power BI? (Select exactly 3.)

Accepted Answer

Roles can use DAX expressions to define filters. Options A, B, and D are correct. Option A: Roles in Power BI RLS use DAX expressions to define row-level filters. Option B: RLS dynamically filters data based on the user's identity (e.g., username). Option D: In DirectQuery mode, RLS filters are pushed to the source database for performance. Option C is incorrect because RLS is not automatically applied in Analyze in Excel; users see all data unless they have the appropriate role. Option E is incorrect because RLS controls row-level data access, not access to specific measures; measures can be hidden via other methods.

Answer

RLS is automatically applied when using Analyze in Excel

Answer

RLS can restrict access to specific measures