PL-300 Model the data — 40 Questions

Question 1

A company has a Power BI dataset that contains a date table with columns: Date, Year, Month, Quarter, Day. The data model also includes a sales fact table with a SalesDate column. To enable time intelligence functions like TOTALYTD, what is the minimum requirement for the relationship between these tables?

Accepted Answer

Create a one-to-many relationship from the date table to the sales table and mark the date table as a date table.. Option B is correct because time intelligence functions like TOTALYTD require a properly configured date table marked as a date table, with a one-to-many relationship from the date table to the sales fact table. This ensures that the date table provides a continuous, unique set of dates that Power BI can use for time-based calculations, and marking it as a date table enables the engine to recognize it as the primary date dimension for time intelligence.

Answer

Create a calculated column in the sales table to extract the date part and relate it to the date table.

Answer

Create a many-to-many relationship between the date table and the sales table.

Answer

Create a one-to-many relationship from the sales table to the date table with bidirectional cross-filtering.

Question 2

A Power BI developer creates a star schema with a fact table Sales and dimension tables Customer, Product, Date. The relationship between Sales and Date is active. The developer wants to create a measure that calculates the total sales for the previous month relative to any selected month. Which DAX expression should the developer use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Date[Date])). Option D is correct because PREVIOUSMONTH(Date[Date]) returns a set of dates for the full previous month relative to the last date in the current filter context. When wrapped in CALCULATE, it shifts the filter on the Date dimension to the prior month, correctly computing total sales for the previous month regardless of the selected month.

Answer

CALCULATE(SUM(Sales[Amount]), DATESMTD(Date[Date]))

Answer

CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))

Answer

CALCULATE(SUM(Sales[Amount]), DATEADD(Date[Date], -1, MONTH))

Question 3

A Power BI data model includes a table 'Orders' with columns OrderID, CustomerID, OrderDate, SalesAmount. The model also has a 'Date' table and a 'Customer' table. The relationships are: Orders[CustomerID] -> Customer[CustomerID] (many-to-one, single direction) and Orders[OrderDate] -> Date[Date] (many-to-one, single direction). A user creates a measure that sums SalesAmount and then filters by a slicer on Customer[City]. The slicer works correctly. However, when the user adds another slicer on Date[Year], the measure does not respect both slicers simultaneously. What is the most likely cause?

Accepted Answer

The measure might be using ALL or ALLEXCEPT that removes the filter context from the Date table.. Option D is correct because the measure likely uses ALL or ALLEXCEPT, which removes the filter context from the Date table. Even though the relationships are correctly configured and filters from the Date slicer propagate to Orders via the single-direction relationship, if the measure explicitly ignores those filters using a function like ALL(Date[Year]) or ALLEXCEPT(Orders, ...), the Date slicer will have no effect on the measure. This is a common DAX mistake where filter removal functions override slicer selections.

Answer

The Customer and Date tables are not related to each other.

Answer

The relationship between Orders and Date is inactive.

Answer

The relationships are set to single direction, so filters from Date do not propagate to Orders.

Question 4

A Power BI developer needs to model data from two sources: an on-premises SQL Server database and a cloud-based Salesforce instance. The developer wants to create a star schema in Power BI. Which approach should the developer use to combine the data?

Accepted Answer

Use Power Query in Power BI Desktop to import both sources and merge/append queries as needed.. Option B is correct because Power Query in Power BI Desktop is the appropriate tool to import data from both an on-premises SQL Server database and a cloud-based Salesforce instance, allowing the developer to merge or append queries as needed to shape the data into a star schema. This approach supports combining disparate sources into a single import model, which is essential for creating a star schema with fact and dimension tables. Using Power Query ensures that all data is loaded into memory, enabling fast query performance and full modeling capabilities.

Answer

Use DirectQuery for both sources and create relationships in the model.

Answer

Use Power BI dataflows to ingest both sources and then reference them in a dataset.

Answer

Create a composite model using DirectQuery for SQL Server and Import for Salesforce.

Question 5

A Power BI developer has a fact table that contains sales data at the transaction level. The table includes columns: TransactionID, ProductID, CustomerID, DateKey, Quantity, UnitPrice, Discount, and SalesAmount. The developer wants to create a measure for total sales after discount. Which approach is best for performance and accuracy?

Accepted Answer

Add a calculated column in Power Query: NetAmount = Quantity * UnitPrice - Discount, then create a measure: SUM(Sales[NetAmount]). Option B is correct because it performs the net amount calculation at the row level in Power Query (M), which is computed during data refresh and stored in the table. This avoids runtime row-by-row iteration in DAX, making the measure SUM(Sales[NetAmount]) a simple, highly efficient aggregation. It ensures both performance and accuracy, as the discount is applied per transaction before aggregation.

Answer

Create a measure: SUM(Sales[SalesAmount]) - SUM(Sales[Discount])

Answer

Create a measure: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] - Sales[Discount])

Answer

Create a measure: SUM(Sales[Quantity] * Sales[UnitPrice]) - SUM(Sales[Discount])

Question 6

A Power BI developer is designing a data model for sales analysis. The model includes a Sales fact table and dimension tables: Product, Customer, Date, and Store. Which TWO design considerations are best practices for optimizing query performance?

Accepted Answer

Create a separate date table and mark it as a date table to enable time intelligence functions.. Option C is correct because creating a separate date table and marking it as a date table enables Power BI to use built-in time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) that rely on a continuous, contiguous date range. This design ensures optimal performance by allowing the engine to generate efficient DAX queries and leverage date-based relationships without ambiguity.

Answer

Configure bidirectional cross-filtering between all dimension tables and the fact table.

Answer

Use multiple inactive relationships between fact and dimension tables to support different analyses.

Answer

Use many-to-many relationships between dimension tables and the fact table to simplify the model.

Question 7

A Power BI developer is building a data model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, ShipDate, SalesAmount. The developer wants to analyze orders by both order date and ship date. Which THREE actions should the developer take to properly model this scenario?

Accepted Answer

Use the USERELATIONSHIP function in DAX measures to specify which relationship to use for a given calculation.. Option A is correct because the USERELATIONSHIP function in DAX allows the developer to temporarily activate an inactive relationship for a specific calculation. In this scenario, one relationship (e.g., between Orders[OrderDate] and a date table) is active, while the other (e.g., between Orders[ShipDate] and the same or another date table) is inactive. By using USERELATIONSHIP in measures, the developer can specify which relationship to use for filtering or aggregation, enabling analysis by both order date and ship date without altering the model's default behavior.

Answer

Merge the OrderDate and ShipDate columns into a single column and use a flag to differentiate.

Answer

Use a single date table with an active relationship to OrderDate and an inactive relationship to ShipDate.

Question 8

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model contains a large fact table with sales data. Users report that reports using this model are slow. Which design change would most improve query performance?

Accepted Answer

Remove unnecessary columns from the fact table.. Option C is correct because removing unnecessary columns from the fact table reduces the amount of data that must be transferred from SQL Server to Power BI for each query. In DirectQuery mode, every report interaction sends a query to the source database, so fewer columns mean smaller result sets and faster query execution. This directly addresses the performance bottleneck caused by a large fact table without changing the underlying storage mode.

Answer

Remove all relationships between tables.

Answer

Switch the model to Import mode.

Answer

Disable the 'Reduce queries' option in report settings.

Question 9

A data analyst is designing a star schema in Power BI. The model includes a table named 'Orders' with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount. Which column should NOT be included in the fact table to maintain a proper star schema?

Accepted Answer

CustomerID. In a star schema, fact tables store quantitative, measurable data (measures) and foreign keys to dimension tables. CustomerID is a descriptive attribute that belongs in a Customer dimension table, not in the fact table. Including it in the fact table would violate normalization principles and reduce the model's flexibility for filtering and hierarchy navigation.

Answer

Quantity

Answer

SalesAmount

Answer

OrderID

Question 10

A Power BI developer is troubleshooting a report that uses a calculated table. The calculated table is defined as: 'Sales Summary = SUMMARIZE(Sales, Sales[ProductID], "Total Sales", SUM(Sales[Amount]))'. Users report that the 'Total Sales' column shows incorrect values when slicers are applied to the report. What is the most likely cause?

Accepted Answer

Calculated tables are static and do not respond to slicer selections.. Calculated tables in Power BI are evaluated at data refresh time and stored in the model as static data. They do not respond to slicer selections or any other report-level filters because they are not recalculated in the query context. Therefore, the 'Total Sales' column in the 'Sales Summary' table will always show the same aggregated values regardless of slicer interactions, which is why users see incorrect values when applying slicers.

Answer

The calculated table lacks a relationship to the Sales table.

Answer

The SUMMARIZE function syntax is incorrect.

Answer

The calculated table is not marked as a date table.

Question 11

A company has a Power BI semantic model that uses Import mode. The model contains a table with 10 million rows. The data source is a SQL Server view that takes 5 minutes to execute. The scheduled refresh is set to every hour. What is the likely impact on refresh performance?

Accepted Answer

Refresh will take at least 5 minutes plus data loading time.. Option D is correct because the refresh process must first execute the SQL Server view to retrieve data, which takes at least 5 minutes, and then load that data into the Import mode model. The total refresh time is the sum of the query execution time and the data loading time, so it will be at least 5 minutes plus additional time for loading 10 million rows.

Answer

Refresh will fail due to timeout on the gateway.

Answer

The model will automatically use incremental refresh to split the load.

Answer

Refresh will complete in parallel with the view execution.

Question 12

A data modeler is creating a Power BI semantic model for a retail company. The model includes a 'Products' dimension table with columns ProductID, ProductName, Category, and Subcategory. The 'Sales' fact table has columns ProductID, Date, Quantity, and Revenue. The modeler wants to ensure that users can filter by Category and Subcategory. Which relationship type should be created between Products and Sales?

Accepted Answer

One-to-many (Products to Sales). In a Power BI semantic model, a one-to-many relationship from Products (the dimension table) to Sales (the fact table) is the standard star schema design. This allows users to filter Sales data by any attribute in Products, such as Category and Subcategory, because each ProductID in Products is unique (the 'one' side) and can be associated with many rows in Sales (the 'many' side). This relationship type ensures proper cross-filtering and aggregation behavior.

Answer

Many-to-one (Sales to Products)

Answer

Many-to-many

Answer

One-to-one

Question 13

A Power BI developer is designing a semantic model that will be used by multiple departments. The developer wants to ensure that the model follows best practices for performance and usability. Which TWO actions should the developer take?

Accepted Answer

Use a star schema with dimension and fact tables.. A star schema organizes data into dimension and fact tables, reducing data redundancy and improving query performance by enabling efficient aggregations and filter propagation. This design aligns with Power BI best practices for semantic models, as it minimizes the number of tables and relationships, leading to faster DAX calculations and better compression.

Answer

Use calculated columns instead of measures for aggregations.

Answer

Combine all data into a single flat table to simplify the model.

Answer

Enable bidirectional cross-filtering between all tables.

Question 14

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model includes a large fact table with 100 million rows. Users are experiencing slow report performance. Which THREE actions should the developer take to improve query performance?

Accepted Answer

Configure incremental refresh to limit data retrieved per query.. Option A is correct because incremental refresh limits the amount of data retrieved per query by partitioning the fact table into smaller, manageable date ranges. In a DirectQuery model, this reduces the volume of data scanned by SQL Server for each query, directly improving report performance by minimizing the data transfer and query execution time.

Answer

Hide columns that are not needed in reports.

Answer

Add calculated columns to precompute aggregations.

Question 15

What is the most likely cause of the error in the DAX query shown in the exhibit?

Accepted Answer

The [Sales Amount] measure is returning multiple values or is not a valid scalar measure.. The error 'A single value for column 'Sales Amount' in table 'Sales' cannot be determined' occurs because the [Sales Amount] measure is being used in a context that expects a scalar value, but the measure is defined to return multiple values (e.g., using SUMX over a table that returns multiple rows without proper aggregation). In DAX, measures used in EVALUATE statements must return a single scalar value; if the measure is not properly aggregated or contains a many-to-many relationship, it can produce multiple values, causing this error.

Answer

There is no relationship between Date and Product tables.

Answer

The SUMMARIZECOLUMNS function does not support multiple group-by columns.

Answer

The ORDER BY clause is not allowed in EVALUATE statements.

Question 16

After loading the data using the Power Query M code shown in the exhibit, the model contains a table with 10,000 rows. However, when users filter by OrderDate in a report, the filter does not affect the aggregated TotalDue values. What is the most likely reason?

Accepted Answer

The data is pre-aggregated in Power Query, so report-level filters cannot re-aggregate.. Option C is correct because the GROUP BY and aggregation happen in Power Query before loading, so the data is pre-aggregated and static. Filters applied in the report cannot re-aggregate because the underlying detail is lost. Option A is wrong because the Date table relationship is not the issue; the data is already aggregated. Option B is wrong because the data type is fine. Option D is wrong because the refresh is not related.

Answer

The TotalDue column is not a numeric type.

Answer

The dataset is not refreshing properly.

Answer

The Date table has no relationship to the Sales table.

Question 17

A company has a fact table with sales data and multiple dimension tables. They want to create a measure that calculates the total sales amount for the current year, but the measure returns incorrect results when used in a visual with a date hierarchy. What is the most likely cause?

Accepted Answer

The relationship between the date table and the fact table is inactive.. Option C is correct because if the relationship between the date table and the fact table is inactive, measures that rely on time intelligence functions (like TOTALYTD, SAMEPERIODLASTYEAR, or a simple SUM with date filtering) will not automatically propagate filters from the date hierarchy to the fact table. In Power BI, only one active relationship can exist between two tables; inactive relationships require explicit activation via USERELATIONSHIP in DAX. Without that, the measure ignores the date filter and returns incorrect or blank results.

Answer

The date table is not marked as a date table in Power BI.

Answer

The fact table is not in a star schema; it is snowflaked.

Answer

The relationship between the date table and the fact table is set to bidirectional cross-filtering.

Question 18

A data model contains a table 'Sales' with columns: Date, ProductID, Quantity, Amount. There is a 'Products' table with columns: ProductID, ProductName, CategoryID. A measure 'Total Sales' = SUM(Sales[Amount]) returns correct values. However, when a user creates a visual with CategoryID from 'Products' and 'Total Sales', some categories show blank. What is the most likely cause?

Accepted Answer

There are ProductID values in Sales that do not exist in Products table.. Option A is correct because when ProductID values in the Sales table do not have matching entries in the Products table, the relationship between the two tables will result in blank CategoryID values for those unmatched rows. In Power BI, a many-to-one relationship (the default) filters from the 'one' side (Products) to the 'many' side (Sales), but if a Sales row has a ProductID not present in Products, it cannot be matched, and any column from Products (like CategoryID) will appear as blank in visuals. This is a classic data integrity issue where the fact table contains orphaned foreign keys.

Answer

The 'Total Sales' measure is not properly referencing the Sales table.

Answer

The relationship between Sales and Products is set to many-to-one, single direction.

Answer

The relationship is set to both directions (bidirectional).

Question 19

A Power BI report uses a DirectQuery data source. The model includes a calculated column that uses the RELATED function to bring a value from another table. The report is performing slowly. What design change would most improve performance without compromising functionality?

Accepted Answer

Replace the calculated column with a calculated table that includes the related column.. Option D is correct because replacing the calculated column with a calculated table that includes the related column moves the join logic to query time, reducing the per-row overhead of the RELATED function in DirectQuery mode. In DirectQuery, calculated columns are evaluated row-by-row for each query, which can cause significant performance degradation; a calculated table is materialized once during refresh (or in DirectQuery, it is resolved as a single query to the source), avoiding repeated row-level lookups. This change preserves the functionality of having the related value available in the table while improving query performance.

Answer

Remove the RELATED function and merge tables in the source query.

Answer

Convert the model to Import mode.

Answer

Use a measure instead of a calculated column.

Question 20

A data model has a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. There is a 'Customers' table with columns: CustomerID, CustomerName. To analyze orders by customer, what is the best practice for modeling the relationship?

Accepted Answer

Create a one-to-many relationship from Customers to Orders with single direction.. Option A is correct because in a star schema, the Customers table (dimension) should have a one-to-many relationship to the Orders table (fact) filtered from the dimension side. This single-direction filter propagation ensures that when a customer is selected, only their orders are shown, while preventing unwanted cross-filtering from orders back to customers. This is the standard best practice for modeling dimension-to-fact relationships in Power BI.

Answer

Create a one-to-one relationship between Customers and Orders based on CustomerID.

Answer

Create an inactive relationship and use USERELATIONSHIP in measures.

Answer

Create a many-to-one relationship from Orders to Customers with both directions.