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

Question 1

Which THREE considerations are important when designing a Power BI data model for large datasets?

Accepted Answer

Disable the auto-date/time feature.. Disabling the auto-date/time feature prevents Power BI from automatically creating hidden date tables for each date column, which can significantly increase the model size and reduce performance in large datasets. This is a best practice for optimizing storage and refresh times when working with large data volumes.

Answer

Store calculated columns in the fact table for quick access.

Answer

Include as many columns as possible in fact tables for flexibility.

Question 2

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 3

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

Accepted Answer

Decimal Number. Power Query supports a specific set of primitive data types for columns in Power BI, including Decimal Number, Text, and Boolean. These types are used to define the structure and behavior of data during transformation and loading, ensuring compatibility with the Power BI engine.

Answer

Array

Answer

Object

Question 4

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 5

You are a data analyst for a retail company. You have a Power BI semantic model that includes a fact table named Sales with columns: Date, ProductID, StoreID, Quantity, and Amount. You also have dimension tables: Product, Store, and Date. The Date table is marked as a date table. You need to create a measure that calculates the running total of sales amount over the last 12 months, including the current month. The measure should be dynamic based on the filter context. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH)). Ly uses DATESINPERIOD to define a dynamic period of the last 12 months from the latest date in the current filter context, creating a rolling 12-month total. Option A uses DATESBETWEEN with a fixed start date, which is not dynamic. Option B uses PARALLELPERIOD, which shifts the entire period by 12 months, not a rolling total. Option D uses TOTALYTD for year-to-date, not a rolling 12-month period.

Answer

CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Date[Date], DATE(2024,1,1), MAX(Date[Date])))

Answer

CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD(Date[Date], -12, MONTH))

Answer

TOTALYTD(SUM(Sales[Amount]), Date[Date])

Question 6

You are building a Power BI report for a multinational corporation. The data model includes a fact table named Orders with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, UnitPrice, and Discount. The Customer dimension contains columns: CustomerID, CustomerName, Country, and Segment. The Product dimension contains: ProductID, ProductName, Category, Subcategory, and Price. You need to create a calculated column in the Orders table that calculates the net amount after discount for each order line (Quantity * UnitPrice * (1 - Discount)). You also need to ensure that the column is stored in the model for high-performance filtering. Which DAX expression should you use?

Accepted Answer

NetAmount = Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[Discount]). It uses simple row-level arithmetic to create a calculated column that is materialized and stored in the model, supporting high-performance filtering. Option A uses SUMX, which is an iterator function designed for measures and would not create a stored column. Option C uses CALCULATE with SUM aggregates, producing a scalar value inappropriate for a calculated column. Option D incorrectly subtracts Discount from the product rather than applying (1 - Discount).

Answer

NetAmount = SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[Discount]))

Answer

NetAmount = CALCULATE(SUM(Orders[Quantity]) * SUM(Orders[UnitPrice]) * (1 - SUM(Orders[Discount])))

Answer

NetAmount = Orders[Quantity] * Orders[UnitPrice] - Orders[Discount]

Question 7

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.. Using DAX SUMMARIZE to create a calculated table that groups by DiagnosisCode and takes the first description resolves duplicates without removing rows from the original table, allowing a one-to-many relationship. Option B is incorrect because removing duplicates in Power Query would permanently alter the Diagnosis table, potentially losing data. Option C is incorrect because a many-to-many relationship would allow multiple matches, but it would not resolve the duplicate issue and could cause ambiguity in aggregations. Option D is incorrect because creating a composite key in PatientVisits with VisitID and DiagnosisCode would not map to a unique key in Diagnosis, as Diagnosis does not contain VisitID; this would still result in a many-to-many relationship or no relationship.

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 8

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.. Creating a date dimension table in the SQL Server data warehouse and using it in the Power BI model for date filtering improves performance because it allows the database to use indexed date columns and reduce the number of rows scanned from the fact table. Option A is incorrect because switching to Import mode is not possible due to data volume constraints. Option C is incorrect because adding an index on the fact table's date column alone does not optimize date-range queries as effectively as using a separate date dimension. Option D is incorrect because calculated tables are not supported in DirectQuery mode.

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 9

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]))). ALLSELECTED(Products[Category]) removes only the filter on the Category column, returning the total sales for all selected categories, which is the correct denominator for the percentage. Option B is wrong because ALLSELECTED(Orders) removes all filters from the Orders table, including potential filters from other dimensions, and does not specifically isolate the category filter. Option C uses ALLEXCEPT(Products, Products[Category]), which preserves the category filter in the denominator, causing each category to show 100%. Option D uses ALL(Products), which removes all filters from the Products table, not just the category filter, so the denominator is the overall total sales regardless of context.

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

Question 10

You are a data analyst for a logistics company. You have a Power BI semantic model that contains a table named Shipments with columns: ShipmentID, OriginCity, DestinationCity, ShipDate, DeliveryDate, Weight, and Cost. The model also contains a table named Cities with columns: City, State, Region, and Population. You need to create a measure that calculates the average delivery time in days for shipments that originated from cities with a population greater than 1 million. The measure should be efficient. Which DAX expression should you use?

Accepted Answer

AVG Delivery Time = CALCULATE(AVERAGE(Shipments[DeliveryDate] - Shipments[ShipDate]), FILTER(Cities, Cities[Population] > 1000000)). This measure uses CALCULATE to modify the filter context. It first filters the Cities table to only include cities with a population greater than 1 million. Then, through the relationship between Shipments and Cities (based on OriginCity matching City), it calculates the average of (DeliveryDate - ShipDate) for only those shipments originating from the filtered cities. This approach is efficient because it filters the dimension table (Cities) rather than iterating over the fact table.

Option A is incorrect because it uses SUMX to iterate over each shipment, checking the RELATED(Cities[Population]) condition for every row, which is less efficient. It then divides the sum of delivery days by the count of qualifying shipments, which is essentially a manual average, but it is unnecessarily complex and slower due to the row-by-row evaluation.

Option C is incorrect because DISTINCT returns a one-column table of distinct values from the specified column, not a logical filter. Using DISTINCT incorrectly distills the condition to a table of TRUE/FALSE values, which does not work as a filter argument.

Option D is incorrect because it calculates the average delivery time across all shipments without applying the population filter, so it does not meet the requirement.

Answer

AVG Delivery Time = SUMX(FILTER(Shipments, RELATED(Cities[Population]) > 1000000), Shipments[DeliveryDate] - Shipments[ShipDate]) / COUNTROWS(FILTER(Shipments, RELATED(Cities[Population]) > 1000000))

Answer

AVG Delivery Time = CALCULATE(AVERAGE(Shipments[DeliveryDate] - Shipments[ShipDate]), DISTINCT(Cities[Population] > 1000000))

Answer

AVG Delivery Time = AVERAGEX(Shipments, Shipments[DeliveryDate] - Shipments[ShipDate])

Question 11

You are a Power BI report creator for a university. You have a semantic model with a table named Enrollments with columns: EnrollmentID, StudentID, CourseID, EnrollmentDate, and Grade. You also have tables: Students (StudentID, StudentName, Major) and Courses (CourseID, CourseName, Department). You need to create a measure that counts the number of students who have enrolled in at least one course in the 'Science' department. The measure should be efficient and respect any filters on the report. Which DAX expression should you use?

Accepted Answer

Students in Science = CALCULATE(DISTINCTCOUNT(Enrollments[StudentID]), FILTER(Courses, Courses[Department] = "Science")). CALCULATE(DISTINCTCOUNT(Enrollments[StudentID]), FILTER(Courses, Courses[Department] = "Science")) efficiently counts distinct students who have enrolled in at least one Science department course. The FILTER function directly filters the Courses table to Science courses, and CALCULATE applies that filter to the Enrollments table via the relationship, so DISTINCTCOUNT counts only students with enrollments in those courses. Option A uses FILTER(Enrollments, RELATED(Courses[Department]) = "Science"), which iterates over each enrollment row and evaluates the RELATED function row by row. This is less efficient and can lead to errors if the relationship is not correctly referenced or if multiple courses match. Option C counts all distinct students regardless of department. Option D counts the number of enrollment rows, not distinct students.

Answer

Students in Science = CALCULATE(DISTINCTCOUNT(Enrollments[StudentID]), FILTER(Enrollments, RELATED(Courses[Department]) = "Science"))

Answer

Students in Science = DISTINCTCOUNT(Enrollments[StudentID])

Answer

Students in Science = COUNTROWS(Enrollments)

Question 12

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

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

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

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