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

Question 1

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?

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

Accepted Answer

AVG Delivery Time = CALCULATE(AVERAGE(Shipments[DeliveryDate] - Shipments[ShipDate]), FILTER(Cities, Cities[Population] > 1000000)). Option B is correct because CALCULATE modifies the filter context of the AVERAGE aggregation using FILTER(Cities, Cities[Population] > 1000000), and since Cities is related to Shipments via the OriginCity relationship, this filter propagates to Shipments and restricts the average to qualifying shipments. It is efficient because it leverages the existing relationship and filter propagation rather than iterating row by row. Option A is wrong because it uses SUMX and COUNTROWS with FILTER over Shipments and RELATED, which is more verbose and less efficient, and RELATED requires the row context to be on the many side with the correct relationship direction. Option C is invalid because DISTINCT(Cities[Population] > 1000000) does not produce a valid table filter for CALCULATE. Option D is wrong because AVERAGEX over all Shipments ignores the population criterion entirely, so it returns the average delivery time for every shipment.

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 2

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 3

You are a Power BI data analyst for a logistics company. The semantic model contains a fact table named Shipments and a dimension table named Routes. Users report that a matrix visual showing total freight cost by RouteName returns a blank for routes that have no shipments yet, and they want those routes to display 0 instead. You must fix this without changing any source data. What should you do?

Accepted Answer

Create a measure using COALESCE to replace BLANK() with 0, and use that measure in the matrix visual.. When a dimension member has no matching fact rows, aggregations over the fact table return BLANK at query time, not zero. Wrapping the aggregation in COALESCE forces a numeric zero while leaving source data and relationships untouched. Visual-level settings and relationship direction changes affect rendering or filter flow, not the evaluated value, so they cannot satisfy the requirement.

Answer

Add a calculated column to the Routes table that stores 0 for every row and sum it in the matrix visual.

Answer

Change the relationship between Shipments and Routes to a bidirectional cross-filter and refresh the dataset.

Answer

Set the RouteName column's 'Show items with no data' property to On in the matrix visual's field well.

Question 4

You are modeling a Power BI semantic model for a retail company. The model contains a Sales fact table and a Date dimension table. Users need to analyze sales by fiscal year, which starts on July 1 and ends on June 30. They also need to see the fiscal year label as 'FY2023' for the period July 1, 2022 to June 30, 2023. You must ensure that time intelligence functions like TOTALYTD work correctly with the fiscal calendar. What should you do?

Accepted Answer

In the Date table, add a calculated column for the fiscal year label and use the 'Sort by Column' feature to sort the fiscal year by a fiscal period column. Then use DAX measures that reference the fiscal year.. To support fiscal year analysis, you need to create calculated columns that define the fiscal year and fiscal period, and ensure proper sorting. Time intelligence functions can then be adapted using DAX patterns that specify the fiscal year end. Marking as a date table is still required, but it does not automatically handle fiscal calendars.

Answer

Create a calculated column in the Date table that returns the fiscal year label, and then use the 'Mark as Date Table' feature on the Date table.

Answer

Use the 'Mark as Date Table' feature on the Date table and specify the fiscal year end date in the table properties.

Answer

Create a calculated column in the Date table that returns the fiscal year label and another column for the fiscal year start date, then use the 'Mark as Date Table' feature.

Question 5

You are modeling data for a retail chain in Power BI Desktop. The Sales fact table contains a SalesAmount column and a ProductKey column. You need to create a measure that returns the total sales amount only for products in the 'Electronics' category. The Product table has Category and ProductKey columns, and there is a relationship between Sales and Product. Which DAX expression should you use?

Accepted Answer

SUMX(FILTER(Sales, RELATED(Product[Category]) = "Electronics"), Sales[SalesAmount]). The correct measure must filter the Sales table based on the related Product category. Using SUMX with FILTER and RELATED allows row-by-row evaluation, ensuring that only sales for Electronics are summed. This pattern is efficient and works regardless of the report's filter context, making it suitable for a reusable measure.

Answer

SUM(Sales[SalesAmount]) * (Product[Category] = "Electronics")

Answer

CALCULATE(SUM(Sales[SalesAmount]), Product[Category] = "Electronics")

Answer

CALCULATE(SUM(Sales[SalesAmount]), FILTER(Product, Product[Category] = "Electronics"))

Question 6

You are modeling a Power BI semantic model for a financial services company. The model contains a fact table 'Transactions' with columns: TransactionID, AccountID, TransactionDate, Amount, and TransactionType. You need to create a measure that calculates the running total of Amount for each account over time, resetting at the beginning of each year. Which DAX pattern should you use?

Accepted Answer

CALCULATE(SUM(Transactions[Amount]), FILTER(ALL(Transactions[TransactionDate]), Transactions[TransactionDate] <= MAX(Transactions[TransactionDate]) && YEAR(Transactions[TransactionDate]) = YEAR(MAX(Transactions[TransactionDate]))), VALUES(Transactions[AccountID])). The correct pattern uses FILTER over ALL on the date column to remove existing date filters, then applies conditions to include only dates up to the current date and within the same year, ensuring an annual reset. The VALUES function on AccountID ensures the calculation is grouped by account.

Answer

CALCULATE(SUM(Transactions[Amount]), FILTER(ALL(Transactions), Transactions[TransactionDate] <= MAX(Transactions[TransactionDate]) && Transactions[AccountID] = MAX(Transactions[AccountID])))

Answer

CALCULATE(SUM(Transactions[Amount]), DATESYTD(Transactions[TransactionDate]), VALUES(Transactions[AccountID]))

Answer

CALCULATE(SUM(Transactions[Amount]), FILTER(ALLSELECTED(Transactions[TransactionDate]), Transactions[TransactionDate] <= MAX(Transactions[TransactionDate])), VALUES(Transactions[AccountID]))

Question 7

You are building a Power BI semantic model for a hotel chain. The Reservations fact table stores a numeric RoomRate and a foreign key to a Rooms dimension. Analysts frequently slice total revenue by room type and by city. To reduce model size and improve compression without changing the reporting experience, what should you do?

Accepted Answer

Remove unused columns from the Rooms dimension so only the keys, room type, and city remain.. VertiPaq compression benefits from narrow tables, so dropping columns that reports do not use reduces the number of stored dictionaries and value ranges, shrinking the model. Changing data types to text, suppressing summarization, or switching to DirectQuery either increases size, has no storage effect, or changes performance characteristics instead of improving compression.

Answer

Change the RoomRate column's data type to Text to reduce storage.

Answer

Convert the Reservations fact table to a DirectQuery storage mode.

Answer

Set the RoomRate column's Summarize By property to None.

Question 8

You are modeling data for a hospital in Power BI. The model includes a Patient table and a Visit fact table. You need to create a measure that calculates the average length of stay (ALOS) for patients, but only for visits that occurred in the current year. The Visit table has AdmissionDate, DischargeDate, and PatientID. The Patient table has PatientID and Age. You want to ensure the measure respects the filter context of the report. Which two DAX expressions should you use together to achieve this? (Choose two.)

Accepted Answer

CALCULATE(AVERAGEX(Visit, DATEDIFF(Visit[AdmissionDate], Visit[DischargeDate], DAY)), FILTER(Visit, YEAR(Visit[AdmissionDate]) = YEAR(TODAY()))). The goal is to calculate the average length of stay for visits in the current year. Both correct expressions use AVERAGEX with DATEDIFF to compute the length of stay per visit and then average them. One uses CALCULATE with FILTER to apply the year filter, while the other uses AVERAGEX directly on a filtered table. Both approaches are valid and respect the filter context.

Answer

CALCULATE(AVERAGE(Visit[LengthOfStay]), YEAR(Visit[AdmissionDate]) = YEAR(TODAY()))

Answer

CALCULATE(AVERAGEX(Visit, DATEDIFF(Visit[AdmissionDate], Visit[DischargeDate], DAY)), YEAR(Visit[AdmissionDate]) = YEAR(TODAY()))

Answer

CALCULATE(AVERAGE(Visit[LengthOfStay]), Visit[AdmissionDate] >= DATE(YEAR(TODAY()), 1, 1))

Question 9

You are the data analyst for a healthcare provider. A semantic model contains a Patients dimension and an Encounters fact table. You need to add a calculated column to Patients that classifies each patient as 'High', 'Medium', or 'Low' risk based on the count of that patient's encounters, and the classification must refresh with the data. Which DAX approach should you use?

Accepted Answer

A calculated column that uses CALCULATE with COUNTROWS over Encounters and a SWITCH expression to assign the risk band.. Calculated columns evaluate in row context, which does not automatically propagate to related tables. Wrapping COUNTROWS over the related Encounters table in CALCULATE converts the row context into filter context, yielding the per-patient encounter count that SWITCH can map to a risk band. Measures are not stored per row, and approaches relying on RELATEDTABLE or missing source columns fail to derive the count correctly.

Answer

A calculated column that uses SUM over a pre-aggregated encounter count column stored in the Patients table.

Answer

A measure that uses COUNTROWS over Encounters and a SWITCH expression, placed in a table visual next to patient names.

Answer

A calculated column that uses COUNTROWS with RELATEDTABLE over Encounters, wrapped in a SWITCH expression to assign the risk band.

Question 10

You are designing a Power BI semantic model for a healthcare provider. The model includes a Patient table, a Visit table, and a Diagnosis table. You need to implement row-level security (RLS) so that doctors can only see data for their own patients, and nurses can see data for all patients in their department. Which two actions should you perform? (Choose two.)

Accepted Answer

Create roles in the Power BI Desktop model and define DAX filter expressions on the Patient table.. To implement RLS, you create roles and define DAX filter expressions that use functions like USERPRINCIPALNAME() to dynamically filter the Patient table based on the user's identity. This allows doctors to see only their patients and nurses to see patients in their department, without duplicating models or adding unnecessary filters.

Answer

Create a separate semantic model for each department and assign permissions accordingly.

Answer

Implement RLS on the Visit and Diagnosis tables as well, using the same filter expressions as on the Patient table.

Answer

Use the LOOKUPVALUE function in a calculated column to determine the user's role and then filter based on that column.

Question 11

You are designing a Power BI semantic model for a logistics company. The model includes a Shipments fact table with columns ShipmentID, OriginCity, DestinationCity, and Cost. You need to create a measure that calculates the total cost for shipments where the origin and destination are in the same city. Which DAX expression should you use?

Accepted Answer

SUMX(FILTER(Shipments, Shipments[OriginCity] = Shipments[DestinationCity]), Shipments[Cost]). To calculate total cost for shipments where origin and destination cities match, you need to filter the Shipments table row by row and sum the Cost. SUMX with FILTER accomplishes this by iterating over each row, evaluating the condition, and summing only the qualifying rows. This approach is efficient and correct.

Answer

CALCULATE(SUM(Shipments[Cost]), FILTER(Shipments, Shipments[OriginCity] = Shipments[DestinationCity]))

Answer

SUM(Shipments[Cost]) * (Shipments[OriginCity] = Shipments[DestinationCity])

Answer

CALCULATE(SUM(Shipments[Cost]), Shipments[OriginCity] = Shipments[DestinationCity])

Question 12

You are modeling a Power BI semantic model for a logistics company. The model contains a Shipments fact table and a Date dimension table. You need to create a measure that calculates the total weight of shipments for the last 30 days relative to the maximum date in the Date table. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Shipments[Weight]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -30, DAY)). The DATESINPERIOD function is designed to return a table of dates for a specified interval, such as the last 30 days, relative to a given end date. Using it within CALCULATE correctly filters the Shipments table to sum the weight for that period.

Answer

CALCULATE(SUM(Shipments[Weight]), LASTDATE('Date'[Date]) - 30)

Answer

CALCULATE(SUM(Shipments[Weight]), DATESBETWEEN('Date'[Date], MAX('Date'[Date]) - 30, MAX('Date'[Date])))

Answer

CALCULATE(SUM(Shipments[Weight]), FILTER(ALL('Date'), 'Date'[Date] >= MAX('Date'[Date]) - 30))

Question 13

You are a data analyst for an online retailer. You have a Power BI semantic model with a fact table named 'InternetSales' that contains a column 'SalesAmount'. You need to create a measure that calculates the total sales for the previous year, but only for product categories that are currently active. The model includes a 'Product' dimension table with a column 'IsActive' (Boolean) and a 'Date' table. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(InternetSales[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]), FILTER(Product, Product[IsActive] = TRUE())). The correct measure must calculate previous-year sales while filtering to active products. Using CALCULATE with SAMEPERIODLASTYEAR shifts the date context, and FILTER over the Product table applies the IsActive condition without removing other relevant filters. This combination ensures accurate results for the specified scenario.

Answer

CALCULATE(SUM(InternetSales[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]), Product[IsActive] = TRUE())

Answer

CALCULATE(SUM(InternetSales[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]), KEEPFILTERS(Product[IsActive] = TRUE()))

Answer

CALCULATE(SUM(InternetSales[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]), FILTER(ALL(Product), Product[IsActive] = TRUE()))

Question 14

You are a data analyst for a healthcare provider. Your Power BI semantic model contains a fact table named Visits with columns VisitID, PatientID, ProviderID, VisitDate, and ChargeAmount. You also have a dimension table named Providers with columns ProviderID, ProviderName, Specialty, and Region. Users report that when they filter a report by Specialty, the total ChargeAmount does not change, even though the visual shows the correct number of visits. You need to ensure that filtering Providers by Specialty correctly filters the Visits fact table. What should you do?

Accepted Answer

Create an active relationship from Providers[ProviderID] to Visits[ProviderID].. The fact table and dimension table must be connected by a relationship for filters to propagate. Creating an active relationship on ProviderID allows the Specialty filter to flow from Providers to Visits and affect ChargeAmount. The other options either do not establish the needed filter path or address unrelated features.

Answer

Create a calculated column in Visits that uses RELATED to retrieve Specialty from Providers.

Answer

Mark the Providers table as a date table.

Answer

Set the cross-filter direction on the existing relationship between Visits and Providers to Both.

Question 15

You are a data analyst for a retail company. You have a Power BI semantic model that contains a table named Sales with columns OrderDate, ProductID, and SalesAmount. You need to create a measure that calculates the total sales amount for the current year. Which DAX expression should you use?

Accepted Answer

Total Sales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales[OrderDate]), YEAR(Sales[OrderDate]) = YEAR(TODAY()))). The measure must filter the Sales table to only include rows where the order date falls in the current year. Using CALCULATE with FILTER over ALL(Sales[OrderDate]) and comparing YEAR(Sales[OrderDate]) to YEAR(TODAY()) achieves this. The other options either do not filter by year, use invalid syntax, or reference a non-existent hierarchy.

Answer

Total Sales = CALCULATE(SUM(Sales[SalesAmount]), YEAR(Sales[OrderDate]) = YEAR(TODAY()))

Answer

Total Sales = SUM(Sales[SalesAmount])

Answer

Total Sales = CALCULATE(SUM(Sales[SalesAmount]), Sales[OrderDate].[Year] = YEAR(TODAY()))