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

Question 1

You have a Power BI model with a date table. You need to ensure that time intelligence functions like TOTALYTD work correctly. What is the most important requirement for the date table?

Accepted Answer

The date table must be marked as a date table in Power BI. Power BI requires that a date table be explicitly marked as a date table via the 'Mark as Date Table' option in the Table tools ribbon. This action tells Power BI that the table contains unique, non-blank date values that can be used by time intelligence functions like TOTALYTD, ensuring they reference the correct date column for calculations such as year-to-date aggregation.

Answer

All date columns must be visible

Answer

The date table must have a single date column

Answer

The date table must contain continuous dates without gaps

Question 2

Refer to the exhibit. You have the model shown. The active relationship is between OrderDate and Date. You create the measure TotalShipments. What does this measure calculate?

Accepted Answer

Sum of Amount filtered by ShipDate. The measure TotalShipments uses the active relationship between OrderDate and Date, but the CALCULATE function or filter context in the measure applies a filter on ShipDate. Because the active relationship is based on OrderDate, any filter on ShipDate requires the measure to use the inactive relationship (via USERELATIONSHIP or by default crossing the active relationship). The correct answer is D because the measure sums Amount filtered by ShipDate, leveraging the inactive relationship to evaluate shipments based on their actual ship date.

Answer

Sum of Amount where ShipDate is not blank

Answer

Sum of all Amount regardless of date

Answer

Sum of Amount filtered by OrderDate

Question 3

You have a Power BI model containing a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. You need to create a measure that calculates the total amount from the previous year. Which DAX function should be used?

Accepted Answer

PREVIOUSYEAR. The PREVIOUSYEAR function is specifically designed to return a table of dates for the previous year relative to the current context in the filter context. It is the most direct and appropriate DAX function for calculating total amount from the previous year when working with a standard calendar date column.

Answer

DATEADD

Answer

PARALLELPERIOD

Answer

SAMEPERIODLASTYEAR

Question 4

You are modeling a many-to-many relationship between 'Student' and 'Class' tables. Which approach should you use in Power BI to handle this?

Accepted Answer

Add a bridge table with composite keys. A many-to-many relationship in Power BI requires a bridge (or junction) table that contains composite keys (e.g., StudentID and ClassID) to resolve the relationship into two one-to-many relationships. This allows Power BI to properly filter and aggregate data across both tables without ambiguity.

Answer

Merge both tables into one

Answer

Use bidirectional cross-filtering

Answer

Create a single one-to-many relationship

Question 5

You have a fact table 'Transactions' with 100 million rows. You need to reduce the model size without losing detail. Which action is most effective?

Accepted Answer

Remove columns that are not used in reports. Removing unnecessary columns reduces storage. Option B is wrong because aggregations reduce detail. Option C is wrong because composite models still store data. Option D is wrong because grouping is not the best for size reduction.

Answer

Group rows by date and sum amounts

Answer

Use a composite model with DirectQuery

Answer

Create aggregations on the table

Question 6

You have a Power BI model with a table 'Sales' and a related 'Product' table. You want to count the number of distinct products sold. Which DAX expression should you use?

Accepted Answer

DISTINCTCOUNT(Sales[ProductID]). DISTINCTCOUNT(Sales[ProductID]) returns the number of unique ProductID values in the Sales table, which directly answers the requirement to count distinct products sold. This function counts each distinct value in the specified column, ignoring duplicates, and is the standard DAX measure for distinct count calculations.

Answer

COUNTROWS(Sales)

Answer

COUNT(Sales[ProductID])

Answer

COUNTA(Sales[ProductID])

Question 7

Which THREE are best practices for designing a star schema in Power BI?

Accepted Answer

Create a separate date dimension table. Options A, C, and D are correct. A separate date dimension table enables consistent date-based analysis across multiple fact tables. Surrogate keys in dimension tables avoid issues with changing business keys and improve join performance. Storing measures at the lowest grain in fact tables ensures accurate aggregations. Option B is wrong because multiple relationships between the same fact and dimension tables create ambiguity; a single active relationship is recommended. Option E is wrong because composite keys in fact tables complicate joins and reduce performance; surrogate keys are preferred.

Answer

Create multiple relationships between fact and dimension

Answer

Use composite keys in fact tables

Question 8

Which TWO DAX functions can be used to create a calculated table in Power BI?

Accepted Answer

FILTER. Options B and D are correct. FILTER returns a table filtered by a condition, and SUMMARIZECOLUMNS returns a summarized table. Options A, C, and E are wrong because SELECTEDVALUE, CALCULATE, and SUMX all return scalar values, not tables.

Answer

SELECTEDVALUE

Answer

CALCULATE

Answer

SUMX

Question 9

Refer to the exhibit. You apply the row-level security (RLS) policy shown to the Sales table. Which rows will be visible to users?

Accepted Answer

Only rows where Amount > 1000. The RLS filter `Sales[Amount] > 1000` retains only rows where Amount exceeds 1000. Option A is incorrect because it suggests rows with non-blank Amount, but the filter explicitly requires >1000. Option B is incorrect because not all rows satisfy the condition. Option C is incorrect because it reverses the condition (<=1000) and would hide the correct rows.

Answer

Rows where Amount is not blank

Answer

All rows in the Sales table

Answer

Only rows where Amount <= 1000

Question 10

Refer to the exhibit. What is the result of this DAX expression?

Accepted Answer

Sum of Amount only for the West region. The DAX expression uses the CALCULATE function to add a filter condition (Region = 'West') to the filter context. This filter restricts the calculation to only rows where the Region is 'West'. Since CALCULATE does not remove other filters but applies the new filter over existing ones, the result is the sum of Amount exclusively for the West region. Options that describe sums for other regions or all regions are incorrect because the filter context is modified to include only the West region.

Answer

Sum of Amount for all regions except West

Answer

Sum of Amount for regions other than West

Answer

Sum of Amount for all regions

Question 11

Refer to the exhibit. You write this Power Query M code to import data. What does the final step do?

Accepted Answer

Filters rows where Amount > 100. The final step in the Power Query M code filters the table to keep only rows where the 'Amount' column value is greater than 100. This is achieved by the `Table.SelectRows` function with the condition `each [Amount] > 100`, which evaluates each row and retains those meeting the predicate. The result is a reduced dataset containing only transactions with an Amount exceeding 100.

Answer

Sorts rows by Amount descending

Answer

Groups rows by Amount

Answer

Removes the Amount column

Question 12

You are designing a star schema for a sales analysis report. The source data includes Order Details, Products, Customers, and Dates. Which table should be the fact table?

Accepted Answer

Order Details. In a star schema, the fact table stores quantitative, measurable data (metrics) and foreign keys linking to dimension tables. Order Details contains sales transactions with measures like quantity and revenue, making it the correct fact table for sales analysis.

Answer

Dates

Answer

Customers

Answer

Products

Question 13

You need to create a calculated column that categorizes sales amounts into 'Low', 'Medium', and 'High' based on thresholds. Which DAX function should you use?

Accepted Answer

SWITCH. The SWITCH function is the correct choice because it evaluates an expression against a list of values and returns the corresponding result, making it ideal for categorizing sales amounts into 'Low', 'Medium', and 'High' based on thresholds. Unlike nested IF statements, SWITCH is more readable and efficient for multiple conditions, as it allows you to define ranges using logical operators within the expression parameter.

Answer

LOOKUPVALUE

Answer

CALCULATE

Answer

IF

Question 14

You are designing a Power BI data model for a retail chain. You have three fact tables: Sales, Inventory, and Returns. Which modeling approach is best for cross-filtering between these facts?

Accepted Answer

Use role-playing dimensions. Role-playing dimensions allow sharing common dimensions like Date, Product, Store. Option A causes ambiguity. Option C is not a standard approach. Option D is unnecessary if dimensions are shared.

Answer

Use calculated columns to link facts

Answer

Merge all fact tables into one

Answer

Create separate dimensions for each fact table

Question 15

You need to create a hierarchy in Power BI that allows drilling down from Year to Quarter to Month. What is the correct approach?

Accepted Answer

Create a hierarchy in the Date table using Year, Quarter, Month columns. The correct approach is to create a hierarchy in the Date table using the Year, Quarter, and Month columns. This enables drill-down from year to quarter to month. Option A is incorrect because measures do not create hierarchies. Option B is incorrect because the Drillthrough feature navigates to a different page, not within the same visual's hierarchy. Option C is incorrect because DATESYTD is a time intelligence DAX function for year-to-date calculations, not for creating drill-down hierarchies.

Answer

Create separate measures for each level

Answer

Use the Drillthrough feature

Answer

Use the DATESYTD function