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

Question 1

You have a Power BI semantic model that imports sales data from Azure SQL Database. The model includes a date table and a fact table with a column 'SalesAmount'. You need to create a measure that calculates the total sales amount only for the last 30 days relative to the report date. Which DAX expressions should you use? (Select all that apply.)

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

Accepted Answer

CALCULATE(SUM(Sales[SalesAmount]), DATESINPERIOD('Date'[Date], TODAY(), -30, DAY)). The correct answer is B only. DATESINPERIOD with -30 DAY returns a contiguous period of 30 days, ending today. Option D includes 31 days because DATESBETWEEN is inclusive on both endpoints. Option A is month-to-date, not last 30 days. Option C filters directly on the fact table, ignores the date table, and also includes future dates if any, so it is not a valid/practice answer.

Answer

TOTALMTD(SUM(Sales[SalesAmount]), 'Date'[Date])

Answer

SUMX(FILTER(Sales, Sales[Date] >= TODAY()-30), Sales[SalesAmount])

Answer

CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN('Date'[Date], TODAY()-30, TODAY()))

Question 2

Refer to the exhibit. You are managing a Power BI workspace and applying dataset permissions via the XMLA endpoint. The JSON policy shows permissions for two users on the 'Sales' dataset. user1 has Viewer role, user2 has Reshare role. The dataset has row-level security (RLS) defined. Which statement is true about the permissions?

Accepted Answer

Both users will have RLS applied when querying the dataset.. Row-level security (RLS) is enforced for all users when querying a dataset, regardless of their permission level. Option A is incorrect because Reshare permission allows sharing but does not grant Build permission; user2 cannot build reports. Option B is incorrect because Reshare does not grant write permissions. Option C is correct: both users will have RLS applied when querying the dataset. Option D is incorrect because the Viewer role is read-only.

Answer

user2 can share the dataset and can build reports.

Answer

user2 can add new measures to the dataset using the XMLA endpoint.

Answer

user1 can modify the dataset structure through the XMLA endpoint.

Question 3

You are modeling a fact table that contains sales transactions with columns: OrderDate, ShipDate, SalesAmount, CustomerKey. You need to create a relationship to a date table. The date table has a single Date column. Which relationship should you create for the most accurate time-based analysis?

Accepted Answer

Create an active relationship from OrderDate to Date and an inactive relationship from ShipDate to Date.. You should create an active relationship from OrderDate to Date for standard reporting, and an inactive relationship from ShipDate to Date that can be activated using USERELATIONSHIP. Option A is incorrect because it ignores ShipDate, limiting time-based analysis. Option B is invalid because a table cannot have two active relationships to the same date table. Option D is incorrect because it ignores OrderDate, the primary transaction date.

Answer

Create a single relationship from OrderDate to Date and ignore ShipDate.

Answer

Create two active relationships: OrderDate to Date and ShipDate to Date.

Answer

Create only one relationship from ShipDate to Date.

Question 4

Refer to the exhibit. You are using DAX Studio to profile a Power BI semantic model. The query above returns a table with sales totals by year and product category. However, you notice that the results do not include rows for years or categories with no sales. Which DAX function should you use to ensure all combinations of year and category appear, even with blank sales?

Accepted Answer

CROSSJOIN. CROSSJOIN generates all possible combinations of the specified columns from different tables, ensuring every combination appears. You can then use a function like GENERATE or a CALCULATETABLE with a LEFT JOIN to the sales table to include rows with blank sales. Option A (ADDCOLUMNS) adds calculated columns to an existing table but does not generate missing combinations. Option C (SUMMARIZE) groups rows but may exclude combinations with no data. Option D (EXPAND) is not a valid DAX function.

Answer

ADDCOLUMNS

Answer

SUMMARIZE

Answer

EXPAND

Question 5

You are designing a Power BI semantic model for an e-commerce company. You have a fact table with OrderID, CustomerID, OrderDate, and SalesAmount. You also have a Customers table with CustomerID, CustomerName, and CustomerSegment. You need to ensure that filters on CustomerSegment propagate to the SalesAmount measure. What should you do?

Accepted Answer

Create a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID].. Creating a one-to-many relationship between Customers[CustomerID] and Sales[CustomerID] (the fact table) enables filters on CustomerSegment (a column in the Customers table) to propagate to the SalesAmount measure. This is the standard star schema design in Power BI. Option A is incorrect because LOOKUPVALUE is used to retrieve a single value and does not create a relationship for filter propagation; it would not allow filters on CustomerSegment to affect the SalesAmount measure. Option C is incorrect: merging tables in Power Query would create a denormalized flat table, which may cause data duplication and is not necessary when a relationship suffices. Option D is incorrect because adding a separate Segment table introduces a snowflake schema, which is unnecessary here; the current design with Customers containing CustomerSegment is sufficient.

Answer

Use the LOOKUPVALUE function in a calculated column.

Answer

Merge the Customers and Sales tables into a single table in Power Query.

Answer

Create a snowflake schema by adding a separate Segment table.

Question 6

Your organization uses Power BI with large datasets from Azure Synapse Analytics. You need to reduce model size and improve query performance. You decide to use aggregations. Which type of aggregation table is most appropriate for pre-aggregating data at the year and category level?

Accepted Answer

Table-based aggregation. Table-based aggregation stores precomputed summaries in a separate table, which aligns with the requirement for pre-aggregating data at the year and category level. Option A (Calculated table) is incorrect because calculated tables are computed at refresh time and do not serve as pre-aggregated storage for query performance. Option C (Measure-based aggregation) is incorrect because measures are computed on the fly and not stored as separate tables, so they do not pre-aggregate data. Option D (In-memory aggregation) is not a standard aggregation type in Power BI.

Answer

Calculated table

Answer

Measure-based aggregation

Answer

In-memory aggregation

Question 7

Refer to the exhibit. You have defined row-level security (RLS) in a Power BI semantic model using Role-level security. The JSON shows three rules for the 'Sales' and 'Targets' tables. The user 'john@contoso.com' belongs to this role. John's manager column in the Sales table has the value 'john@contoso.com'. What data will John see when he queries the model?

Accepted Answer

Sales rows where Region is 'West' and Manager is 'john@contoso.com'; Targets rows where Region is 'West'.. RLS rules are combined with AND logic. John will see only Sales rows where Region is 'West' AND Manager is 'john@contoso.com'. For Targets, only rows with Region 'West'. Option B is incorrect because OR is not used. Option C is incorrect because both rules on Sales apply. Option D is incorrect because all rules apply.

Answer

All Sales and Targets data because John is a manager.

Answer

Sales rows where Region is 'West' OR Manager is 'john@contoso.com'; Targets rows where Region is 'West'.

Answer

Only Sales rows where Manager is 'john@contoso.com'; no Targets restriction.

Question 8

You are building a Power BI semantic model that includes a Date table. Which of the following is a best practice for creating a Date table?

Accepted Answer

Mark the date table as a date table in Power BI Desktop.. Marking a date table as a date table in Power BI Desktop enables time intelligence functions and ensures the table is used as the source for date hierarchies. Option B is incorrect because you should not create relationships to every date column; instead, have one active relationship per fact table and use inactive relationships with USERELATIONSHIP as needed. Option C is incorrect because CALENDARAUTO can generate an unpredictable date range that may not cover all necessary dates; it is better to use CALENDAR or CALENDARPERIOD with explicit start and end dates. Option D is incorrect because the date table should contain a contiguous range of dates that covers all dates in the fact tables, not only those that exist in the data.

Answer

Create relationships from the date table to every fact table column that contains dates.

Answer

Use the CALENDARAUTO function to automatically generate dates.

Answer

Use a date table that includes only dates that exist in the fact tables.

Question 9

Refer to the exhibit. A Power BI developer wrote the DAX measure above to calculate year-over-year sales growth. When tested in a matrix visual with Year on rows and Month on columns, the measure returns incorrect results for the month of February in leap years. What is the likely cause?

Accepted Answer

SAMEPERIODLASTYEAR does not handle leap years correctly for February 29.. SAMEPERIODLASTYEAR shifts the date context by exactly one year (365 days), which can cause inaccuracies in leap years for dates after February 28. For example, February 29 in a leap year maps to March 1 in a non-leap year, leading to incorrect year-over-year comparisons. Options B, C, and D are incorrect: the measure is appropriately defined as a measure (B), the variable is properly used (C), and DIVIDE handles zero division gracefully (D).

Answer

The measure should be a calculated column, not a measure.

Answer

The variable CurrentSales is not needed; use SUM directly.

Answer

The DIVIDE function returns an error when PreviousSales is zero.

Question 10

You have a Power BI semantic model with a fact table containing sales data at the transaction level. You need to create a measure that calculates the number of distinct customers who made a purchase in the current period and also made a purchase in the previous period. Which DAX pattern should you use?

Accepted Answer

Use CALCULATE with a filter that intersects customers in both periods.. The retention pattern uses CALCULATE with a filter that intersects customers who made a purchase in the current period and previous period. Option B is incorrect because PREVIOUSCUSTOMER is not a DAX function. Option C is incorrect because DISTINCTCOUNT is a valid function, but using it over a concatenated key does not properly compare across two time periods; it only counts distinct customers in one context. Option D is incorrect because while a calculated column could be used, it is less flexible than a measure and does not adjust to filter context.

Answer

Use the PREVIOUSCUSTOMER function.

Answer

Use DISTINCTCOUNT over a concatenated key.

Answer

Add a calculated column to identify returning customers.

Question 11

Which TWO of the following are valid reasons to create a separate date table in a Power BI semantic model? (Select two.)

Accepted Answer

To enable time intelligence functions like TOTALYTD.. Options B and C are correct. A separate date table is required for time intelligence functions like TOTALYTD in Power BI (B), and it provides a standardized hierarchy of year, quarter, month, and day for filtering and drill-down (C). Option A is incorrect because date arithmetic can be performed without a separate date table using DAX functions like DATEDIFF. Option D is incorrect because many-to-many relationships between fact tables are not resolved by creating a date table; they typically require bridge tables or specific relationship settings. Option E is incorrect because a date table can only have one active relationship per fact table; multiple active relationships from the same fact table would cause ambiguity, and Power BI activates only one relationship at a time.

Answer

To calculate measures that require date arithmetic.

Answer

To create many-to-many relationships between fact tables.

Answer

To allow multiple active relationships from the same fact table to the date table.

Question 12

Which TWO of the following are best practices when designing star schemas in Power BI? (Select two.)

Accepted Answer

Store numeric measures in fact tables.. Fact tables in a star schema are designed to store quantitative, numeric measures (e.g., sales amount, quantity) that can be aggregated. This aligns with the star schema principle of separating measures (facts) from descriptive context (dimensions), enabling efficient compression and fast aggregations in Power BI's VertiPaq engine.

Answer

Use calculated columns in fact tables for row-level security.

Answer

Include many columns in fact tables for filtering.

Answer

Normalize dimension tables to reduce redundancy.

Question 13

Which THREE of the following are valid ways to model a many-to-many relationship between tables in Power BI? (Select three.)

Accepted Answer

Add a calculated column to one table to create a unique key.. Adding a calculated column to create a unique key is a common technique used in bridge tables to resolve many-to-many relationships. Option D is correct because a bridge table with many-to-many cardinality directly models the relationship. Option E is correct because setting cross filter direction to Both with Many-to-Many cardinality is a built-in feature in Power BI. Option A is incorrect because LOOKUPVALUE is a scalar function that returns a single value, not a modeling method for relationships. Option C is incorrect because creating two one-to-many relationships with USERELATIONSHIP is used for handling multiple active relationships between the same two tables, not for many-to-many scenarios.

Answer

Use the LOOKUPVALUE function in a measure.

Answer

Create two one-to-many relationships and use USERELATIONSHIP in measures.

Question 14

Which TWO of the following are valid DAX functions for modifying filter context within a measure? (Select two.)

Accepted Answer

CALCULATE. Options B (CALCULATE) and E (ALL) are correct. CALCULATE is the primary function used to modify filter context within a measure. It evaluates an expression in a modified filter context. ALL is a filter modifier that removes filters from a table or column, thereby altering the filter context when used as a filter argument inside CALCULATE. Option A (FILTER) is a table function that returns a filtered table; it does not directly modify filter context but is used as an argument. Option C (VALUES) returns a single-column table of unique values and does not modify filter context. Option D (SUM) is an aggregation function and does not affect filter context.

Answer

FILTER

Answer

VALUES

Answer

SUM

Question 15

You are designing a data model for a sales analysis report. The source data includes a table named 'Sales' with columns: OrderDate, CustomerID, ProductID, Quantity, and SalesAmount. You need to create a date table to support time intelligence calculations. Which approach should you use?

Accepted Answer

Create a date table using CALENDAR function with start date = MIN(Sales[OrderDate]) and end date = MAX(Sales[OrderDate]). Using CALENDAR with MIN(Sales[OrderDate]) as start and MAX(Sales[OrderDate]) as end generates a continuous date range covering all sales dates. After marking this table as a date table using the 'Mark as Date Table' feature, DAX time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR work correctly. Option A is incorrect because it requires manually specifying start and end dates, which may include unnecessary dates outside the sales period, leading to extra data and potential performance issues. Options B and C are incorrect as explained.

Answer

Create a date table using CALENDAR function with a continuous date range covering all possible dates, then use Mark as Date Table in the model view

Answer

Use the CALENDARAUTO function to automatically generate dates and rely on auto date/time

Answer

Use the DISTINCT function on Sales[OrderDate] to create a date table