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

Question 1

You are designing a Power BI model for a multinational company. The fact table contains sales data in multiple currencies. You need to convert all amounts to a single reporting currency (USD) using exchange rates from a separate table. What is the recommended approach?

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

Accepted Answer

Create a relationship between the fact table and the exchange rate table on date and currency, then create a measure using SUMX with rate lookup. A measure using SUMX with LOOKUPVALUE or related exchange rates dynamically converts amounts based on the context. Option B is correct because it uses relationships and a calculated measure that respects filter context. Option A is wrong because a calculated column would not respond to filter context and would require manual updates if rates change. Option C is wrong because hard-coding the rate in a measure is not dynamic and would require manual maintenance for each currency. Option D is wrong because merging in Power Query creates a static column that does not automatically reflect new exchange rates.

Answer

Add a calculated column to the fact table that multiplies by the exchange rate from a separate table

Answer

Hard-code the exchange rate in a measure

Answer

Use Power Query to merge the exchange rate table into the fact table during data load

Question 2

Which THREE of the following are valid reasons to use a calculated column instead of a measure in Power BI?

Accepted Answer

The value is needed in a row-level security rule. A, C, D are correct. Option A is correct because calculated columns are evaluated row-by-row during data refresh and are available for row-level security rules, which require a static value per row. Option C is correct because calculated columns can be used to create relationships between tables, whereas measures cannot be used in relationships. Option D is correct because calculated columns can act as slicers or filters in visuals since they are part of the data model. Option B is wrong because aggregations that change with user interaction (like SUM with slicers) should be measures to respond to filter context dynamically. Option E is wrong because time intelligence calculations typically depend on filter context and are more efficiently handled with measures that leverage DAX time intelligence functions.

Answer

The value is an aggregation (e.g., SUM) that changes with user interaction

Answer

The value is a time intelligence calculation that depends on the current filter context

Question 3

You run the above DAX query in DAX Studio. The result set shows a row for each Category and Year combination, but the Total Sales and Total Cost columns are both blank. What is the most likely reason?

Accepted Answer

There is no active relationship between the Sales table and the Product and Date tables. SUMMARIZECOLUMNS automatically adds filter context based on the columns used. If there is no active relationship between the Sales table and the Product or Date tables, the filter context from Category and Year cannot propagate to Sales, resulting in blank aggregates. Option D is correct. Option A is incorrect because if the Sales[Amount] column contained only BLANK values, only Total Sales would be blank, but here both Total Sales and Total Cost are blank, indicating a broader relationship issue. Option B is incorrect because SUMMARIZECOLUMNS can compute multiple aggregate columns. Option C is incorrect because the ORDER BY clause does not affect aggregation; it only sorts results after aggregation.

Answer

The Sales[Amount] column contains only BLANK values

Answer

SUMMARIZECOLUMNS cannot compute two aggregate columns

Answer

The ORDER BY clause is causing the aggregation to fail

Question 4

You are modeling a data source that includes a table with columns: EmployeeID, ManagerID. You need to create a hierarchy that shows the reporting structure. Which Power BI feature should you use?

Accepted Answer

Use DAX PATH and PATHITEM functions to create a calculated hierarchy table. Parent-child hierarchy functions, such as PATH, can be used to flatten a hierarchy. Option C is correct. Option A is wrong because relationships are for separate tables. Option B is wrong because calculated columns alone cannot create a hierarchy. Option D is wrong because grouping is for binning values.

Answer

Create a self-referencing relationship

Answer

Add a calculated column using IF statements

Answer

Use the 'Group' feature in Power Query

Question 5

You are reviewing a Power BI model definition. The Sales table has a partition with an M expression. You need to ensure that the table can be refreshed incrementally. Which additional property must be set?

Accepted Answer

Set the 'Mode' property of the table to 'Import' and define a range partition. For incremental refresh to work, the table must be in Import mode and have range partitions defined using the incremental refresh policy. Option D correctly identifies this: setting the Mode to Import and defining a range partition is essential. Option A is incorrect because annotations are not required for incremental refresh; they are optional. Option B is incorrect because adding more partitions does not enable incremental refresh; it is about partitioning strategy. Option C is incorrect because changing the data type of OrderDate to Date might be necessary for proper filtering but is not the additional property that must be set to enable incremental refresh.

Answer

Add an annotation to enable incremental refresh

Answer

Change the data type of OrderDate to Date

Answer

Add more partitions to the table

Question 6

You are building a star schema in Power BI. The Sales table contains sales amount, quantity, and discount. You need to create a relationship between Sales and a Date table. The Date table must have a one-to-many relationship with Sales, and all date filters must show sales even if no sales occurred on that date. Which relationship configuration should you use?

Accepted Answer

One-to-many, single direction (Date filters Sales). A one-to-many relationship with the Date table on the 'one' side and the Sales table on the 'many' side, with single direction cross-filter direction from Date to Sales, ensures that when the Date table is used as a filter, all dates are shown regardless of whether sales occurred. This configuration meets the requirement of showing sales even if no sales occurred on a date because the filter originates from the Date table. Option A is incorrect because it sets the filter direction from Sales to Date, which would hide dates with no sales. Option C is incorrect because bidirectional filtering is unnecessary and can cause ambiguity. Option D is incorrect because many-to-one reverses the cardinality, which is not appropriate when Date is the dimension table.

Answer

One-to-many, single direction (Sales filters Date)

Answer

One-to-many, both directions

Answer

Many-to-one, single direction (Date filters Sales)

Question 7

You have a Power BI semantic model with a fact table 'Orders' and dimension tables 'Customer', 'Product', and 'Date'. The model uses DirectQuery against Azure Synapse. Users report that a measure calculating Year-over-Year growth returns incorrect values for the current year when no data exists for the prior year. Which DAX modification ensures the measure returns blank instead of an incorrect value?

Accepted Answer

Add an IF condition to check if prior year has any rows before computing growth. Adding an IF condition to check if the prior year has any rows (e.g., using IF(COUNTROWS(...)>0, ...)) ensures that the measure returns blank when no prior year data exists, preventing incorrect growth calculations. Option B is incorrect because DIVIDE with an alternative result of 0 returns 0 instead of blank, which does not handle the absence of prior year rows. Option C is incorrect because ISBLANK checks only if the current year value is blank, not whether prior year data exists. Option D is incorrect because COALESCE replaces blanks with a non-blank value, which would still produce an incorrect result if prior year is missing.

Answer

Use DIVIDE with an alternative result of 0

Answer

Use ISBLANK to check the current year value

Answer

Use COALESCE to replace blank with 0

Question 8

You are designing a Power BI data model for a retail company. The model must include a table with product prices that change over time. Which table design should you use to support historical price analysis?

Accepted Answer

Create a separate dimension table for price with effective date ranges and relate it to the fact table. A separate dimension table with effective date ranges (Slowly Changing Dimension Type 2) allows tracking price changes over time and can be related to the fact table via date. Option A is wrong because a separate table without a relationship cannot be used for analysis. Option B is wrong because updating the price column in the product dimension (Type 1) overwrites historical prices. Option D is wrong because storing only the current price in the fact table and overwriting it loses history.

Answer

Create a separate table for price changes without any relationship

Answer

Add a price column to the product dimension and update it when price changes

Answer

Store the current price in the fact table and overwrite when price changes

Question 9

You are modeling data from multiple sales regions. Each region has a unique ID, but region names might be spelled inconsistently (e.g., 'North America' vs. 'N. America'). You need to create a single dimension table for regions. What is the best practice to handle this?

Accepted Answer

Use Power Query to clean and standardize region names before loading. Use Power Query to clean and standardize region names before loading. This ensures consistency and avoids data quality issues. Option A is wrong because keeping both spellings and using a many-to-many relationship would complicate the model and lead to incorrect aggregations. Option C is wrong because hiding the region column does not fix the underlying inconsistency; it still causes issues when using region names in visuals. Option D is wrong because a bridge table is unnecessary and adds complexity; standardizing the data is simpler.

Answer

Keep both spellings in the dimension and use a many-to-many relationship

Answer

Hide the region column and use only region ID in visuals

Answer

Use a bridge table to map both spellings to a single region ID

Question 10

You have a Power BI model with a measure that calculates total sales using SUMX over a filtered table. The measure performs slowly. Which change will most likely improve performance without altering business logic?

Accepted Answer

Rewrite the measure using CALCULATE with a simple SUM instead of SUMX. Replacing the iterative SUMX with a base SUM measure applied via CALCULATE leverages the storage engine's columnar aggregation, which is faster than row-by-row iteration. Option A is incorrect because KEEPFILTERS adds unnecessary overhead and does not improve performance. Option C is incorrect because switching to DirectQuery typically reduces performance for aggregations. Option D is incorrect because removing all filter functions would alter the business logic by discarding necessary filters.

Answer

Add KEEPFILTERS to preserve existing filters

Answer

Change the storage mode of the table from Import to DirectQuery

Answer

Remove all filter functions from the measure

Question 11

You are designing a data model for a sales analysis. The 'Sales' fact table contains columns: OrderDate, ShipDate, Amount, Quantity. You need to analyze sales by both order date and ship date. What is the best practice to model the dates?

Accepted Answer

Create two date tables: one for OrderDate (active) and one for ShipDate (inactive), using USERELATIONSHIP as needed. Create two date tables (one active, one inactive) and use USERELATIONSHIP to switch between them. Option B is wrong because a separate date table for both roles still requires two relationships; it's better to have one active and one inactive. Option C is wrong because merging the two date columns into one loses the distinction between order and ship dates. Option D is wrong because using a single date table with separate measures doesn't allow proper date filtering for both columns.

Answer

Put the date keys in a separate table and relate to both

Answer

Create a single date table and merge both date columns into one

Answer

Use only one date table and create separate measures for each date

Question 12

You are modeling a fact table with granularity at the order line level. The table includes columns: OrderID, ProductID, Quantity, UnitPrice, Discount. You need to create a measure for total revenue considering discounts (Quantity * UnitPrice * (1 - Discount)). Some orders have no discount (null). What is the correct DAX for this measure?

Accepted Answer

SUMX('Orders', Orders[Quantity] * Orders[UnitPrice] * (1 - COALESCE(Orders[Discount], 0))). It uses COALESCE to treat null discounts as 0, ensuring the calculation works for all orders. Option B fails to handle null discounts, causing errors or incorrect results when Discount is null. Option C uses an incorrect formula (dividing by 1+Discount). Option A ignores discounts entirely.

Answer

SUM('Orders'[Quantity] * 'Orders'[UnitPrice])

Answer

SUMX('Orders', Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[Discount]))

Answer

SUMX('Orders', DIVIDE(Orders[Quantity] * Orders[UnitPrice], 1 + Orders[Discount]))

Question 13

You need to create a relationship between two tables where one table has duplicate values on the key column. Which cardinality should you choose?

Accepted Answer

Many-to-one. Many-to-one cardinality is used when the lookup table (the 'one' side) has unique values and the fact table (the 'many' side) has duplicate values in the key column. This is the typical relationship in a star schema where a dimension table (unique keys) relates to a fact table (duplicate keys). Option A is incorrect because one-to-one requires unique keys on both sides. Option B is incorrect because many-to-many is used when both tables have duplicate values. Option D is incorrect because one-to-many is the reverse relationship (where the other table has duplicates).

Answer

One-to-one

Answer

Many-to-many

Answer

One-to-many

Question 14

You are modeling data with a many-to-many relationship between 'Student' and 'Class' tables. Which THREE configurations can handle this scenario?

Accepted Answer

Use CROSSFILTER function to enable many-to-many filtering. Options A, C, and E are correct. Option A uses CROSSFILTER to enable many-to-many filtering. Option C creates a bridge table with StudentID and ClassID, which is the standard approach. Option E uses a calculated column to concatenate Class names in the Student table, useful for limited scenarios. Option B is wrong because removing duplicates loses information. Option D is wrong because changing to one-to-many incorrectly assumes uniqueness.

Answer

Remove duplicate rows from both tables

Answer

Change relationship to one-to-many by creating a unique key

Question 15

You are creating a star schema in Power BI. Which TWO tables are typically dimension tables?

Accepted Answer

Product. (Product) and Option C (Date) are correct because they are typical dimension tables that provide descriptive attributes for analysis. Option A (TransactionDetails) is wrong because it contains transactional data and is a fact table. Option D (InventoryTransactions) is wrong because it is also a fact table recording inventory movements. Option E (Sales) is wrong because it is a fact table capturing sales transactions.

Answer

TransactionDetails

Answer

InventoryTransactions

Answer

Sales