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

Question 1

You have a Power BI data model with a table 'Orders' that has columns: 'OrderID', 'OrderDate', 'CustomerName', 'Region', 'Product', 'Quantity', 'UnitPrice'. You want to create a measure that calculates total sales amount. Which DAX expression should you use?

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

Accepted Answer

Total Sales = SUMX(Orders, Orders[Quantity] * Orders[UnitPrice]). SUMX is an iterator function that evaluates the expression `Orders[Quantity] * Orders[UnitPrice]` for each row in the Orders table and then sums the results. This is necessary because DAX does not support direct multiplication of two columns inside SUM; SUM expects a single column reference, not an expression. SUMX performs row-by-row evaluation, which correctly computes the total sales amount as the sum of (Quantity × UnitPrice) across all orders.

Answer

Total Sales = COUNTROWS(Orders)

Answer

Total Sales = AVERAGE(Orders[Quantity]) * AVERAGE(Orders[UnitPrice])

Answer

Total Sales = SUM(Orders[Quantity] * Orders[UnitPrice])

Question 2

You are modeling data from an Azure SQL Database into Power BI. The source table 'Sales' contains 10 million rows. You need to ensure that the data model supports fast query performance for a report that shows sales by month and product category. The report uses a slicer for year. What is the best practice for improving performance?

Accepted Answer

Create an aggregate table in Power BI that pre-aggregates sales by month and product category.. Creating an aggregate table in Power BI that pre-aggregates sales by month and product category drastically reduces the number of rows the report must scan, from 10 million to a much smaller set of aggregated rows. This enables fast query performance for the slicer and visual-level filters, as Power BI can leverage the aggregate table via its aggregation feature, which automatically redirects queries to the pre-summarized data when possible.

Answer

Disable the auto-date/time feature.

Answer

Increase the data load frequency to every 15 minutes.

Answer

Use DirectQuery mode to query the source database directly.

Question 3

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 4

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 5

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.. Option A is correct because in a star schema, dimension tables serve as the lookup/reference tables, so they must have a primary key that uniquely identifies each row (e.g., ProductKey, DateKey) plus descriptive attributes such as product name, category, or color used for slicing and grouping in Power BI visuals. Option C is correct because fact tables store the measurable events (sales, quantities, amounts) and must contain foreign keys that relate back to the primary keys of the dimension tables, forming the one-to-many relationships that Power BI's VertiPaq engine and DAX rely on for efficient filtering and aggregation. Option B is not a best practice because calculated columns in fact tables consume memory and storage, and business logic is better handled via measures or in the source/ETL layer to keep the fact table lean. Option D is wrong because flattening all tables into a single wide table defeats the star schema's purpose, causing redundancy, larger model size, and degraded performance. Option E is incorrect because many-to-many relationships between fact and dimension tables are an anti-pattern in star schema design; relationships should be one-to-many from dimension to fact to ensure correct filter propagation and predictable 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 6

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.. Option C is correct because DirectQuery does not cache or import data into Power BI; instead, every visual interaction generates a query that is passed through to the underlying source (e.g., SQL Server, Azure Synapse, Oracle) in real time, so results reflect the source's current state. Option D is correct because row-level security can be defined in the Power BI model and, for DirectQuery sources, the RLS filters are translated into the native query sent to the data source, restricting the rows each user can see. Option E is correct because DirectQuery must translate DAX into the source's query language, so certain DAX functions and constructs (for example, some time intelligence and parent-child functions) are unsupported or behave differently, and Power BI will flag them as not supported in DirectQuery mode. Option A is wrong because importing data describes Import mode, not DirectQuery, which leaves the data in the source. Option B is wrong because relationships between tables can still be created in a DirectQuery model; they are simply used to generate the appropriate joins in the queries sent to the source.

Answer

Data is imported into the Power BI dataset.

Answer

You cannot create relationships between tables.

Question 7

Which TWO of the following are true about the Power BI composite model?

Accepted Answer

A composite model can combine DirectQuery and Import tables.. A composite model in Power BI allows mixing DirectQuery and Import tables within the same data model. This enables you to leverage the performance of in-memory Import storage for some tables while using DirectQuery to access large or real-time data sources without duplicating data.

Answer

Composite models do not support many-to-many relationships.

Answer

All tables in a composite model must use the same storage mode.

Answer

Calculated tables are not supported in composite models.

Question 8

You are building a star schema model in Power BI. You have a fact table of sales transactions and dimension tables for Date, Customer, Product, and Store. The Date table contains a column 'FiscalYear' that you want to use for time intelligence calculations. What is the best practice for handling the Date relationship?

Accepted Answer

Mark the Date table as a date table using the Calendar icon in the Table tools ribbon and set a relationship on the Date column.. Marking the Date table as a date table (via the Calendar icon in Table tools) and creating a relationship on the Date column is the best practice for time intelligence in Power BI. This ensures that DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) work correctly by using a single, continuous date column that aligns with the fact table's date column. It also avoids the need for composite keys or separate fiscal tables, maintaining a clean star schema.

Answer

Create a separate fiscal date table and relate it to the fact table using the FiscalYear column.

Answer

Use the built-in DATESYTD function directly on the OrderDate column from the fact table.

Answer

Create a composite key using FiscalYear and Quarter columns in the Date table and relate to the fact table.

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.. 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).

Answer

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

Answer

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

Question 10

You are designing a Power BI model that includes a fact table with sales data and a dimension table for customers. Each customer can have multiple addresses, but you only need the primary address for analysis. The source system has a 'CustomerAddress' table with a 'IsPrimary' flag. What is the best approach to bring this into the model?

Accepted Answer

In Power Query, filter the CustomerAddress table to only include rows where IsPrimary = True, then merge with Customer.. It uses Power Query to filter the CustomerAddress table to only primary addresses before merging with the Customer dimension. This ensures that only the necessary rows are imported into the model, reducing data volume and avoiding complex DAX or relationship overhead. The result is a clean, single-row-per-customer dimension that directly supports analysis without runtime filtering.

Answer

Use a DAX measure to filter the address table dynamically.

Answer

Import the entire CustomerAddress table and create an active relationship on the CustomerID column.

Answer

Create a calculated table using SUMMARIZE to get the primary address per customer.

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

You are modeling data from multiple sources: a SQL Server database for sales, an Excel file for budget, and a SharePoint list for product targets. You need to combine these into a single Power BI report. What is the recommended approach for handling data refresh?

Accepted Answer

Use Power Query to combine data from all sources into a single dataset, then schedule a daily refresh in the Power BI service.. Power Query (Get Data) in Power BI Desktop is designed to connect to and combine data from multiple heterogeneous sources—SQL Server, Excel, and SharePoint—into a single dataset. After publishing to the Power BI service, you can configure a scheduled refresh (via an on-premises data gateway for on-premises sources) to keep the dataset up to date automatically, which is the recommended approach for recurring refreshes.

Answer

Import each source into separate Power BI Desktop files and manually update.

Answer

Use Excel Online as the single source and import all data into it first.

Answer

Create separate datasets for each source and use composite models with DirectQuery.

Question 13

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.. Option A is correct because a composite model lets some tables use DirectQuery (for real-time or frequently changing data) while other tables remain in Import mode, so you can blend live DirectQuery data with imported historical data in one model. Option B is correct because composite models support aggregation tables, allowing you to keep large fact tables in DirectQuery (or as detail tables) while importing pre-aggregated summary tables to accelerate queries. Option E is correct because the core purpose of a composite model is to combine DirectQuery sources with imported tables in the same semantic model, which is otherwise impossible in a pure Import or pure DirectQuery model. Option C is not a valid reason: composite storage mode does not inherently improve relationship performance, and relationship performance depends on cardinality, cross-filter direction, and model design rather than storage mode. Option D is not a valid reason: calculated tables are created with DAX and are always stored in Import mode, so they cannot be based directly on DirectQuery sources as a benefit of using a composite model.

Answer

To improve relationship performance between tables.

Answer

To create calculated tables based on DirectQuery sources.

Question 14

Which TWO of the following are true about calculated columns in Power BI?

Accepted Answer

Calculated columns are computed using DAX formulas.. Option C is correct because calculated columns are created by writing Data Analysis Expressions (DAX) formulas in the Power BI model, and their values are computed row by row using that DAX expression. Option D is correct because calculated columns are materialized and persisted in the model (in-memory in the VertiPaq engine), so they consume memory and increase the model size. Option A is incorrect because calculated columns can absolutely be used as the key on one side of a relationship, provided their values are unique and valid. Option B is incorrect because calculated columns are evaluated at data refresh/processing time, not at query time; it is measures that are evaluated at query time. Option E is incorrect because calculated columns are not stored on disk and loaded on demand — they are stored in memory as part of the model, unlike DirectQuery or some other storage behaviors.

Answer

Calculated columns cannot be used in relationships.

Answer

Calculated columns are evaluated at query time.

Answer

Calculated columns are stored on disk and loaded on demand.

Question 15

Refer to the exhibit. You apply the above RLS rule to a semantic model. The rule is intended to restrict sales data by the user's region, which is stored in the user's email domain (e.g., user@west.contoso.com). However, the rule does not filter any rows. What is the most likely issue?

Accepted Answer

The filter expression compares the full UPN to the Region column, which likely contains only the region name.. The correct answer is C: the filter expression compares the full UPN to the Region column, which likely contains only the region name. USERPRINCIPALNAME() returns the entire sign-in name such as user@west.contoso.com, so an equality test against a Region value like "West" will never match and no rows are filtered. The rule should instead extract the subdomain or use a mapping table (for example, using SEARCH or PATHITEM-style parsing) to derive the region before comparing. Option A is unlikely because the scenario states the rule is intended to restrict by region and the failure is that it filters nothing, which points to a value mismatch rather than a missing column. Option B is false because USERPRINCIPALNAME() is supported in Power BI for RLS. Option D is incorrect because if the rule were not applied at all, that would be a deployment issue, but the question focuses on the rule's expression logic.

Answer

The Sales table does not have a Region column.

Answer

USERPRINCIPALNAME() is not available in the current Power BI version.

Answer

The RLS rule is not applied to the semantic model.