PL-300 Model the data Practice Test 17 — 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?

Accepted Answer

Import both sources into Power BI and create relationships.. Option B is correct because importing both sources into Power BI and creating relationships between tables allows the Power BI engine to optimize queries in-memory. Option A is wrong because DirectQuery would incur performance overhead with on-premises data. Option C is wrong because combining at the source doesn't reduce duplication and may be complex. Option D is wrong because exporting the CRM data to a single Excel file introduces manual steps and data staleness.

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]). Option B is correct because TOTALYTD calculates year-to-date sales relative to the current context. Option A is wrong because it gives total sales without any time calculation. Option C is wrong because SAMEPERIODLASTYEAR compares to the prior year, not YTD. Option D is wrong because CALCULATE with DATESYTD is redundant; TOTALYTD is the standard function.

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 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.. Option A is correct because marking the Product table as a dimension table with 'Category' as a column allows for efficient aggregations. Option B is wrong because a calculated column in Sales would increase model size. Option C is wrong because a calculated category table would be redundant. Option D is wrong because summarizing the Sales table loses granularity.

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 4

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.. Option D is correct because RLS can only filter rows directly; to handle dynamic hierarchies, you need to use DAX with functions like PATH and PATHCONTAINS to flatten the hierarchy. Option A is wrong because adding a column for each manager is not scalable. Option B is wrong because RLS does not support dynamic hierarchy by itself. Option C is wrong because a role per manager is not dynamic.

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 5

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 D are correct. Reducing the number of columns queried and applying filters early minimize data transfer. Option B is wrong because calculated columns in DirectQuery are pushed to the source and can degrade performance. Option C is wrong because increasing row-level security complexity adds overhead. Option E is wrong because aggregations are not supported in DirectQuery mode.

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 6

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.. Options A, C, and D are correct. Star schema design emphasizes dimension tables with descriptive attributes, fact tables with measures and foreign keys, and avoiding many-to-many relationships. Option B is wrong because normalization is for OLTP, not for analysis. Option E is wrong because calculated columns are generally not recommended for dimension tables.

Answer

Fact tables should be normalized to reduce data duplication.

Answer

Use calculated columns in dimension tables to derive new attributes.

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.. Options A and D are correct. A date table enables time intelligence functions and provides a consistent date dimension. Option B is wrong because date tables are not required for simple calculations. Option C is wrong because date tables don't help with non-date hierarchies. Option E is wrong because relationships are not affected by date tables.

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.. Option A is correct because the role only grants read access to the Amount column. All other columns in the Sales table are not accessible. Option B is wrong because the role does not allow seeing the entire table. Option C is wrong because the role does not include any other tables. Option D is wrong because the role does not block all data.

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.. Option B is correct because creating a date table in DAX using CALENDAR and marking it as a date table satisfies all requirements: it minimizes model size (no extra columns), enables time intelligence, and provides a consistent dimension. Option A is wrong because Power BI's auto-generated date hierarchy cannot be marked as a date table for time intelligence. Option C is wrong because importing a full date table from SQL adds overhead without benefit. Option D is wrong because using the Sales[OrderDate] column directly does not provide a separate date dimension and may cause issues with time intelligence.

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.. Option A is correct: Patients as a dimension, Visits as a fact (or bridge), and Procedures as a fact. This allows cost analysis by patient attributes and date. Option B is wrong because merging all into one table leads to a wide table with redundancy. Option C is wrong because creating separate models for visits and procedures prevents combined analysis. Option D is wrong because using DirectQuery for all tables can cause performance issues.

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.. Option A is correct because importing the Excel file and creating a date table allows for time-based analysis with minimal effort. Option B is wrong because using DirectQuery on an Excel file is not supported. Option C is wrong because splitting into multiple tables is unnecessary for a single sheet. Option D is wrong because Power BI can handle the data without an external database.

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.. Option B is correct because creating an aggregate table in Synapse that pre-computes monthly completed deposit amounts by account type reduces the data volume significantly, and using DirectQuery on the aggregate with the ability to drill down to details via the original table (using composite model or dual storage) provides flexibility. Option A is wrong because DirectQuery on the full table would be slow. Option C is wrong because aggregations in Power BI still require importing the base data. Option D is wrong because incremental refresh does not reduce the data volume for initial load.

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 developing a Power BI semantic model for an e-commerce company. The source data comes from a CSV file containing order details: OrderID, OrderDate, CustomerID, ProductID, Quantity, UnitPrice, Discount, and ShippingCost. The file is updated daily. You need to model the data to support the following analyses: 1) Total sales amount (Quantity * UnitPrice - Discount) by product and month. 2) Average shipping cost per order by customer region (CustomerRegion is in a separate table). 3) Year-over-year comparison of sales. You need to create the measures and ensure optimal performance. What should you do?

Accepted Answer

Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.. Option C is correct because importing the CSV and CustomerRegion table into Power BI and creating a star schema with relationships allows efficient measure calculation. Option A is wrong because calculated columns in source increase storage. Option B is wrong because DirectQuery on CSV is not supported. Option D is wrong because a flat table with appended customer region would duplicate data.

Answer

Use Power Query to add a calculated column for sales amount and shipping cost per order, then import.

Answer

Create a single table by appending the customer region to each row in the CSV using Power Query, then import.

Answer

Use DirectQuery on the CSV file to avoid storing data in Power BI.

Question 14

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.. Option A is correct because importing both tables and creating a relationship allows for easy analysis. Option B is wrong because DirectQuery on Access is not recommended due to performance. Option C is wrong because a single table duplicates customer information. Option D is wrong because an external database is unnecessary.

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 15

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.. Option C is correct because using DirectQuery (or dual storage) with a date table allows real-time data access and supports time intelligence. Option A is wrong because DirectQuery on a single flat table loses relationships. Option B is wrong because importing into a star schema does not support real-time. Option D is wrong because composite model is not needed for this scenario.

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.