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

Question 1

When creating a many-to-many relationship between two tables, what is a common approach to model this in Power BI?

Accepted Answer

Introduce a bridge table that contains the unique combinations.. A bridge table (also known as a junction table) is used to resolve many-to-many relationships by creating two one-to-many relationships from the bridge table to each of the original tables. This allows the relationship to be modeled correctly in Power BI. Option A is incorrect because the CROSSJOIN function creates a Cartesian product of all rows from two tables, which is not a proper way to model a many-to-many relationship and leads to incorrect results. Option B is incorrect because merging both tables into a single table removes the relational structure and may cause data duplication and loss of granularity. Option D is incorrect because Power BI does not support direct many-to-many relationships without using a bridge table or setting up a many-to-many cardinality using a common dimension (which still involves a bridge table in the background). Therefore, introducing a bridge table is the standard approach.

Answer

Use the CROSSJOIN DAX function in calculated tables.

Answer

Merge both tables into a single table.

Answer

Create a direct many-to-many relationship in the model.

Question 2

Which TWO actions are valid for improving query performance in DirectQuery mode?

Accepted Answer

Create aggregated tables to pre-summarize data.. Correct answers are C and E. C: Creating aggregated tables pre-summarizes data, reducing the volume of data queried in DirectQuery mode, which improves performance. E: Reducing the number of columns in the table minimizes the data retrieved from the source, speeding up query execution. Option A is incorrect because disabling relationship enforcement can lead to incorrect results and does not improve performance. Option B is incorrect because converting to Import mode changes the storage mode entirely, not a DirectQuery optimization. Option D is incorrect because calculated columns are evaluated row-by-row and often degrade performance in DirectQuery.

Answer

Disable relationship enforcement to speed up joins.

Answer

Convert the model to Import mode.

Answer

Use calculated columns in the fact table.

Question 3

Which THREE considerations are important when designing a date table for time intelligence?

Accepted Answer

The date table must be marked as a date table in Power BI.. Marking a table as a date table is required for time intelligence functions. Option C is correct because a relationship to the fact table's date column is necessary for filtering. Option D is correct because a contiguous range of dates ensures proper time intelligence calculations. Option B is not correct because date columns can be calculated or imported; they don't have to be calculated columns. Option E is not correct because a model can have multiple date tables.

Answer

All date columns must be calculated columns.

Answer

The model must have exactly one date table.

Question 4

Which TWO approaches can you use to implement row-level security (RLS) in Power BI?

Accepted Answer

Create roles with static filters.. Static RLS uses roles with filters based on fixed values, restricting rows for users assigned to that role. Option E is correct because dynamic RLS uses the USERNAME or USERPRINCIPALNAME function to filter rows based on the current user. Option A is wrong because assigning users to security groups is part of RLS implementation but not an approach to implement RLS; roles are the mechanism, not group assignment directly. Option B is wrong because column-level security hides columns, not rows. Option C is wrong because object-level security restricts access to entire tables, not rows.

Answer

Assign users to security groups in the dataset.

Answer

Use column-level security to restrict row visibility.

Answer

Use object-level security to restrict rows.

Question 5

You have a table with a column 'Date' and a measure 'Total Sales'. You want to calculate the cumulative total of sales over time. Which DAX function should you use?

Accepted Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]). TOTALYTD calculates the year-to-date cumulative total of an expression. It takes a sum expression and a date column, and computes the cumulative total from the start of the year to the current context. Option A is wrong because SUM simply adds all values without any cumulative or time-based logic. Option B is wrong because DATESYTD returns a set of date ranges, not a cumulative value. Option C is wrong because CALCULATE with ALL(Sales) removes all filters, producing the total over the entire table, not a cumulative total over time.

Answer

SUM(Sales[Amount])

Answer

DATESYTD('Date'[Date])

Answer

CALCULATE(SUM(Sales[Amount]), ALL(Sales))

Question 6

You need to create a measure that calculates the average sales per transaction. The 'Sales' table has columns 'TransactionID' and 'Amount'. Which DAX expression is correct?

Accepted Answer

DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales)). DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales)) calculates total sales divided by the total number of rows in the Sales table. Assuming each transaction is represented by exactly one row, this gives the average sales per transaction. COUNTROWS counts all rows, which equals the number of transactions if each transaction is a single row. Option A is incorrect because it divides total sales by the distinct count of TransactionIDs; this would also be correct if each transaction has one row, but using DISTINCTCOUNT is unnecessary and may be slower. Option C is incorrect because AVERAGEX(Sales, Sales[Amount]) averages the Amount column over all rows, which is the average per row, not per transaction. Option D is incorrect because AVERAGE(Sales[Amount]) calculates the arithmetic mean of the Amount column, again per row, not per transaction.

Answer

SUM(Sales[Amount]) / DISTINCTCOUNT(Sales[TransactionID])

Answer

AVERAGEX(Sales, Sales[Amount])

Answer

AVERAGE(Sales[Amount])

Question 7

Which THREE factors can cause a DAX measure to return BLANK unexpectedly?

Accepted Answer

Division by zero.. Division by zero in DAX returns BLANK (A). FILTER with a false condition returns an empty table; when used in an aggregate measure (e.g., within CALCULATE), the measure returns BLANK (C). Using BLANK in arithmetic operations, particularly multiplication or division, propagates BLANK (E). SUM ignores blanks, so it does not cause an unexpected BLANK unless all values are blank (B). Referencing a nonexistent column produces a DAX error, not BLANK (D).

Answer

Using SUM on a column with blanks.

Answer

Referencing a column name that does not exist.

Question 8

You have a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. You want to create a measure that shows the total number of orders. Which DAX function should you use?

Accepted Answer

COUNTROWS(Orders). COUNTROWS(Orders) counts all rows in the Orders table, which corresponds to the total number of orders. Option A is wrong because DISTINCTCOUNT(Orders[OrderID]) counts distinct OrderID values, which would equal the number of rows if OrderID is unique, but the question asks for the total number of orders, which is simpler with COUNTROWS. Option B is wrong because SUM(Orders[OrderID]) attempts to sum a text/ID column, which is not meaningful. Option C is wrong because COUNT(Orders[OrderID]) counts non-blank numeric values in the column, which may also count rows if OrderID is numeric and non-blank, but COUNTROWS is the explicit function for row count.

Answer

DISTINCTCOUNT(Orders[OrderID])

Answer

SUM(Orders[OrderID])

Answer

COUNT(Orders[OrderID])

Question 9

You are designing a data model for a report that shows sales by product category and by month. Which table configuration is most efficient?

Accepted Answer

A date dimension table, a product dimension table, and a sales fact table.. A star schema with separate date and product dimension tables linked to a sales fact table is the most efficient design for filtering and aggregating sales by product category and month. This configuration reduces data redundancy, improves query performance, and enables easy filtering across dimensions. Option A is incorrect because having two fact tables (one for date and one for product) is not a conventional star schema; date and product are dimensions, not facts. Option C is incorrect because a single flat table leads to redundancy and poor performance. Option D is incorrect because combining date and product into one dimension table violates normalization principles and makes it harder to filter by a single dimension independently.

Answer

Two fact tables: one for date and one for product.

Answer

A single table with all columns.

Answer

One dimension table containing date and product attributes.

Question 10

You have a Power BI model with tables: Sales (DateKey, Amount) and Date (DateKey, Year, Month, Day). The measure above is intended to show sales for the current year ignoring any filters on date. However, when placed in a matrix with Month on rows, it returns the same value for every month. What is the most likely reason?

Accepted Answer

The FILTER function removes month context from the Date table. The FILTER function uses ALL(Date) which removes all filters from the Date table, including the month filter from the matrix row context. Therefore, the measure always returns the total sales for the entire current year regardless of the month. Option B is correct. Option A is wrong because the expression is valid syntactically. Option C is wrong because there is no relationship ambiguity issue. Option D is wrong because the table name is used correctly.

Answer

The CALCULATE function is misused and should be replaced with SUMX

Answer

The Date table is not marked as a date table

Answer

The relationship between Sales and Date is inactive

Question 11

You are designing a data model that will support self-service analytics. You have a Sales table with over 100 million rows. Which of the following modeling approaches would provide the best query performance while maintaining a user-friendly experience?

Accepted Answer

Design a star schema with a fact table and dimension tables. A star schema design separates facts and dimensions, optimizing query performance by reducing table scans and enabling efficient aggregations. Option A is correct because it balances query performance with user-friendliness. Option B (single flat table) causes high storage costs and slow queries. Option C (single table via merge) duplicates data and loses normalization. Option D (snowflake schema) may increase join complexity and reduce performance compared to star schema.

Answer

Create a single flat table with all columns

Answer

Import all tables into a single table using Power Query merge

Answer

Use a snowflake schema with multiple levels of dimensions

Question 12

You are creating a Power BI report that uses a composite model (DirectQuery for large tables and Import for small dimension tables). You want to ensure that measures referencing the DirectQuery tables are responsive. Which of the following design choices should you avoid?

Accepted Answer

Use complex time intelligence measures that iterate over the fact table. Complex time intelligence measures that iterate over the fact table (e.g., using DATESBETWEEN with a running total) can be very slow in DirectQuery mode because they generate inefficient SQL queries and require row-by-row processing. Therefore, such measures should be avoided when using DirectQuery. Option B (using measures that aggregate columns rather than rows) is acceptable because it reduces the amount of data transferred. Option C (creating a date dimension table imported from the source) is a good practice to support time intelligence without iteration. Option D (creating user-defined aggregations) can improve query performance by pre-aggregating data.

Answer

Use measures that aggregate columns rather than rows

Answer

Create a date dimension table imported from the source

Answer

Create user-defined aggregations for the DirectQuery table

Question 13

You are modeling data for a sales analysis. The Sales table includes a column 'ProductID' and a related Products table. You need to create a measure that calculates the total sales amount only for products in the 'Electronics' category. Which DAX measure should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Products[Category] = "Electronics")). CALCULATE with KEEPFILTERS ensures that any existing filters on the Products[Category] column are preserved while adding the 'Electronics' filter, making option A the correct choice. Option B uses ALL incorrectly; ALL requires a table or column reference, not a filter condition. Option C uses FILTER but, without KEEPFILTERS, it overrides any existing filters on Products[Category], which is not the intended behavior. Option D is syntactically invalid because SUMX cannot contain a nested SUM function; it expects a scalar expression.

Answer

CALCULATE(SUM(Sales[Amount]), ALL(Products[Category] = "Electronics"))

Answer

CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Category] = "Electronics"))

Answer

SUMX(Products, IF(Products[Category]="Electronics", SUM(Sales[Amount])))

Question 14

You are loading data into Power BI. The above Power Query script connects to a SQL Server database and loads aggregated sales by customer. However, the report requires the ability to drill down to individual transactions for a selected customer. What is the best approach to meet this requirement without losing the performance benefit of aggregation?

Accepted Answer

Use a composite model with a DirectQuery connection to the source for detail data. Use a composite model: import the aggregated table for high-level queries and use DirectQuery for the full detail table when drilling down. Option D is correct. Option A is wrong because it loses performance. Option B is wrong because the script filters out data before 2023. Option C is wrong because the script groups data, removing transaction details.

Answer

Remove the Grouped step and add a measure to aggregate at query time

Answer

Modify the filter date to include all years

Answer

Remove the Grouped step and import all transactions

Question 15

You have a Power BI data model with a table 'Orders' containing columns: OrderID, CustomerID, OrderDate, Amount. You want to create a measure that calculates the running total of sales over time, ordered by OrderDate. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Orders[Amount]), FILTER(ALLSELECTED(Orders), Orders[OrderDate] <= MAX(Orders[OrderDate]))). For a running total measure in Power BI, the correct pattern uses CALCULATE with FILTER and ALLSELECTED, and MAX to get the current context date. Option A follows this pattern: CALCULATE(SUM(Orders[Amount]), FILTER(ALLSELECTED(Orders), Orders[OrderDate] <= MAX(Orders[OrderDate]))). Option D is incorrect because EARLIER is used in calculated columns, not measures. In measures, row context is not available; instead, functions like MAX are used to get the current filter context value.

Answer

SUMX(Orders, RANKX(ALL(Orders), Orders[OrderDate], , ASC) * Orders[Amount])

Answer

TOTALYTD(SUM(Orders[Amount]), Orders[OrderDate])

Answer

CALCULATE(SUM(Orders[Amount]), FILTER(ALL(Orders), Orders[OrderDate] <= EARLIER(Orders[OrderDate])))