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

Question 1

You are a Power BI analyst at a marketing agency. You have a data model with a 'Campaigns' table (columns: CampaignID, CampaignName, StartDate, EndDate, Budget, Spent) and a 'Results' table (columns: ResultID, CampaignID, Date, Impressions, Clicks, Conversions). You need to model the data to analyze campaign performance over time. You create a relationship between Campaigns and Results on CampaignID (one-to-many). You also create a date dimension 'Calendar' and relate it to Results[Date]. However, when you create a measure to calculate the total budget by month, the budget for a campaign that spans multiple months is counted in each month, leading to double-counting. What should you do to accurately allocate the budget across months?

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

Accepted Answer

Create a calculated table using CROSSJOIN between Campaigns and Calendar, then use it as a bridge. Creating a bridge table using CROSSJOIN between Campaigns and Calendar will allow you to allocate the budget across months without double-counting. This bridge table creates a many-to-many relationship that enables you to distribute the budget proportionally (e.g., by number of days in the month that the campaign is active). Option B is incorrect because changing the relationship to bidirectional would filter both tables but still not allocate budget correctly; it can also cause ambiguous filtering. Option C is incorrect because USERELATIONSHIP activates an inactive relationship, but no such relationship exists here. Option D is incorrect because simply dividing the budget by the number of months is arbitrary and does not account for the actual duration or partial months.

Answer

Change the relationship between Campaigns and Results to bidirectional

Answer

Use the DAX function USERELATIONSHIP to activate an inactive relationship

Answer

Add a calculated column to Campaigns that divides the budget by the number of months

Question 2

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?

Accepted Answer

Import both sources into Power BI and create relationships.. Importing both sources into Power BI and creating relationships allows the Power BI engine to optimize queries using in-memory storage and star schema design. This minimizes data duplication by storing each table once and leveraging relationships for aggregation. Option A is wrong because exporting CRM data to Excel introduces manual steps, data staleness, and doesn't leverage Power BI's modeling capabilities. Option B is wrong because creating a SQL Server view that joins both sources results in a single flattened table, causing data duplication when multiple fact tables from CRM are combined with the SQL Server data, and it loses the ability to define relationships in Power BI. Option C is wrong because DirectQuery for both sources would keep data in their original databases, but queries would be sent to the source systems each time, leading to performance overhead, especially with on-premises SQL Server and cloud CRM.

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 3

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 function for year-to-date calculations, automatically filtering the date range from the start of the year to the latest date in the current context. Option A is incorrect because it returns total sales without any time filtering. Option B uses DATESYTD, which is a valid alternative, but TOTALYTD is the more direct and efficient function for YTD. Option D is incorrect because SAMEPERIODLASTYEAR compares to the same period in the previous year, which does not compute YTD.

Answer

SalesYTD = SUM(Sales[Amount])

Answer

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

Answer

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

Question 4

You are designing a Power BI semantic model for a retail company. The model includes a Sales table (50 million rows) and a Product table (10,000 rows). You need to create a measure that calculates the average sales amount per product category. The Product table has a column 'Category' with 20 distinct values. To optimize performance, what should you do?

Accepted Answer

Create a relationship between Sales and Product on ProductID, and use Product[Category] in the measure.. Creating a relationship between Sales and Product on ProductID allows you to use the Product[Category] column directly in the measure through implicit cross-filtering. This leverages the storage engine's ability to aggregate efficiently without materializing additional columns. Option A is incorrect because adding a calculated column to Sales using RELATED would increase model size and processing time for 50 million rows. Option B is incorrect because summarizing the Sales table to category level in Power Query would lose row-level granularity needed for other analyses and sacrifices performance gains from the relationship. Option C is incorrect because a separate calculated table for categories would be redundant and increase model complexity without benefit.

Answer

Add a calculated column to Sales that looks up the category using RELATED.

Answer

Summarize the Sales table to the category level in Power Query.

Answer

Create a separate calculated table for categories and link it to Sales.

Question 5

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 DAX functions PATH and PATHCONTAINS to dynamically traverse the employee-manager hierarchy and filter data so that the current user (manager) sees only their direct and indirect reports. This approach is scalable and handles any depth of hierarchy. Option B is incorrect because it only filters direct reports (ManagerID equals user's principal name), not indirect reports. Option C is incorrect because adding a static column listing all subordinate employees per manager would require manual updates and is not dynamic. Option D is incorrect because creating a separate role per manager is not scalable and requires maintenance as the organization changes.

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 6

Which TWO actions should you take to improve the performance of a DirectQuery model in Power BI? (Select two.)

Accepted Answer

Limit the columns selected to only those needed in the report.. Options A and C are correct. Limiting columns reduces data transfer, and pushing filters to the source database reduces the amount of data processed by Power BI. Option B is wrong because calculated columns in DirectQuery are pushed to the source and can degrade performance. Option D is wrong because aggregations are not supported in DirectQuery mode; they are used in Import mode. Option E is wrong because row-level security filters add overhead and do not improve performance.

Answer

Use calculated columns instead of measures to precompute values.

Answer

Create aggregations on the imported tables.

Answer

Implement row-level security filters on the fact table.

Question 7

Which TWO are valid reasons to use a date table in a Power BI semantic model? (Select two.)

Accepted Answer

To enable time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR.. The correct answers are C and E. A date table is specifically required to enable time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR (Option C). It also ensures consistent date filtering across multiple fact tables by providing a single authoritative date dimension (Option E). Option A is incorrect because date tables do not affect non-date hierarchies. Option B is incorrect because automatic date hierarchies are generated by default in Power BI without a separate date table. Option D is incorrect because relationships between tables are not improved by a date table; they are defined by keys regardless.

Answer

To allow users to drill down into non-date hierarchies.

Answer

To automatically generate date hierarchies in visuals.

Answer

To improve the performance of relationships between tables.

Question 8

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 role 'Sales Manager' is defined with row-level security (RLS) that restricts read access to only the Amount column in the Sales table. Since the user 'salesmgr@contoso.com' is assigned this role, they can only see the Amount column from the Sales table. Option C is correct because the role explicitly limits access to that single column. Option A is incorrect because the user cannot see all columns in all tables; only the Amount column is visible. Option B is incorrect because the role is assigned to the user, so they can see the Amount column. Option D is incorrect because the user cannot see all columns in the Sales table; only the Amount column is accessible.

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 9

You are a data analyst at a multinational retail company. The company uses Microsoft Power BI to analyze sales data from multiple regions. The source data is stored in Azure SQL Database and includes tables: Sales (OrderID, ProductID, Quantity, Amount, OrderDate, StoreID), Stores (StoreID, StoreName, Region, Country), Products (ProductID, ProductName, Category, Price). The model is imported daily. You need to design a semantic model that supports the following requirements: 1) Allow users to filter by year and month using a single slicer. 2) Ensure that time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) work correctly. 3) Minimize model size. 4) Provide a consistent date dimension for all fact tables. The Sales table has orders from 2018-01-01 to 2025-12-31. You decide to create a date table. Which of the following approaches should you take?

Accepted Answer

Create a date table using DAX with CALENDAR and mark it as a date table.. Creating a date table using DAX with CALENDAR and marking it as a date table satisfies all requirements: it provides a consistent date dimension, enables time intelligence functions (like TOTALYTD, SAMEPERIODLASTYEAR), and minimizes model size by not importing extra columns. Option B is wrong because using Sales[OrderDate] directly does not create a separate date dimension and may cause issues with time intelligence. Option C is wrong because auto-generated date hierarchies cannot be marked as a date table and may not support all time intelligence functions. Option D is wrong because importing a full date table from the source database adds unnecessary overhead and may include extra attributes that increase model size.

Answer

Use the Sales[OrderDate] column directly and create a calculated column for year and month.

Answer

Use Power BI's auto-generated date hierarchy for each date column.

Answer

Import a date table from the source database that includes all dates and additional attributes.

Question 10

You are building a Power BI semantic model for a healthcare provider. The model must track patient visits and procedures. You have three source tables: Patients (PatientID, Name, DOB, Gender), Visits (VisitID, PatientID, VisitDate, DoctorID), Procedures (ProcedureID, VisitID, ProcedureCode, Cost). The model should allow users to analyze procedure costs by patient demographics (gender, age group) and by date (year, quarter, month). You need to design the star schema. Which of the following is the most appropriate design?

Accepted Answer

Patients as a dimension, Visits as a fact table, Procedures as a fact table with a many-to-one relationship to Visits.. The most appropriate star schema design. Patients is a dimension table (one row per patient) containing demographic attributes like gender and date of birth for grouping and filtering. Visits is a fact table (one row per visit) with a foreign key to Patients (PatientID) and a date column (VisitDate), enabling time-based analysis. Procedures is another fact table (one row per procedure) with a foreign key to Visits (VisitID) and a measure (Cost), allowing cost analysis by both patient demographics (via the relationship through Visits to Patients) and date (via Visits). This design conforms to star schema principles: dimensions for descriptive attributes and facts for quantitative data, with clear granularity and relationships.

Option A is incorrect because DirectQuery is a storage mode, not a schema design choice. While relationships between tables are needed, DirectQuery alone does not define a star schema and may lead to performance issues with large data volumes.

Option B is incorrect because creating separate models for visits and procedures prevents combined analysis (e.g., procedure costs by patient demographics) and adds unnecessary complexity with composite models.

Option D is incorrect because merging all tables into a single flat table creates a wide table with redundant data (e.g., patient info repeated for each procedure), leading to poor performance and maintenance challenges. A star schema normalizes dimensions and facts for efficient querying.

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 11

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.. The correct approach. Importing the Excel file into Power BI and creating a dedicated date table enables time intelligence functions (e.g., YTD, QoQ) for analyzing revenue trends. Option A is unnecessarily complex and adds latency; Power BI can import Excel directly. Option C is invalid because DirectQuery does not support Excel files; only import is supported for Excel. Option D is overkill for a single sheet; Power Query can create a date table and define relationships without splitting the source data.

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 12

You are a Power BI developer for a financial services company. The company has a large transactional database in Azure Synapse Analytics. The database contains a table 'Transactions' with 2 billion rows. The table includes columns: TransactionID, AccountID, TransactionDate, Amount, Type (Deposit/Withdrawal), Status (Pending/Completed). You need to build a Power BI semantic model that allows executives to analyze monthly trends of completed deposit amounts by account type (e.g., Savings, Checking). The account type is in a separate 'Accounts' table (1 million rows) with columns: AccountID, AccountType, CustomerID. The model must refresh within 2 hours. Due to the large data volume, you cannot import the entire Transactions table. What should you do?

Accepted Answer

Create an aggregated table in Synapse that pre-aggregates data at the month and account type level, then use DirectQuery on the aggregated table, and use a composite model with the detail table for drill-through.. Creating an aggregated table in Azure Synapse that pre-aggregates data at the month and account type level drastically reduces the data volume. Using DirectQuery on this aggregated table allows fast querying, and a composite model with the detail table enables drill-through to individual transactions when needed. This approach meets the refresh time constraint and handles large data efficiently. Option A is incorrect because incremental refresh with DirectQuery for older data does not solve the initial load issue and can be complex. Option B is incorrect because Power BI aggregations still require the base data to be imported, which is not feasible with 2 billion rows. Option C is incorrect because DirectQuery on the full table would be too slow for analysis.

Answer

Use incremental refresh to import only recent data, and use DirectQuery for older data.

Answer

Import only the necessary columns and use Power BI aggregations to pre-aggregate.

Answer

Use DirectQuery on the full Transactions table and rely on the Synapse query optimizer.

Question 13

You are creating a Power BI report for a small business. The data source is a Microsoft Access database with tables: Customers (CustomerID, CompanyName, City), Orders (OrderID, CustomerID, OrderDate, Amount). You need to model the data to analyze total orders by customer and by month. What is the most efficient approach?

Accepted Answer

Import both tables into Power BI and create a relationship between CustomerID columns.. Importing both tables and creating a relationship between CustomerID columns allows Power BI to normalize the data model, avoid data duplication, and leverage in-memory compression for efficient analysis. Option A is wrong because DirectQuery on an Access database is not recommended due to poor performance and query limitations. Option B is wrong because merging tables in Power Query creates a denormalized view with repeated customer information, increasing model size. Option C is wrong because migrating to SQL Server introduces unnecessary complexity and overhead for a small business.

Answer

Use DirectQuery on the Access database to avoid importing.

Answer

Create a single query in Power Query that joins the tables and import the result.

Answer

Migrate the Access database to SQL Server and then import.

Question 14

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.. The best approach because using DirectQuery on all three tables ensures near-real-time data access from the SQL Server source, and adding a date table in the model enables time intelligence calculations (e.g., total cost and weight per month) while maintaining star-schema relationships. Option A is incorrect because importing the tables creates a snapshot that does not support near-real-time reporting, contradicting the requirement for maximum data freshness. Option B is incorrect because a single flattened view with DirectQuery loses the dimensional model, making it difficult to filter by customer region and carrier mode efficiently and harming query performance. Option D is incorrect because composite models introduce a mix of import and DirectQuery storage; importing Customers and Carriers means those tables are not real-time, potentially leading to stale dimension data and violating the requirement for maximum freshness across the model.

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 15

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.