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

Question 1

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.. Option C is correct because the 'Mark as Date Table' feature lets you designate an existing table containing a contiguous date column as the official date table, which Power BI then uses for time intelligence functions such as DATESYTD and SAMEPERIODLASTYEAR. Option E is correct because CALENDARAUTO() is a DAX function that, when used in a calculated table (e.g., DateTable = CALENDARAUTO()), automatically generates a complete, contiguous date table spanning the minimum and maximum dates found across the model's date columns. Option A is incorrect because Power BI has no 'Create Date Table' ribbon button; date tables are created via DAX calculated tables, Power Query, or external sources. Option B is incorrect because Power Query has no built-in 'Generate Date Table' option; you must build one manually with M functions like List.Dates or Date.From. Option D is incorrect because manually typing dates in Excel and importing them is not a recognized Power BI date-table creation method and would not guarantee the contiguous, marked date table required for time intelligence.

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 2

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.. Option A is correct because a calculated column is evaluated row by row and materialized in the table, so it becomes a regular column that other calculated columns can reference in their DAX expressions. Option B is correct because calculated columns are computed during data refresh (or processing) and their results are stored in the VertiPaq engine, so they are not recalculated at query time. Option D is correct because, being stored columns, calculated columns can be placed on slicers, used in report filters, and used as rows/columns in visuals, unlike measures which are evaluated at query time and cannot be used in slicers. Option C is incorrect because calculated columns consume memory by storing a value for every row, whereas measures are computed on demand and do not store per-row values, so measures generally use less memory. Option E is incorrect because calculated columns increase model size and refresh time on large datasets, while measures are typically more efficient for aggregations since they are computed at query time and benefit from the VertiPaq engine's compression and aggregation.

Answer

They use less memory than measures.

Answer

They are more efficient for large datasets than measures.

Question 3

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

Accepted Answer

Whole Number. Whole Number (B) is a valid Power BI data type, used for integer values with no fractional component, stored as a 64-bit integer. Decimal Number (C) is also valid, representing floating-point numeric values with fractional digits for precise calculations. Date/Time (E) is a valid Power BI data type that stores both date and time values together, such as 2024-01-15 14:30:00. Currency (A) is not a Power BI data type; monetary values use the Fixed Decimal Number type instead. Time (D) is not a standalone Power BI data type, since time values are stored within the Date/Time type.

Answer

Currency

Answer

Time

Question 4

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 5

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 6

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 7

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 8

Which TWO of the following are best practices for designing a Power BI data model?

Accepted Answer

Use a star schema to reduce model complexity.. A star schema organizes data into dimension and fact tables, reducing redundancy and simplifying queries. This design improves query performance by minimizing the number of table joins and enabling efficient aggregation, which is a core best practice in Power BI data modeling.

Answer

Store dimension attributes directly in fact tables for faster queries.

Answer

Enable bi-directional cross-filtering on all relationships.

Answer

Create calculated columns instead of Power Query transformations when possible.

Question 9

You have a Power BI model with a large fact table (100 million rows) and several dimension tables. You need to improve query performance for a report that uses a date slicer. The report currently takes 10 seconds to load. What is the most effective optimization?

Accepted Answer

Create an aggregated table that summarizes data at the month level.. Creating an aggregated table that summarizes data at the month level (Option D) reduces the number of rows processed by the report. Since the report uses a date slicer, the user likely queries at day-level granularity, but the aggregation can be used when monthly totals suffice, or the report can use the detailed table only when needed. This is a common and effective optimization for large fact tables in Power BI, as it directly reduces the data load and query processing time. Partitioning (Option A) primarily improves refresh and load performance, not query response, and is less effective for a single date slicer. Removing columns (B) helps but is not as impactful as aggregation. Increasing capacity (C) is costly and does not address the root cause.

Answer

Partition the fact table by date range.

Answer

Remove unused columns from the fact table.

Answer

Increase the Power BI service capacity.

Question 10

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

Accepted Answer

SWITCH(TRUE(), Sales[Amount] < 100, "Low", Sales[Amount] < 500, "Medium", "High"). It uses SWITCH(TRUE(), ...) to evaluate multiple conditions sequentially, returning the first matching result. This is the standard pattern for categorizing continuous values into discrete buckets in DAX, as SWITCH evaluates conditions in order and stops at the first TRUE condition.

Answer

IF(Sales[Amount] < 100, "Low", IF(Sales[Amount] < 500, "Medium", "High"))

Answer

SELECTEDVALUE(Sales[Amount]) & "Category"

Answer

SWITCH(Sales[Amount], 100, "Low", 500, "Medium", "High")

Question 11

You have a star schema with a Sales fact table and a Date dimension. You need to calculate year-to-date sales that reset each fiscal year starting July 1. Which measure is correct?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "6-30")). The correct measure is CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "6-30")). DATESYTD returns the set of dates from the start of the fiscal year (July 1) to the last date in the current filter context, and CALCULATE evaluates SUM in that context. TOTALYTD(..., ALL('Date'), "6-30") incorrectly strips all filters on the Date table, so the YTD would not advance with the selected date. Additionally, the ALL('Date') argument is not required for a simple YTD calculation and actually contradicts the goal of computing a dynamic YTD.

Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], "6-30")

Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], ALL('Date'), "6-30")

Question 12

Refer to the exhibit. You are implementing row-level security (RLS) in Power BI. The JSON policy above is applied to the 'Sales' table. The user is in the 'SalesRegion' role. Which rows will the user see?

Accepted Answer

Rows where Region is 'North' only.. The JSON policy defines a filter that restricts the 'Sales' table to rows where the 'Region' column equals 'North'. The user is in the 'SalesRegion' role, which applies this filter. RLS in Power BI evaluates the filter expression for each row, returning only those that satisfy the condition, so only rows with Region = 'North' are visible.

Answer

Rows where Region is 'North' and 'South'.

Answer

No rows, because there is a conflict.

Answer

Rows where Region is 'South' only.

Question 13

You have a table with columns 'Date', 'Sales', and 'Quantity'. You need to create a measure that calculates the average sales per transaction. Which DAX measure should you use?

Accepted Answer

AVERAGE(Sales[Sales]). The AVERAGE function in DAX calculates the arithmetic mean of a column's numeric values. Since the question asks for 'average sales per transaction,' you need the average of the 'Sales' column, which represents the sales amount per transaction. AVERAGE(Sales[Sales]) directly computes this mean.

Answer

AVERAGE(Sales[Quantity])

Answer

DIVIDE(SUM(Sales[Sales]), SUM(Sales[Quantity]))

Answer

SUM(Sales[Sales])

Question 14

You are building a Power BI model with a fact table and multiple dimension tables. You want to enforce a rule that each fact row must have a matching row in a related dimension. Which modeling technique should you use?

Accepted Answer

Use a calculated column to check for existence.. To enforce that every fact row has a matching dimension row, you can use a calculated column to check for the existence of a corresponding value in the dimension table. For example, using LOOKUPVALUE or a related function to return a value only if a match exists. This allows you to filter out or flag rows without matches. Option D is incorrect because assuming referential integrity does not enforce the rule; it only tells Power BI to trust that data is already clean, but does not validate or prevent unmatched rows.

Answer

Set the relationship cardinality to many-to-many.

Answer

Set the cross-filter direction to both.

Answer

Assume referential integrity in the relationship settings.

Question 15

Refer to the exhibit. You have a Sales table. You create a measure: TotalSales = SUM(Sales[SalesAmount]). You then create another measure: SalesAboveAverage = CALCULATE([TotalSales], FILTER(Sales, Sales[SalesAmount] > AVERAGE(Sales[SalesAmount]))). What is the value of SalesAboveAverage?

Accepted Answer

550. The average of SalesAmount is (100+200+300+150+250)/5 = 200. The rows with SalesAmount > 200 are: 300 and 250. Sum = 550. Option A is wrong because it includes all rows (1000). Option B is wrong because it is an incorrect sum, not the sum of values above average. Option C is wrong because it sums values less than or equal to 200. Option D is correct.

Answer

1000

Answer

800

Answer

450