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

Question 1

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. Option C is correct. The code filters rows where Year >= 2020, groups by ProductID, and sums Amount per product. Option A is not grouped. Option B sums all rows into one. Option D is not grouped by product.

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 2

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. Option B is correct because USERELATIONSHIP activates the specified relationship. The relationship is defined by the columns Sales[DateKey] and Date[DateKey]; if this matches the ShipDateKey relationship, it uses ship dates. Option A is incorrect because USERELATIONSHIP overrides the active relationship. Option C is incorrect because it activates the specified relationship. Option D is incorrect because the expression does not ignore filters.

Answer

Both order and ship dates

Answer

Order dates

Answer

No relationship, all dates

Question 3

You have a Power BI data model with a Sales table and a Product table. You want to create a measure that calculates the percentage of total sales for each product category. Which DAX pattern should you use?

Accepted Answer

DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category]))). Option B is correct because DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category]))) calculates the percentage of total. Option A uses ALL(Product) which removes all filters on Product table. Option C uses ALLSELECTED which respects slicers. Option D uses REMOVEFILTERS on a column.

Answer

SUM(Sales[Amount]) / CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales))

Answer

SUM(Sales[Amount]) / SUM(Sales[Amount])

Answer

DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED()))

Question 4

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. Option C is correct because using DAX CALENDARAUTO ensures a contiguous set of dates covering all dates in the data model, which is essential for accurate time intelligence. Option A is wrong because importing from Excel may introduce gaps. Option B is wrong because Power Query M language can also create date tables, but DAX is typically more efficient for this purpose. Option D is wrong because using a date column directly from the fact table without a separate date table limits time intelligence functions.

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 5

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. Option D is correct because creating a second Date table with an inactive relationship for ShipDate allows you to use USERELATIONSHIP in measures to switch between active and inactive relationships as needed. Option A is wrong because multiple active relationships cause ambiguity and errors. Option B is wrong because merging ShipDate into a single column loses the original ship date information. Option C is wrong because using the ShipDate directly without a date table limits time intelligence.

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 6

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. Option B is correct because 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 'Single' only filters from the one side to the many side. Option C is wrong because 'None' disables cross filtering. Option D is wrong because 'Automatic' is not a valid setting in Power BI.

Answer

Automatic

Answer

None

Answer

Single

Question 7

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. Option C is correct because the data types do not match: Sales.ProductID is int64, while Product.ProductID is string. Power BI relationships require matching data types. Option A is wrong because the column names match, so no rename is needed. Option B is wrong because many-to-one is allowed, but the data type mismatch is the problem. Option D is wrong because the relationship direction is not the primary issue.

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 8

You have a Power BI model with a table named 'Orders' that contains columns: OrderID, CustomerID, OrderDate, and TotalAmount. You need to create a measure that calculates the total sales amount for orders placed in the last 30 days, but only for customers who have placed more than 5 orders in total. What is the most efficient DAX measure?

Accepted Answer

TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), KEEPFILTERS(Orders[OrderDate] > TODAY() - 30), KEEPFILTERS(CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5)). Option B is correct because it uses KEEPFILTERS to apply both the date filter and the customer-level condition within a single CALCULATE, ensuring that the row context for the customer filter is properly expanded via ALLEXCEPT without causing context transition issues. This approach is more efficient than using multiple FILTER iterators, as it leverages CALCULATE's filter modification capabilities to apply the conditions at the correct granularity.

Answer

TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), FILTER(Orders, Orders[OrderDate] > TODAY() - 30), FILTER(Orders, CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5))

Answer

TotalSalesLast30Days = SUMX(FILTER(Orders, Orders[OrderDate] > TODAY() - 30 && CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5), Orders[TotalAmount])

Answer

TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), DATESINPERIOD(Orders[OrderDate], TODAY(), -30, DAY))

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. Option A is correct because the different granularities can cause ambiguity in the relationship if not properly managed. When 'Sales' is filtered by a specific day, the relationship to 'Targets' may not filter correctly because 'Targets' is at month level. Option B is wrong because data type mismatch is not mentioned. Option C is wrong because many-to-many is not the issue; the relationship is likely many-to-one. Option D is wrong because bidirectional filtering is not necessary and could cause issues.

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 designing a data model for a report that shows sales by region and product category. The source data includes a table 'Sales' with columns: Region, Category, SalesAmount. You also have separate tables 'Regions' and 'Categories' that contain additional attributes. You need to create a star schema. What should you do with the 'Region' and 'Category' columns in the 'Sales' table?

Accepted Answer

Remove them from the Sales table and use foreign keys to link to the dimension tables. Option C is correct because in a star schema, dimension tables contain the attributes, and fact tables contain foreign keys. Removing the columns and using foreign keys normalizes the model. Option A is wrong because keeping them duplicates data and increases model size. Option B is wrong because merging them is not necessary and can complicate the model. Option D is wrong because marking as date tables is irrelevant for region and category.

Answer

Keep them in the Sales table as attributes for simplicity

Answer

Merge the Regions and Categories tables into the Sales table

Answer

Mark the Sales table as a date table

Question 12

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. Option B is correct because 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 13

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 C are correct. Option A: Using integer foreign keys reduces storage and improves join performance. Option C: Hiding the fact table from report view prevents users from accidentally querying it directly, promoting use of measures. Option B is wrong because creating calculated columns in the fact table can increase model size and degrade performance. Option D is wrong because default summarization is not recommended for fact tables; measures should be used instead. Option E is wrong because disabling auto date/time is a general best practice but not specific to large fact tables.

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 14

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: RLS filters data at the row level based on the user's role. Option B: Roles can be defined using DAX expressions that filter tables. Option D: RLS is enforced in DirectQuery mode, but performance depends on the source. Option C is wrong because RLS does not restrict access to measures; measures are still visible unless explicitly hidden. Option E is wrong because RLS is not applied when users view data in Power BI Desktop; it is only applied when published to the service.

Answer

RLS is automatically applied when using Analyze in Excel

Answer

RLS can restrict access to specific measures

Question 15

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. Options B and D are correct. Option B: Star schema simplifies the model and improves query performance because of fewer joins and a clear structure. Option D: Star schema reduces model size by normalizing dimensions and avoiding duplication. Option A is wrong because star schema does not inherently reduce the number of tables; it typically increases them compared to a flat table. Option C is wrong because star schema supports many-to-one relationships, not many-to-many directly. Option E is wrong because star schema is not specifically required for time intelligence; a date table is needed, but star schema is broader.

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

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

Exhibit

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

Exhibit