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

Question 1

You are building a Power BI semantic model that combines sales data from an on-premises SQL Server database and a cloud-based CRM system. The CRM system returns data in a de-normalized format with multiple fact tables. You need to design the model to minimize data duplication and improve query performance. What should you do?

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

Accepted Answer

Import both sources into Power BI and create relationships.. Option D is correct because importing both sources into Power BI and creating relationships lets the semantic model use its in-memory VertiPaq engine, which compresses data, minimizes duplication through shared dimension tables, and delivers the fastest query performance for a star-schema design. Import mode also allows you to normalize the de-normalized CRM fact tables by splitting them into proper fact and dimension tables with defined relationships. Option A is wrong because exporting CRM data to Excel adds a fragile manual step and does not reduce duplication or improve performance. Option B is wrong because a SQL Server view joining both sources cannot span an on-premises SQL Server and a cloud CRM without additional linked-server or gateway complexity, and importing the view still materializes a single wide table rather than a relational model. Option C is wrong because DirectQuery leaves data in the source systems, so every visual issues remote queries, which typically performs worse and does not address duplication.

Answer

Export the CRM data to Excel and import that Excel file along with the SQL Server data.

Answer

Create a SQL Server view that joins both sources and import the view.

Answer

Use DirectQuery for both sources to keep data in their original databases.

Question 2

You have a Power BI semantic model with a date table that spans from January 1, 2020 to December 31, 2025. You want to calculate year-to-date (YTD) sales for the current year. Which DAX formula should you use?

Accepted Answer

SalesYTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]). TOTALYTD is the dedicated DAX time-intelligence function that evaluates the given expression (SUM(Sales[Amount])) over the year-to-date period ending at the last date in the current filter context, using the marked date table 'Date'[Date]. It automatically handles the year boundary and requires a contiguous, marked date table, which the scenario provides (2020-2025). Option A is just a plain sum with no time intelligence, so it returns total sales, not YTD. Option B uses DATESYTD inside CALCULATE, which can work but is not the formula requested here and is less direct than TOTALYTD. Option D uses SAMEPERIODLASTYEAR, which returns the equivalent period from the prior year, not year-to-date for the current year.

Answer

SalesYTD = SUM(Sales[Amount])

Answer

SalesYTD = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date]))

Answer

SalesYTD = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

Question 3

You have a Power BI semantic model that uses row-level security (RLS). The model contains a table 'Employees' with columns: EmployeeID, ManagerID, Region. You need to configure RLS so that a manager can see only the data for employees who report to them, including indirect reports (i.e., the entire hierarchy under the manager). What is the best approach?

Accepted Answer

Use RLS with a DAX filter that uses PATH and PATHCONTAINS to include all descendants.. It uses the DAX PATH and PATHCONTAINS functions to traverse the Employees hierarchy and return all direct and indirect reports for the signed-in manager, which is exactly the requirement for hierarchical RLS. PATH builds the lineage from the manager down through ManagerID relationships, and PATHCONTAINS filters the Employees table to rows whose path includes the current manager's EmployeeID. Option B only compares ManagerID to USERPRINCIPALNAME(), which would not work unless ManagerID stored the user principal name and would not include indirect reports. Option C could work if the column were maintained, but it is not the best approach because it requires extra data modeling and maintenance rather than native DAX hierarchy functions. Option D is impractical and does not scale, since it would require a separate role per manager and manual user assignment.

Answer

Use RLS with a DAX filter like 'Employees[ManagerID] = USERPRINCIPALNAME()'.

Answer

Add a column to the Employees table that lists all employees under each manager, then filter on that column.

Answer

Create a separate role for each manager and assign users accordingly.

Question 4

Which THREE factors should you consider when designing a star schema for a Power BI semantic model? (Select three.)

Accepted Answer

Fact tables should contain measures and foreign keys to dimension tables.. Option A is correct because in a star schema the fact table stores the numeric, aggregatable measures (such as Sales Amount or Quantity) plus foreign keys that reference each related dimension table, forming the center of the star. Option B is correct because dimension tables hold the descriptive, filterable attributes (like Product Name, Category, or Customer City) and are intentionally denormalized into a flat, wide structure to simplify relationships and improve query performance in Power BI. Option E is correct because many-to-many relationships between dimensions add ambiguity and complexity to filter propagation and DAX evaluation, so a well-designed star schema avoids them in favor of clean one-to-many relationships from dimensions to facts. Option C is not correct because fact tables should be denormalized, not normalized — normalization is a characteristic of dimension design trade-offs, and reducing duplication is not the goal of the fact table. Option D is not correct because calculated columns are a modeling technique, not a star-schema design factor, and deriving attributes is typically done in Power Query or the source rather than being a defining consideration for the schema itself.

Answer

Fact tables should be normalized to reduce data duplication.

Answer

Use calculated columns in dimension tables to derive new attributes.

Question 5

Refer to the exhibit. You have a Power BI semantic model with a role 'Sales Manager'. The role is defined with read permission on the Sales table but only on the Amount column. When the user 'salesmgr@contoso.com' creates a report using this model, which of the following will they be able to see?

Accepted Answer

Only the Amount column from the Sales table.. The correct answer is C: the user will see only the Amount column from the Sales table. In a Power BI semantic model, row-level security (RLS) roles can restrict both rows and columns; when a role grants read permission on the Sales table but limits it to the Amount column, users assigned to that role can query only that column. Since salesmgr@contoso.com is the user the scenario describes as creating the report, the effective permission is the Amount column only. Option A is wrong because the role does not grant access to all columns or all tables, and option D is wrong because the role explicitly excludes the other Sales columns. Option B is wrong because the scenario implies the role applies to this user, so data is not blocked entirely.

Answer

All columns in all tables in the model.

Answer

No data because the role is not assigned to the user.

Answer

All columns in the Sales table.

Question 6

You are building a Power BI semantic model that combines sales data from an on-premises SQL Server database and a cloud-based CRM system. The CRM system returns data in a de-normalized format with multiple fact tables. You need to design the model to minimize data duplication and improve query performance. What should you do?

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

Accepted Answer

Patients as a dimension, Visits as a fact table, Procedures as a fact table with a many-to-one relationship to Visits.. Option C is correct because it builds a proper star schema: Patients becomes a dimension table, while Visits and Procedures are fact tables, with Procedures related many-to-one to Visits via VisitID, so procedure costs can be sliced by patient demographics (through Visits to Patients) and by date. This preserves granularity, avoids redundant storage, and lets Power BI filter costs by gender, age group, year, quarter, and month efficiently. Option A is wrong because DirectQuery is a storage/query mode, not a schema design, and simply relating all three tables does not establish the needed fact-dimension roles. Option B is wrong because splitting into separate models and combining via a composite model adds unnecessary complexity and does not by itself produce the required star schema. Option D is wrong because merging everything into one flat table denormalizes the data, increases redundancy, and loses the dimensional structure needed for flexible analysis.

Answer

Use DirectQuery and create relationships between all three tables.

Answer

Create a separate model for visits and another for procedures, then combine them in a composite model.

Answer

Merge all tables into a single flat table in Power Query.

Question 7

You are developing a Power BI report for a sales team. The data source is an Excel file with a single sheet containing columns: Date, Salesperson, Product, Units Sold, Revenue. The file is updated weekly. You need to model the data to allow analysis of revenue by salesperson and product over time. What is the simplest and most effective approach?

Accepted Answer

Import the Excel file into Power BI and create a date table for time intelligence.. Importing the single-sheet Excel file directly into Power BI and adding a dedicated date table is the simplest way to enable time intelligence (e.g., YTD, YoY) for analyzing revenue by salesperson and product over time. A proper date table marked as a date table lets Power BI build the necessary relationships and time-based calculations without extra infrastructure. Option A adds unnecessary complexity and cost by introducing SQL Server for a small weekly-updated file. Option C is invalid because DirectQuery is not supported for Excel workbooks as a data source. Option D over-engineers the model by manually splitting into a star schema, which is not required for this simple scenario.

Answer

Load the data into a SQL Server database first, then import from there.

Answer

Use DirectQuery on the Excel file to avoid importing.

Answer

Split the Excel data into separate dimension and fact tables in Power Query.

Question 8

You are a data modeler for a logistics company. The company uses Power BI to analyze shipment data. The source system is a SQL Server database with tables: Shipments (ShipmentID, ShipDate, CustomerID, Weight, Cost, CarrierID), Customers (CustomerID, CustomerName, Region), Carriers (CarrierID, CarrierName, Mode). The database is updated in real-time. You need to build a semantic model that supports near real-time reporting with maximum data freshness while maintaining good query performance. The model should allow users to filter by customer region and carrier mode, and calculate total cost and weight per month. What is the best approach?

Accepted Answer

Use DirectQuery on the three tables and create a date table in the model.. DirectQuery on the three tables keeps the model connected to the live SQL Server data, providing near real-time freshness, while a model-created date table enables the required month-level total cost and weight calculations and supports filtering by customer region and carrier mode through the existing relationships. DirectQuery avoids importing stale data and lets the source database handle the joins and aggregations, which is appropriate for a real-time updated source. Option A would import a snapshot, so it cannot meet maximum data freshness. Option B uses DirectQuery but a single joined view can hurt query performance and flexibility compared with separate tables and proper relationships. Option D is a composite model that imports Customers and Carriers, so changes to those dimensions would not be reflected in near real time, reducing freshness.

Answer

Import the three tables into Power BI and create a star schema with relationships.

Answer

Create a single view in SQL Server that joins all tables and use DirectQuery on that view.

Answer

Use a composite model: import Customers and Carriers, and use DirectQuery on Shipments.

Question 9

Which TWO actions should you take to optimize a star schema in Power BI?

Accepted Answer

Create a date table and mark it as a date table.. Creating a date table and setting it as a date table is a best practice for time intelligence. Creating calculated columns in the fact table is generally discouraged because it increases storage and query time.

Answer

Denormalize dimension tables into the fact table.

Answer

Use bidirectional cross-filtering for all relationships.

Answer

Add calculated columns to the fact table for filtering.

Question 10

Which TWO DAX functions can be used to implement row-level security (RLS) in Power BI?

Accepted Answer

FILTER. The two DAX functions commonly used to implement row-level security (RLS) in Power BI are FILTER and USERNAME. FILTER is used within RLS expressions to restrict rows, while USERNAME returns the current user's domain\username. USERPRINCIPALNAME is also available but is not required; the question asks for two. LOOKUPVALUE retrieves a single value and is not used for RLS. CALCULATE modifies filter context but is not directly used in RLS filtering expressions.

Answer

LOOKUPVALUE

Answer

CALCULATE

Answer

USERPRINCIPALNAME

Question 11

Which THREE data types are supported in Power Query for Power BI?

Accepted Answer

Decimal Number. Power Query's type system includes Decimal Number (B), which stores 64-bit floating-point numeric values for precise decimal calculations, Text (D), which holds Unicode character strings, and Boolean (E), which stores TRUE/FALSE logical values — all three are native, selectable data types in the Power Query Editor and M language. Array (A) and Object (C) are not Power Query data types; they are JSON/JavaScript-style constructs, and while Power Query can parse JSON containing arrays and records, its own type system uses List and Record instead, so these options do not belong.

Answer

Array

Answer

Object

Question 12

Which THREE actions can improve query performance in a Power BI DirectQuery model?

Accepted Answer

Limit the columns retrieved from the source.. Reducing columns and rows selected reduces data transfer. Increasing the number of visuals on a page can increase the number of queries, hurting performance. Using summarized tables can reduce the amount of data queried. Creating calculated columns in Power Query (rather than DAX) improves performance because they are materialized at refresh.

Answer

Create calculated columns in DAX rather than Power Query.

Answer

Add more visuals to a single page to reduce page count.

Question 13

You are a Power BI developer for a healthcare organization. You have a semantic model that contains a table named PatientVisits with columns: VisitID, PatientID, VisitDate, DiagnosisCode, and Charges. You have a separate table named Diagnosis with columns: DiagnosisCode, DiagnosisDescription, and Category. The Diagnosis table contains duplicate rows for the same DiagnosisCode but with different descriptions due to data entry errors. You need to create a relationship between PatientVisits and Diagnosis on DiagnosisCode. However, the duplicate values in Diagnosis prevent creating a one-to-many relationship. You must ensure that the relationship can be created without removing any rows from the Diagnosis table. What should you do?

Accepted Answer

Create a calculated table in DAX using SUMMARIZE(Diagnosis, Diagnosis[DiagnosisCode], "Description", FIRSTNONBLANK(Diagnosis[DiagnosisDescription], 1)) and use that table for the relationship.. Option A is correct because it creates a new calculated table that has exactly one row per DiagnosisCode, which satisfies the one-to-many relationship requirement while leaving the original Diagnosis table untouched, so no rows are removed. SUMMARIZE groups by DiagnosisCode and FIRSTNONBLANK picks a single representative DiagnosisDescription, eliminating the duplicate-key problem. Option B is wrong because removing duplicate rows deletes data from the Diagnosis table, which the scenario explicitly forbids. Option C is wrong because a many-to-many relationship does not resolve the duplicate-key issue and is not the appropriate fix here. Option D is wrong because concatenating VisitID and DiagnosisCode creates a key that does not match DiagnosisCode in the Diagnosis table, so the relationship could not be created on that key.

Answer

In Power Query, remove duplicate rows based on DiagnosisCode, keeping the first occurrence.

Answer

Create a many-to-many relationship between PatientVisits and Diagnosis.

Answer

Create a composite key in PatientVisits by concatenating VisitID and DiagnosisCode, and relate to Diagnosis on that key.

Question 14

You are a data analyst for a financial services firm. You have a Power BI semantic model that uses DirectQuery mode against a SQL Server data warehouse. The model includes a large fact table named Transactions with billions of rows. Users are complaining that the report is slow when filtering by date range. You need to improve the performance of date-related queries without changing the data source structure. You cannot switch to Import mode due to data volume constraints. What should you do?

Accepted Answer

Create a date dimension table in the SQL Server data warehouse and use that table in the Power BI model for date filtering. Ensure the date column is indexed.. Option B is correct because in DirectQuery mode, Power BI pushes filtering and aggregation to the source, so a dedicated, indexed date dimension table lets date-range filters be resolved with efficient indexed lookups and joins rather than scanning the huge Transactions fact table. The indexed date column in the SQL Server warehouse further ensures the source can satisfy the date predicates quickly without changing the fact table structure. Option A is invalid because Import mode is explicitly ruled out due to data volume constraints. Option C is wrong because adding an index to the fact table's date column still leaves date filtering on the large fact table and does not provide the optimized dimension-based filtering path. Option D is wrong because a DAX calculated table in DirectQuery mode does not create a source-side indexed structure and cannot be refreshed or persisted like a warehouse table, so it does not improve query performance.

Answer

Switch to Import mode for the Transactions table and schedule frequent refreshes.

Answer

Add an index on the date column in the fact table and use the date column directly in visuals.

Answer

Create a calculated table in Power BI using DAX to generate a distinct list of dates from the Transactions table.

Question 15

You are a Power BI developer for an e-commerce company. You have a semantic model that contains a table named Orders with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount. You also have a table named Products with columns: ProductID, ProductName, Category, and Price. You need to create a measure that calculates the percentage of total sales for each product category. The measure should be used in a matrix visual with Category on rows. Which DAX expression should you use?

Accepted Answer

% Total Sales = DIVIDE(SUM(Orders[SalesAmount]), CALCULATE(SUM(Orders[SalesAmount]), ALLSELECTED(Products[Category]))). It uses DIVIDE to safely compute the ratio of the current category's sales to the total sales across all categories visible in the matrix. The denominator uses CALCULATE with ALLSELECTED(Products[Category]), which removes the row filter on Category while respecting any external slicers or filters, yielding the correct grand total for the percentage calculation. Option B divides by a total over ALLSELECTED(Orders), which removes filters on the Orders table rather than on Products[Category], so the denominator would not represent the total across categories. Option C uses ALLEXCEPT(Products, Products[Category]), which keeps the Category filter and removes all other Product columns, so the denominator would still be filtered by the current category and produce 100%. Option D uses ALL(Products), which ignores external slicer selections and would compute a percentage against an unfiltered total rather than the currently visible total.

Answer

% Total Sales = SUM(Orders[SalesAmount]) / CALCULATE(SUM(Orders[SalesAmount]), ALLSELECTED(Orders))

Answer

% Total Sales = DIVIDE(SUM(Orders[SalesAmount]), CALCULATE(SUM(Orders[SalesAmount]), ALLEXCEPT(Products, Products[Category])))

Answer

% Total Sales = DIVIDE(SUM(Orders[SalesAmount]), CALCULATE(SUM(Orders[SalesAmount]), ALL(Products)))