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

Question 1

Refer to the exhibit. A user is a member of both 'Manager' and 'Executive' roles. Which rows will the user see?

Accepted Answer

Rows where Department is 'Sales' or 'Marketing'.. Option B is correct because when a user belongs to multiple roles, the filters are combined with OR. So the user sees rows where Department is 'Sales' (from Manager) OR Department is 'Sales' or 'Marketing' (from Executive). The union is Sales and Marketing rows. Option A is wrong because it ignores the Executive role. Option C is wrong because it shows only Sales. Option D is wrong because it shows all departments.

Answer

Only rows where Department is 'Sales'.

Answer

All rows, because role membership overrides filters.

Answer

Rows where Department is 'Sales' only, but with both role permissions.

Question 2

You have a table with columns 'Product', 'Category', and 'Sales'. You want to create a hierarchy that allows users to drill down from Category to Product. Which is the correct order to create the hierarchy?

Accepted Answer

Category -> Product. Option A is correct because hierarchies drill down from the highest level to the lowest. Option B is reverse. Option C is wrong because Sales is a measure, not a level. Option D is wrong because Product should be below Category.

Answer

Product -> Category -> Sales

Answer

Product -> Category

Answer

Sales -> Product -> Category

Question 3

You are modeling data for a retail company. You have a 'Transactions' fact table with millions of rows. You need to improve query performance for a report that filters by 'Store' and 'Date'. Which action will have the most impact?

Accepted Answer

Ensure the date table is marked as a date table and has a continuous date range.. Option C is correct because marking the date table improves time intelligence and performance. Option A is wrong because composite models add complexity. Option B is wrong because removing the date table would break time intelligence. Option D is wrong because merging tables increases size.

Answer

Merge the 'Store' and 'Transactions' tables into one.

Answer

Use a composite model with DirectQuery for the fact table.

Answer

Remove the date table and use the date column directly in the fact table.

Question 4

You are designing a star schema for a sales data model. Which table should be defined as a dimension table?

Accepted Answer

Date. Option B is correct because Date is a classic dimension used for time-based analysis. Option A is wrong because SalesAmount is a measure. Option C is wrong because TransactionID is a fact table key. Option D is wrong because OrderQuantity is a measure.

Answer

TransactionID

Answer

OrderQuantity

Answer

SalesAmount

Question 5

You have a Power BI model with a fact table 'Sales' and dimensions 'Customer', 'Product', 'Date'. You need to enforce that every fact row has a valid CustomerKey. Which approach enforces referential integrity in Power BI?

Accepted Answer

Set the relationship between Sales and Customer to 'Enforce Referential Integrity'. Option C is correct because marking the relationship as 'Enforce Referential Integrity' in Power BI ensures that only valid keys are used. Option A is wrong because combining tables changes the model structure. Option B is wrong because hiding columns does not enforce integrity. Option D is wrong because the assumption is not enforced natively.

Answer

Merge Sales and Customer tables into a single table

Answer

Hide CustomerKey columns in both tables

Answer

Assume all CustomerKey values are valid

Question 6

You need to create a calculated column that categorizes products as 'Low', 'Medium', or 'High' based on the 'UnitPrice' column. Which DAX function should you use?

Accepted Answer

SWITCH. Option B is correct because SWITCH evaluates conditions sequentially. Option A is wrong because IF can only handle two outcomes. Option C is wrong because CALCULATE modifies filter context, not for categorization. Option D is wrong because SUM is for aggregation.

Answer

CALCULATE

Answer

SUM

Answer

IF

Question 7

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. Option A is correct because 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 8

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 9

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 10

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. Option C is correct because a bridge table resolves many-to-many relationships. Option A is wrong because a single relationship cannot handle many-to-many. Option B is wrong because bidirectional filtering is not sufficient. Option D is wrong because merging creates a single table.

Answer

Merge both tables into one

Answer

Use bidirectional cross-filtering

Answer

Create a single one-to-many relationship

Question 11

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. Option A is correct because 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 12

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]). Option C is correct because DISTINCTCOUNT counts unique values. Option A is wrong because COUNT counts non-blank cells. Option B is wrong because COUNTA counts non-blank values. Option D is wrong because COUNTROWS counts rows.

Answer

COUNTROWS(Sales)

Answer

COUNT(Sales[ProductID])

Answer

COUNTA(Sales[ProductID])

Question 13

Which TWO actions can improve performance of a Power BI DirectQuery model?

Accepted Answer

Ensure proper indexing in the source database. Options A and D are correct. Reducing columns and increasing data source indexing improve query performance. Option B is wrong because bidirectional filtering often decreases performance. Option C is wrong because complex calculated columns slow queries. Option E is wrong because Import mode is not DirectQuery.

Answer

Switch the model to Import mode

Answer

Enable bidirectional cross-filtering on all relationships

Answer

Use calculated columns with complex logic

Question 14

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. Descriptive keys, separate date dimension, and aggregations at grain are best practices. Option B is wrong because natural keys are preferred when available. Option E is wrong because multiple relationships complicate the model.

Answer

Create multiple relationships between fact and dimension

Answer

Use composite keys in fact tables

Question 15

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

Accepted Answer

FILTER. Options A and C are correct. SUMMARIZECOLUMNS and FILTER return tables. Option B is wrong because SUMX returns a scalar. Option D is wrong because SELECTEDVALUE returns a scalar. Option E is wrong because CALCULATE returns a scalar.

Answer

SELECTEDVALUE

Answer

CALCULATE

Answer

SUMX

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

Exhibit

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

Exhibit