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

Question 1

You are building a star schema in Power BI. Which table design best supports filtering a sales fact table by product category?

Accepted Answer

Create a separate Product dimension table with category, related to Sales by ProductID.. Option C is correct because a star schema requires a dedicated Product dimension table containing descriptive attributes such as category, joined to the Sales fact table via the ProductID key in a one-to-many relationship; this lets slicers and filters on category propagate to the fact table efficiently. Option A is wrong because a single flat table is a denormalized design, not a star schema, and it increases redundancy and reduces filter performance. Option B is wrong because appending rows merges tables vertically (union), which does not create the dimension-to-fact relationship needed for filtering. Option D is wrong because storing category directly in the fact table duplicates descriptive data and prevents clean dimension-based filtering.

Answer

Use a single table containing all sales and product attributes.

Answer

Merge Sales and Product tables into one by appending rows.

Answer

Store product category in the Sales fact table.

Question 2

You have a date table with columns: Date, Year, Month, Quarter, Day. To enable time intelligence functions like TOTALYTD, what is the minimum requirement?

Accepted Answer

A separate date table with a continuous range of dates and a relationship to the fact table.. The correct answer is A: a separate date table with a continuous range of dates and a relationship to the fact table. Time intelligence functions like TOTALYTD require a dedicated date table that contains an unbroken sequence of dates (no gaps) and is related to the fact table, so the engine can correctly filter and aggregate across time periods. Option B is insufficient because marking a table as a date table is a helpful setting but does not by itself satisfy the structural requirement of a continuous date range and relationship. Option C is wrong because merging date and fact tables into one table breaks the star schema and does not provide the separate date dimension time intelligence needs. Option D is wrong because a single date column in the fact table lacks the continuous, dedicated date dimension required for functions like TOTALYTD.

Answer

Mark the date table as a date table in Power BI.

Answer

Combine date and fact tables into one table.

Answer

A single date column in the fact table.

Question 3

You need to create a calculated column that categorizes products based on price: 'Low' (<$10), 'Medium' ($10-$50), 'High' (>$50). Which DAX expression should you use?

Accepted Answer

SWITCH(TRUE(), Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High"). Option C is correct because SWITCH(TRUE(), ...) evaluates each condition in order and returns the first TRUE result, so Product[Price] < 10 yields "Low", Product[Price] <= 50 yields "Medium", and the fallback "High" covers prices above 50. This pattern is the standard DAX way to implement multi-branch conditional logic with range comparisons. Option A is wrong because SWITCH with a scalar expression compares for equality, not ranges, so <10 and <=50 are not valid match values. Option B is wrong because LOOKUPVALUE retrieves a value from another table by matching columns and does not perform range-based categorization. Option D is wrong because DAX IF only takes three arguments (condition, true result, false result), so passing five arguments is invalid.

Answer

SWITCH(Product[Price], <10, "Low", <=50, "Medium", "High")

Answer

LOOKUPVALUE(Category, Product[Price], Product[Price])

Answer

IF(Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High")

Question 4

When creating a many-to-many relationship between two tables, what is a common approach to model this in Power BI?

Accepted Answer

Introduce a bridge table that contains the unique combinations.. The correct answer is C: introduce a bridge table that contains the unique combinations, because Power BI's recommended pattern for many-to-many relationships is to create a junction (bridge) table with one row per unique pairing of the two entity keys, then relate each original table to the bridge via one-to-many relationships. This keeps the model star-schema-like, avoids ambiguous filter paths, and lets DAX propagate filters correctly across both dimensions. Option A is wrong because CROSSJOIN in a calculated table just produces a Cartesian product of all rows and does not establish a proper relational model. Option B is wrong because merging both tables into one denormalized table destroys the separate entities and causes duplication and aggregation errors. Option D is wrong because although Power BI can technically create a direct many-to-many relationship, it is not the common or recommended modeling approach and can produce ambiguous or incorrect results.

Answer

Use the CROSSJOIN DAX function in calculated tables.

Answer

Merge both tables into a single table.

Answer

Create a direct many-to-many relationship in the model.

Question 5

Which TWO approaches can you use to implement row-level security (RLS) in Power BI?

Accepted Answer

Create roles with static filters.. Option D is correct because creating roles with static filters is a core RLS implementation method in Power BI: in Power BI Desktop you define roles in Manage Roles and add DAX filter expressions on tables (for example, [Region] = "West"), then assign users or groups to those roles in the Power BI service, so each user sees only the rows matching the filter. Option E is correct because dynamic filters based on USERNAME() (or USERPRINCIPALNAME()) implement RLS by evaluating the signed-in user at query time, typically via a relationship to a security/identity table, so row visibility adapts automatically without creating a separate static role per user. Option A is not a valid RLS approach because assigning users to security groups in the dataset is a membership/administration step, not a row-filtering mechanism by itself. Option B is wrong because column-level security restricts which columns are visible, not which rows. Option C is wrong because object-level security restricts access to tables and columns (metadata), not rows.

Answer

Assign users to security groups in the dataset.

Answer

Use column-level security to restrict row visibility.

Answer

Use object-level security to restrict rows.

Question 6

You have a table with a column 'Date' and a measure 'Total Sales'. You want to calculate the cumulative total of sales over time. Which DAX function should you use?

Accepted Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]). TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]) is correct because it is a time-intelligence function that evaluates the sum expression over the year-to-date period ending at the latest date in the current filter context, producing a cumulative total over time. It takes the aggregation and a date column as arguments and automatically applies the necessary date filtering. SUM(Sales[Amount]) alone returns only the total for the current context, not a cumulative value. DATESYTD returns a table of dates rather than a scalar cumulative total, and CALCULATE(SUM(Sales[Amount]), ALL(Sales)) removes filters to give a grand total, not a running cumulative total.

Answer

SUM(Sales[Amount])

Answer

DATESYTD('Date'[Date])

Answer

CALCULATE(SUM(Sales[Amount]), ALL(Sales))

Question 7

You are designing a data model for a report that shows sales by product category and by month. Which table configuration is most efficient?

Accepted Answer

A date dimension table, a product dimension table, and a sales fact table.. Option B is correct because a star schema with a date dimension, a product dimension, and a sales fact table is the most efficient design for reporting sales by product category and by month. The fact table stores numeric measures such as sales amount at the grain of each transaction or monthly aggregate, while the dimension tables provide descriptive attributes for filtering and grouping. This separation reduces redundancy, improves query performance, and supports clean time intelligence and category-level analysis. Option A is wrong because date and product are descriptive dimensions, not fact tables. Option C is wrong because a single wide table causes redundancy and poor scalability. Option D is wrong because combining date and product attributes into one dimension creates a snowflake-like or denormalized structure that complicates grouping and time analysis.

Answer

Two fact tables: one for date and one for product.

Answer

A single table with all columns.

Answer

One dimension table containing date and product attributes.

Question 8

You are designing a data model that will support self-service analytics. You have a Sales table with over 100 million rows. Which of the following modeling approaches would provide the best query performance while maintaining a user-friendly experience?

Accepted Answer

Design a star schema with a fact table and dimension tables. A star schema with a fact table and dimension tables is correct because it minimizes the number of joins needed for queries, allowing the engine to scan a narrow fact table and use smaller dimension tables for filtering and grouping, which delivers the best query performance at scale (100M+ rows) while keeping the model intuitive for self-service users. The denormalized dimensions also make it easy for business users to drag and drop fields without navigating complex relationships. A single flat table (B) or a Power Query merge into one table (C) creates a very wide table with redundant data, increasing storage and scan time and hurting performance. A snowflake schema (D) normalizes dimensions into multiple related tables, adding extra joins that slow queries and complicate the user experience.

Answer

Create a single flat table with all columns

Answer

Import all tables into a single table using Power Query merge

Answer

Use a snowflake schema with multiple levels of dimensions

Question 9

You are creating a Power BI report that uses a composite model (DirectQuery for large tables and Import for small dimension tables). You want to ensure that measures referencing the DirectQuery tables are responsive. Which of the following design choices should you avoid?

Accepted Answer

Use complex time intelligence measures that iterate over the fact table. The design choice to avoid is A: complex time intelligence measures that iterate over the fact table, because in a composite model the DirectQuery fact table is queried remotely, and row-by-row iteration (e.g., SUMX over the fact table) forces expensive, non-foldable queries that hurt responsiveness. Measures that aggregate columns (B) can often be folded into a single SQL GROUP BY, so they are efficient. Importing a date dimension (C) is a recommended pattern because it keeps time intelligence calculations local and foldable. User-defined aggregations (D) are also recommended, as they cache pre-aggregated DirectQuery data and improve query performance.

Answer

Use measures that aggregate columns rather than rows

Answer

Create a date dimension table imported from the source

Answer

Create user-defined aggregations for the DirectQuery table

Question 10

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. Option A is correct because row-level security rules in Power BI (DAX filter expressions on tables) are evaluated at the row level and can reference calculated columns, whereas measures cannot be used directly in RLS filter predicates. Option C is correct because relationships between tables require a column on the "one" or "many" side, and calculated columns are stored in the model and can serve as relationship keys, while measures cannot participate in relationships. Option D is correct because slicers and filter fields require column values to populate their item lists, and a calculated column materializes those values at row level so it can be placed on the slicer or filter well, unlike a measure. Option B is not correct because aggregations that respond to user interaction are precisely the role of measures, which are evaluated dynamically per filter context. Option E is not correct because time intelligence calculations depending on the current filter context are dynamic and should be implemented as measures, not calculated columns, which are computed at refresh time and cannot adapt to slicer or filter changes.

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 11

Which TWO of the following are true about using the 'Mark as Date Table' feature in Power BI?

Accepted Answer

The date table must have a column with unique date values. Option C is correct because 'Mark as Date Table' requires you to designate a column that contains unique, contiguous date values with no gaps or duplicates, which Power BI uses as the basis for time-based calculations. Option E is correct because marking a table as a date table lets Power BI apply built-in time intelligence functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATESYTD correctly against that table's date column. Option A is wrong because marking a date table is not required for every relationship involving a date column; it is only needed when you want proper time intelligence behavior. Option B is wrong because the marked date column must contain unique values, so duplicates are not allowed. Option D is wrong because 'Mark as Date Table' does not generate Year, Quarter, Month, or Day columns; those must already exist in the table or be created separately.

Answer

It is required for any relationship involving a date column

Answer

The date column can contain duplicate dates

Answer

It automatically creates all date hierarchy columns (Year, Quarter, Month, Day)

Question 12

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. Option C is correct because a slowly changing dimension (Type 2) with effective date ranges preserves each historical price version and lets the fact table relate to the price that was valid at the time of each transaction, enabling accurate historical price analysis. Option B fails because updating a single price column in the product dimension overwrites history, so past transactions would reflect the current price rather than the price at sale time. Option A is wrong because an unrelated price-change table cannot be joined to the fact table, so no historical price context can be applied. Option D is also wrong because storing and overwriting the current price in the fact table destroys prior values and prevents any trend or historical comparison.

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 13

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. Option B is correct because Power Query is the proper ETL layer in Power BI for cleaning and standardizing inconsistent text values such as 'North America' vs. 'N. America' before the data reaches the model, ensuring a single conformed region name per region ID in the dimension table. This keeps the dimension table clean, avoids ambiguous relationships, and lets visuals aggregate correctly by region. Option A is wrong because keeping duplicate spellings and a many-to-many relationship introduces ambiguity and incorrect totals rather than resolving the inconsistency. Option C is wrong because hiding the region column and using only region ID does not create a usable region name dimension and leaves the underlying inconsistency unresolved. Option D is wrong because a bridge table is used to resolve many-to-many relationships between dimensions and facts, not to fix spelling variants that should be standardized upstream.

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 14

Your Power BI model includes a calculated column that concatenates first and last name. Users report that the column shows blank for some rows. The data source has no nulls. What is the most likely cause?

Accepted Answer

One of the columns contains only spaces. If a source column contains only spaces (or empty strings that appear as spaces), concatenating it with another column can produce a result that appears blank or contains only spaces. Power BI's concatenation does not trim whitespace, so the resulting column may display as blank in visuals.

Answer

Data type mismatch between the two columns

Answer

The columns are from different tables without a relationship

Answer

The relationship between tables is set to single direction

Question 15

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))). Option D is correct because it uses SUMX to iterate the order-line rows and computes Quantity * UnitPrice * (1 - Discount) per row, while COALESCE(Orders[Discount], 0) replaces null discounts with 0 so lines without a discount are not excluded or turned into blanks. This matches the required revenue formula and handles the stated null-discount scenario. Option A is wrong because it multiplies column totals rather than row-level values and ignores Discount entirely. Option B is wrong because a null Discount makes the whole row expression blank, dropping those order lines from the total. Option C is wrong because it divides by (1 + Discount) instead of multiplying by (1 - Discount), producing an incorrect revenue calculation.

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]))