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

Question 1

Which THREE factors should you consider when designing a star schema in Power BI?

Accepted Answer

A separate date table should be created for time intelligence.. A separate date table is required for time intelligence functions in Power BI because DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) rely on a continuous, contiguous date range with no gaps. Power BI automatically marks a table as a date table only if it contains a complete set of dates from the earliest to the latest transaction, enabling functions like DATEADD and DATESBETWEEN to work correctly across all granularities.

Answer

Use natural keys instead of surrogate keys in dimension tables.

Answer

Use a snowflake schema to reduce data redundancy.

Question 2

Which TWO DAX functions can be used to filter data in a measure?

Accepted Answer

FILTER. (FILTER) is correct because it is a DAX function specifically designed to return a filtered subset of a table based on a logical condition, making it a primary tool for row-level filtering within measures. Option E (CALCULATE) is correct because it modifies the filter context of a measure by applying or removing filters, effectively filtering data through its filter arguments. Both functions directly control which rows are included in calculations.

Answer

VALUES

Answer

SUM

Answer

ALL

Question 3

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

Accepted Answer

Ensure proper indexing in the source database. Proper indexing in the source database reduces the query execution time for DirectQuery models. DirectQuery sends queries to the source database in real-time, so efficient indexes on columns used in filters, joins, and aggregations minimize table scans and improve retrieval speed.

Answer

Switch the model to Import mode

Answer

Enable bidirectional cross-filtering on all relationships

Answer

Use calculated columns with complex logic

Question 4

A data model contains a Date table and a Sales table. You need to create a measure that calculates total sales for the previous year. Which DAX function should you use?

Accepted Answer

SAMEPERIODLASTYEAR. SAMEPERIODLASTYEAR is the correct DAX function for calculating total sales for the previous year because it returns a set of dates shifted back by exactly one year while preserving the current filter context (e.g., month, quarter). This function is specifically designed for year-over-year comparisons and works seamlessly with a Date table marked as a date table in the model. Option B: DATEADD can also shift dates by one year but requires specifying the interval and number of intervals, making it less direct for this specific requirement. Option C: PARALLELPERIOD returns the entire parallel period (e.g., full year) regardless of current granularity, which does not preserve the same relative period. Option D: PREVIOUSYEAR returns the entire previous year, not the same period shifted back, so it does not maintain month-over-month or quarter-over-quarter comparisons.

Answer

DATEADD

Answer

PARALLELPERIOD

Answer

PREVIOUSYEAR

Question 5

You have a Power BI data model with a fact table and multiple dimension tables. You notice that many-to-many relationships cause ambiguous results. What is the best practice to resolve this?

Accepted Answer

Add a bridge table with appropriate relationships. In Power BI, many-to-many relationships between fact and dimension tables can produce ambiguous results because the model cannot determine a unique filter propagation path. The best practice is to introduce a bridge table that resolves the many-to-many relationship into two one-to-many relationships, ensuring unambiguous filter context and correct aggregations.

Answer

Change the relationship to one-to-one

Answer

Use a bidirectional cross-filter direction

Answer

Create a calculated table to merge the dimensions

Question 6

You have a data model with a Sales table and a Date table. You create a measure: Total Sales = SUM(Sales[Amount]). When you add a slicer for Date[Year], the measure does not filter correctly. What is the most likely cause?

Accepted Answer

The relationship is inactive. The most likely cause is that the relationship between the Sales table and the Date table is inactive. In Power BI, only one active relationship can exist between two tables; any additional relationships must be inactive. A slicer on Date[Year] will only filter through the active relationship. If the active relationship is not the one intended for the measure, the slicer will not affect the measure's result. Using USERELATIONSHIP in the measure or setting the correct relationship as active would resolve this.

Answer

The Date table is not marked as a date table

Answer

The Sales table has no relationship to the Date table

Answer

The measure uses SUM instead of SUMX

Question 7

Which TWO of the following are valid DAX functions for time intelligence? (Select two.)

Accepted Answer

DATEADD. DATEADD is a valid DAX time intelligence function that shifts dates forward or backward by a specified number of intervals (days, months, quarters, years). TOTALYTD is also a valid time intelligence function that calculates the year-to-date value of an expression. Both are part of the dedicated time intelligence function set in DAX, which requires a properly marked date table with continuous dates.

Answer

RANKX

Answer

CONCATENATEX

Answer

MINX

Question 8

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]))). It uses DIVIDE for safe division and CALCULATE with ALL(Product[Category]) to remove the filter context on the Product[Category] column, allowing the measure to compute the percentage of total sales for each product category. This pattern ensures that the denominator represents the total sales across all categories, while the numerator respects the current filter context for the specific category.

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 9

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 = SUMX(FILTER(Orders, Orders[OrderDate] > TODAY() - 30 && CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5), Orders[TotalAmount]). It iterates over filtered rows where both conditions are met using a single FILTER and SUMX, which is syntactically valid and more efficient than multiple FILTER iterators. Option B is invalid because KEEPFILTERS expects a filter expression, not a scalar boolean result from CALCULATE(...) > 5.

Answer

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

Answer

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

Answer

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

Question 10

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. In a star schema, dimension tables (Regions, Categories) contain descriptive attributes, and the fact table (Sales) stores foreign keys referencing those dimensions. Removing the Region and Category columns from the Sales table and replacing them with foreign keys (e.g., RegionID, CategoryID) normalizes the model, reduces data redundancy, and enables efficient filtering and slicing by region and category attributes. This approach aligns with best practices for Power BI data modeling, ensuring optimal query performance and maintainability.

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 11

Which THREE are best practices for managing relationships in Power BI? (Select exactly 3.)

Accepted Answer

Hide foreign key columns in dimension tables to prevent misuse. Options B, C, and E are correct. Option B: Hiding foreign key columns in dimension tables prevents users from accidentally using them in visuals, which is a best practice. Option C: Single-direction cross-filtering is recommended to avoid ambiguity and performance issues; bidirectional should only be used when necessary. Option E: Ensuring data types match between related columns is essential for creating valid relationships. Option A is incorrect because many-to-many relationships should be avoided when possible; they add complexity and can negatively impact performance. Option D is incorrect because setting cross-filter direction to both is not recommended by default—it can lead to ambiguous filtering and degraded performance.

Answer

Use many-to-many relationships whenever possible to simplify the model

Answer

Always set cross-filter direction to both to allow full interactivity

Question 12

You need to create a calculated column in Power BI that categorizes sales amounts as 'Low', 'Medium', or 'High' based on the value. The column should be evaluated row by row. Which DAX function should you use?

Accepted Answer

SWITCH. SWITCH is the correct DAX function because it evaluates an expression (the sales amount) against a series of conditions and returns a corresponding result for each condition. In this scenario, you can use SWITCH(TRUE(), [Sales] < 1000, 'Low', [Sales] < 5000, 'Medium', 'High') to create a calculated column that categorizes values row by row. SWITCH is designed for multiple conditional branches, making it the most efficient and readable choice for this three-tier categorization.

Answer

IF

Answer

CALCULATE

Answer

FORMAT

Question 13

You are modeling a many-to-many relationship between 'Students' and 'Courses' via a junction table 'Enrollments'. You need to create a measure that counts the number of students enrolled in at least one course. The relationship between Students and Enrollments is one-to-many, and between Courses and Enrollments is one-to-many. What DAX measure should you use?

Accepted Answer

DISTINCTCOUNT(Students[StudentID]). DISTINCTCOUNT(Students[StudentID]). This measure counts unique students, which correctly handles the many-to-many relationship because filters applied to 'Courses' propagate through 'Enrollments' to 'Students', ensuring only students with at least one enrollment are counted. Option A (COUNTROWS(Enrollments)) counts enrollment rows, potentially double-counting students with multiple courses. Option B (COUNT(Courses[CourseID])) counts courses, not students. Option C (COUNTA(Students[StudentID])) counts all non-blank StudentIDs regardless of enrollment, missing the condition. DISTINCTCOUNT is the proper DAX function for distinct counts.

Answer

COUNTROWS(Enrollments)

Answer

COUNT(Courses[CourseID])

Answer

COUNTA(Students[StudentID])

Question 14

Which TWO of the following are valid DAX functions for time intelligence?

Accepted Answer

TOTALYTD. TOTALYTD is a valid DAX time intelligence function that calculates the year-to-date value of an expression, typically used with a date column to aggregate data from the start of the year to the current context. It requires a properly marked date table with continuous dates to function correctly.

Answer

RANKX

Answer

SUM

Answer

CALCULATE

Question 15

Which THREE of the following are considerations when implementing row-level security (RLS) in Power BI? (Select three.)

Accepted Answer

RLS does not apply to data accessed via the XMLA endpoint unless using dynamic security.. Options A, B, and C are correct. Row-level security (RLS) in Power BI ensures that users only see data they are authorized to view. Option A is correct because RLS does not apply to data accessed via the XMLA endpoint unless dynamic security is configured; static RLS filters are not applied through the endpoint. Option B is correct because RLS filters are applied at query time, restricting data in all visuals. Option C is correct because RLS can be defined using DAX filter expressions in the Manage Roles feature. Option D is incorrect because RLS applies to all users regardless of their role (Viewer, Editor, etc.); it is not limited to the Viewer role. Option E is incorrect because the "Show all" option in visuals does not bypass RLS; users cannot see data they are not permitted to see.

Answer

RLS is enforced only for users with the Viewer role.

Answer

RLS can be bypassed by using the 'Show all' option in visuals.