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

Question 1

You have a Power BI model with a table 'Employees' that includes columns: 'EmployeeID', 'ManagerID', 'Name'. You need to create a hierarchy that shows the reporting structure. What should you do first?

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

Accepted Answer

Create a self-join relationship from Employees[EmployeeID] to Employees[ManagerID].. To create a hierarchy that shows the reporting structure in Power BI, you first need to establish a self-join relationship between the Employees table and itself, linking EmployeeID to ManagerID. This allows Power BI to traverse the parent-child relationship and build a natural hierarchy (e.g., using Parent-Child functions like PATH). Without this relationship, the model cannot understand how employees relate to their managers.

Answer

Merge the Employees table with itself using Merge Queries.

Answer

Add a calculated column that concatenates EmployeeID and ManagerID.

Answer

Create a many-to-many relationship between Employees and itself.

Question 2

You are building a data model for a retail company. The 'Sales' fact table has a column 'Discount' that is a percentage (0 to 1). You create a measure 'Total Discount Amount' = SUM(Sales[Discount]) * SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]). However, the measure returns incorrect results when multiple discount percentages exist in the same filter context. What is the issue?

Accepted Answer

The measure is performing aggregations at the wrong granularity; it should use SUMX to iterate over each row.. The measure uses SUM on each column individually, which aggregates all values in the filter context before multiplying. When multiple discount percentages exist, this incorrectly multiplies the total of all discounts by the total of all quantities and total of all unit prices, rather than computing discount per row. The correct approach is to use SUMX to iterate over each row of the Sales table, calculating Discount * Quantity * UnitPrice per row and then summing those row-level results, ensuring accurate granularity.

Answer

The measure contains a circular dependency.

Answer

The measure is referencing columns from different tables without proper relationships.

Answer

The Discount column should be of type Decimal instead of Percentage.

Question 3

You have a Power BI data model with a 'Date' table that contains continuous dates from January 1, 2020, to December 31, 2025. The 'Sales' table has a relationship with the 'Date' table. You need to create a measure that calculates the total sales for the last 12 months from the current context. Which DAX function should you use?

Accepted Answer

DATESINPERIOD. The DATESINPERIOD function is correct because it returns a contiguous set of dates from a specified start date (the last date in the current context) going back a given number of intervals (12 months). This allows the measure to dynamically calculate total sales for the trailing 12 months relative to any filter context, such as a specific year or month.

Answer

PREVIOUSMONTH

Answer

DATEADD

Answer

SAMEPERIODLASTYEAR

Question 4

Which TWO of the following are best practices for designing a star schema in Power BI?

Accepted Answer

Dimension tables should have a primary key and descriptive columns.. Dimension tables in a star schema are designed to store descriptive attributes (e.g., product name, category) and must have a primary key column that uniquely identifies each row. This primary key is referenced by foreign keys in fact tables, enabling efficient filtering and slicing in Power BI. Without a primary key, relationships cannot be properly enforced, leading to ambiguous or incorrect query results.

Answer

Fact tables should contain calculated columns for business logic.

Answer

Merge all tables into a single flat table for simplicity.

Answer

Use many-to-many relationships between fact and dimension tables.

Question 5

Which THREE of the following are valid considerations when using Power BI DirectQuery mode?

Accepted Answer

Queries are sent to the underlying data source in real time.. In DirectQuery mode, Power BI does not import data; instead, it sends native queries to the underlying data source (e.g., SQL Server, Oracle) each time a visual is rendered or a filter is applied. This ensures that the data displayed is always current, as the query is executed in real time against the source system.

Answer

Data is imported into the Power BI dataset.

Answer

You cannot create relationships between tables.

Question 6

Which TWO of the following are valid methods to create a date table in Power BI?

Accepted Answer

Using the 'Mark as Date Table' feature on an existing table with dates.. The 'Mark as Date Table' feature allows you to designate an existing table that already contains valid date values as the official date table for time intelligence functions. This is a key step in Power BI modeling, as it enables DAX time intelligence functions like TOTALYTD or SAMEPERIODLASTYEAR to work correctly by ensuring the table has continuous, non-gapped dates and a proper date data type.

Answer

Using the 'Create Date Table' button in the ribbon.

Answer

Using the 'Generate Date Table' option in Power Query.

Answer

Manually typing dates in Excel and importing.

Question 7

Which THREE of the following are benefits of using a calculated column over a measure in Power BI?

Accepted Answer

They can be referenced by other calculated columns.. Calculated columns are evaluated during data refresh and stored in the model, allowing them to be used like any other column. They can be referenced by other calculated columns, used in slicers and report filters, and are pre-computed for faster query performance. Measures, on the other hand, are calculated at query time and do not store values, but they can still be used in other measures via DAX references. However, calculated columns consume memory per row, whereas measures do not, making measures more memory-efficient, especially for large datasets.

Answer

They use less memory than measures.

Answer

They are more efficient for large datasets than measures.

Question 8

Which THREE of the following are valid data types in Power BI?

Accepted Answer

Whole Number. Whole Number is a valid data type in Power BI for storing integers without decimal places. It is used for counts, IDs, and other discrete values, and it supports operations like aggregation and indexing efficiently.

Answer

Currency

Answer

Time

Question 9

You are importing data from a SQL Server database into Power BI. The source table has a column 'OrderDate' of type DATETIME. You want to filter data based on the date only, ignoring time. What is the most efficient approach?

Accepted Answer

Change the data type of the OrderDate column to 'Date' in Power Query. [wrong]. Changing the data type of the OrderDate column to 'Date' in Power Query is the most efficient approach. This modifies the existing column directly, avoiding the storage of an additional column and reducing memory usage. Unlike creating a new column with Date.From(), which keeps both the original datetime and the new date column, changing the data type is simpler and more memory-efficient. Power Query transformations like changing data type are applied during data load, making them more performant than DAX calculated columns, which are computed after data is loaded.

Answer

Create a calculated column in DAX using DATEVALUE(OrderDate). [CORRECT]

Answer

Create a new column in Power Query using Date.From(OrderDate). [wrong]

Answer

Use the 'Split Column' feature in Power Query to separate date and time. [wrong]

Question 10

Refer to the exhibit. The DAX measure 'Sales YTD' is defined as shown. The model has a Date table marked as a date table and a relationship to Sales[OrderDate]. However, the measure returns BLANK for all months except December. What is the most likely cause?

Accepted Answer

The Date table's year-end date is December 31, so YTD only shows data for December.. The TOTALYTD function defaults to a year-end date of December 31. In this scenario, the measure returns BLANK for months other than December because the underlying data only contains sales for December. The YTD calculation for any month other than December would accumulate from January 1 to that month, but if no sales exist for those months, the result is BLANK. When December is selected, the YTD includes the December sales, resulting in a non-blank value. The common trap is assuming that TOTALYTD always returns a running total regardless of data availability, but the year-end date being December 31 means the YTD is calculated within the calendar year, and if data is sparse, blanks can appear.

Answer

The measure uses 'Date'[Date] but the fact table uses 'Sales'[OrderDate].

Answer

The Date table is not marked as a date table.

Answer

The relationship between the Date table and Sales table is inactive.

Question 11

You have a Power BI model with a fact table 'Sales' and a dimension table 'Product'. The Product table contains columns: ProductID, ProductName, Category, and Subcategory. You want to create a hierarchy for drill-down in reports: Category > Subcategory > ProductName. What is the correct way to define this hierarchy?

Accepted Answer

In Model view, right-click 'Category' > 'Create hierarchy', then add Subcategory and ProductName as levels.. Power BI's Model view allows you to create a hierarchy by right-clicking a column (e.g., Category) and selecting 'Create hierarchy', then adding Subcategory and ProductName as child levels. This defines a natural drill-down path for visuals, enabling users to navigate from Category to Subcategory to ProductName without modifying the data model or using workarounds.

Answer

In Power Query, merge the Category, Subcategory, and ProductName columns into one.

Answer

Create a calculated column using CONCATENATE to combine the levels.

Answer

In Report view, add all three columns to a visual's Values well.

Question 12

Refer to the exhibit. You are configuring a Power BI data model for a workspace containing a semantic model with sensitive salary data. The JSON policy above is applied. A viewer reports that they can see the EmployeeSalaries table in the report. What is the most likely reason?

Accepted Answer

The JSON policy is for row-level security (RLS), not object-level security (OLS).. The JSON policy shown uses the `tables` array with `filter` expressions, which is the syntax for row-level security (RLS) policies in Power BI. RLS restricts data rows within a table but does not hide the table itself from the report view. Object-level security (OLS) requires a different policy structure using `excludedTables` or `setObjectLevelSecurity` in XMLA endpoints or Tabular Editor, not a JSON RLS policy. Therefore, the viewer can still see the EmployeeSalaries table because OLS has not been configured.

Answer

The EmployeeSalaries table is not in the semantic model.

Answer

The excludedTables property hides the table only in Power Query, not in reports.

Answer

The policy has not been applied to the semantic model.

Question 13

You have a fact table 'Orders' with columns: OrderID, CustomerID, OrderDate, SalesAmount. You also have a 'Calendar' table with Date, Year, Month, Quarter. The Calendar table is marked as a date table. You want to calculate total sales for the current month. Which DAX measure should you use?

Accepted Answer

CALCULATE(SUM(Orders[SalesAmount]), DATESMTD(Calendar[Date])). Both TOTALMTD and CALCULATE with DATESMTD are valid DAX patterns for calculating month-to-date totals. TOTALMTD is a dedicated time intelligence function equivalent to CALCULATE(SUM(...), DATESMTD(...)). Since the question asks for a measure to calculate total sales for the current month (month-to-date), both C and D are correct.

Answer

TOTALYTD(SUM(Orders[SalesAmount]), Calendar[Date])

Answer

SUM(Orders[SalesAmount])

Question 14

Refer to the exhibit. You are reviewing a DAX measure for month-over-month sales growth. The measure returns BLANK for all months. What is the likely issue?

Accepted Answer

The IF condition incorrectly treats zero as blank.. In DAX, BLANK() is treated as 0 in comparisons, so an IF condition like IF(PriorSales = 0, BLANK(), ...) will return BLANK when PriorSales is BLANK. This causes the measure to return BLANK for all months if there are no prior-month sales. The FILTER function does not remove filter context by itself; context removal is typically done by ALL or ALLEXCEPT.

Answer

The FILTER function removes all context from the Date table, causing PriorSales to be calculated over the entire table.

Answer

The Date table is not marked as a date table.

Answer

The variable PriorDate returns a blank because MAX is used.

Question 15

Which THREE of the following are valid reasons to use a composite model (mixed storage mode) in Power BI?

Accepted Answer

To enable real-time data from a DirectQuery source while using imported historical data.. Options A, B, and E are correct. Composite models allow combining imported and DirectQuery data in a single dataset. Option A is valid because you can have real-time data from a DirectQuery source alongside historical imported data. Option B is valid because you can use aggregations on large DirectQuery fact tables while keeping smaller tables imported. Option E is valid because combining DirectQuery and imported data is the core purpose of composite models. Option C is incorrect because composite models do not inherently improve relationship performance and may introduce limitations. Option D is incorrect because calculated tables are not supported on DirectQuery sources.

Answer

To improve relationship performance between tables.

Answer

To create calculated tables based on DirectQuery sources.