PL-300 Visualize and analyze the data Practice Test 15 — 15 Questions

Question 1

You run the above DAX query in DAX Studio. The result shows profit margin values for each year, but all values are the same for every year despite sales amounts varying. What is the most likely cause?

Accepted Answer

The measures use row context instead of filter context, causing them to evaluate over the entire table.. The most likely cause is that the measures [Total Sales] and [Total Cost] are written using SUMX without a CALCULATE wrapper. When used inside SUMMARIZECOLUMNS, these measures are evaluated in row context for the grouping columns, but the SUMX iterator scans the entire Sales table and does not propagate the filter context from the year column. This results in the same total for each year. To fix this, the measures should either use CALCULATE with SUM, or the SUMX should be nested inside CALCULATE to apply the filter context. Option A is incorrect because there is no mention of a calculated column. Option B is incorrect because division by zero would produce an error or blank, not the same value. Option C is incorrect because SUMMARIZECOLUMNS works fine with SUMX; the issue is filter context propagation.

Answer

The Sales table contains a calculated column that overrides the year filter.

Answer

The DIVIDE function is not handling division by zero correctly.

Answer

The SUMMARIZECOLUMNS function is not compatible with measures that use SUMX.

Question 2

You are optimizing a DAX query that returns product-country combinations with total sales over 10,000. The query runs slowly. What is the primary performance issue with this query?

Accepted Answer

Using SUMMARIZE instead of SUMMARIZECOLUMNS causes inefficient grouping and measure evaluation.. The primary performance issue is using SUMMARIZE instead of SUMMARIZECOLUMNS. SUMMARIZE performs a table scan and then evaluates measures in a row context, which is slower. SUMMARIZECOLUMNS is optimized for grouping and measure evaluation, making it more efficient. Option A is incorrect because SUMX is not necessarily slow; it depends on the measure logic. Option C is incorrect because FILTER is not the main issue; the grouping method is. Option D is incorrect because relationships are not indicated as problematic.

Answer

The measure [Total Sales] is defined using SUMX which is slow.

Answer

The FILTER function should be replaced with CALCULATETABLE for better performance.

Answer

The query lacks proper relationships between tables, causing cross joins.

Question 3

You are designing a Power BI report for a sales team. The team needs to see the top 10 products by sales amount, but also want to see the sales amount for all other products aggregated as 'Others'. What is the best approach to create this visual?

Accepted Answer

Use a Top N filter on the visual to show top 10 and enable the 'Others' option in the filter pane.. The best approach in Power BI is to use a Top N filter on the visual and enable the 'Others' option. This will display the top 10 products individually and aggregate all remaining products into a single 'Others' bucket. This method is dynamic, updates with slicers and filters, and is a built-in feature. A calculated column (option D) is static and does not recalculate based on report interactions, making it unsuitable for an interactive sales report.

Answer

Create a calculated table that holds the top 10 products and join it to the main table.

Answer

Use the RANKX function in a measure and filter the visual to show rank <= 10.

Answer

Create a calculated column that categorizes products as 'Top 10' or 'Others' based on rank, then use that column in the visual.

Question 4

You are a Power BI developer for a retail company. You have a semantic model that includes a 'Sales' fact table with columns: 'Date', 'ProductID', 'StoreID', 'Quantity', 'UnitPrice'. The 'Product' dimension table includes 'ProductID', 'ProductName', 'Category', 'SubCategory'. The 'Store' dimension table includes 'StoreID', 'StoreName', 'Region', 'District'. You need to create a report page that allows users to analyze sales performance by product category and store region. The report must include a matrix visual with: - Rows: Product Category - Columns: Store Region - Values: Total Sales Amount (Quantity * UnitPrice) Additionally, users must be able to drill down from category to subcategory in the rows, and from region to district in the columns. You also need to ensure that when a user selects a specific store region, the matrix only shows data for that region and its districts. You have created the measures and the matrix visual. However, when you test the drill down, the hierarchy does not work as expected: clicking the expand icon on a category does not show subcategories. What is the most likely cause?

Accepted Answer

The matrix rows do not have a hierarchy defined; Category and SubCategory are separate fields.. For drill down to work in a matrix, the fields must be in a hierarchy. The correct approach is to use a hierarchy in the rows, where Category is the parent and SubCategory is the child. If Category and SubCategory are added as separate fields (not as a hierarchy), the expand icon will not drill down, which matches option C. Option A is incorrect because the measure definition does not affect the hierarchical drill down behavior. Option B is incorrect because a slicer for Store Region would not prevent drill down within the matrix rows. Option D is incorrect because the relationship direction does not affect drill down in a matrix; it affects cross-filtering between visuals.

Answer

The Total Sales measure is incorrectly defined, causing blank values for subcategories.

Answer

A slicer for Store Region is interfering with the matrix drill down behavior.

Answer

The relationship between Sales and Product is set to single direction, preventing drill through.

Question 5

You are working on a Power BI report for a marketing team. The report includes a page with a line chart showing website visits over time, and a table showing the top 5 marketing campaigns by conversion rate. The marketing manager wants to be able to click on a point in the line chart (representing a specific date) and have the table update to show only campaigns that were active on that date. The campaigns data includes a 'StartDate' and 'EndDate'. You have implemented a measure to filter campaigns based on the selected date. However, when you click on a date point in the line chart, the table does not update. What should you do to enable this interaction?

Accepted Answer

In the 'Edit interactions' settings, ensure that the line chart cross-filters the table.. The issue is that the line chart and table are not interacting as expected. By default, visuals in Power BI cross-filter each other, but this interaction can be disabled. To enable it, you need to edit the interactions so that the line chart cross-filters the table. Option D correctly describes this: in 'Edit interactions', ensure cross-filtering is enabled. Option A is incorrect because disabling cross-filtering would prevent the desired interaction. Option B is incorrect because drillthrough navigates to a different page, not filter within the same page. Option C is incorrect because bookmarks are not designed for dynamic filtering based on user clicks.

Answer

In the 'Edit interactions' settings, disable cross-filtering between the line chart and table.

Answer

Create a drillthrough page and configure the line chart to drill through to the table page.

Answer

Add a bookmark for each date and set the line chart to trigger the bookmark on click.

Question 6

You are a Power BI analyst for a financial services company. You have a semantic model that contains a 'Transactions' fact table with millions of rows, and a 'Calendar' date dimension table. You need to create a report page that shows the year-to-date (YTD) total transaction amount compared to the same period last year (SPLY). The report must allow users to select a year from a slicer, and the YTD calculation should always be based on the maximum date in the data for the selected year (i.e., show YTD as of the latest available date). You have created the following measures:

- Total Amount = SUM(Transactions[Amount])
- YTD Amount = TOTALYTD([Total Amount], 'Calendar'[Date])
- SPLY YTD = CALCULATE([YTD Amount], SAMEPERIODLASTYEAR('Calendar'[Date]))

When users select a year from the slicer, the YTD Amount does not correctly show the YTD as of the last date in the selected year; instead, it shows the YTD for each individual date in the context. What is the most likely issue?

Accepted Answer

The YTD measure should be wrapped in a CALCULATE that filters to the last date in the selected period.. The TOTALYTD function calculates the year-to-date value for each date in the current filter context. If the visual includes a date column (e.g., from the Calendar table), it will show a YTD value for every date, not a single YTD as of the last date. To show YTD as of the latest available date in the selected year, you need to wrap the YTD measure in a CALCULATE that fixes the filter context to the last date. For example: YTD as of Last Date = CALCULATE([YTD Amount], LASTDATE('Calendar'[Date])). Option C correctly identifies this. Option A is incorrect because the relationship is active. Option B is incorrect because the slicer filtering is likely working. Option D is incorrect because TOTALYTD is the appropriate function; DATESYTD would also require similar handling.

Answer

The relationship between Transactions and Calendar is not active.

Answer

The slicer is not configured to filter the Calendar table.

Answer

The TOTALYTD function is not appropriate; you should use DATESYTD instead.

Question 7

You are a Power BI consultant for a logistics company. The company has a semantic model with a 'Shipments' fact table (containing 'ShipmentID', 'Date', 'OriginCity', 'DestinationCity', 'Weight', 'Cost') and 'City' dimension table (with 'City', 'Region', 'Country'). The company wants a report that displays a map visual with bubbles sized by total shipment weight, and a slicer to select a region. Additionally, they want to be able to click on a bubble (city) and see a table of shipments from that city. You have implemented a map visual using the 'City' field and a table visual. When you click on a bubble, the table does not filter to show only shipments from that city. You have confirmed that cross-filtering is enabled. What is the most likely cause?

Accepted Answer

The relationship between Shipments and City is based on OriginCity, but the table shows DestinationCity, so the filter does not apply.. The map visual uses the 'City' field from the City dimension table. Clicking a bubble sends a filter on the City table. However, the table visual likely displays shipments with 'DestinationCity' from the Shipments table. The relationship between Shipments and City is based on 'OriginCity', not 'DestinationCity'. Therefore, the filter from the map (on City) does not propagate to the table because the relationship is on a different column. Option C is correct. Option A is incorrect because a legend is not required for cross-filtering to work. Option B is incorrect; while a filter could prevent cross-filtering, the question states cross-filtering is enabled, so that is not the likely cause. Option D is incorrect; the slicer is not directly interfering; cross-filtering interactions can be managed separately.

Answer

The map visual does not have a legend field, so clicking does not generate a filter.

Answer

The table visual has a filter that prevents cross-filtering.

Answer

The region slicer is interfering with the cross-filtering.

Question 8

You are a Power BI developer for a healthcare organization. You have a semantic model with a 'PatientVisits' fact table (columns: 'VisitID', 'PatientID', 'Date', 'Department', 'DurationMinutes', 'Cost') and a 'Patients' dimension table (columns: 'PatientID', 'Age', 'Gender', 'City'). You need to create a report page that shows the average cost per visit by department and age group. The age groups are: 0-18, 19-35, 36-50, 51-65, 65+. You want to use a custom visual that displays a matrix with departments as rows and age groups as columns, and the value as average cost. You have created a calculated column for age groups in the Patients table. However, when you place the age group column in the matrix columns, the values are not aggregated correctly; instead, each age group shows the same average cost as the overall average. What is the most likely cause?

Accepted Answer

The relationship between PatientVisits and Patients is many-to-many, causing ambiguous filter propagation.. The issue arises because the relationship between the PatientVisits fact table and the Patients dimension table is many-to-many. In a many-to-many relationship, filter propagation can be ambiguous, causing filters applied from the dimension table (e.g., age group) to not properly restrict the fact table rows. As a result, the average cost measure calculates the overall average regardless of the age group filter, leading to the same value across all groups. Option A is incorrect because using SUM instead of AVERAGE would produce higher values, not identical ones. Option B is incorrect because placing the age group in rows would still cause the same filter propagation issue. Option D is incorrect because the calculated column is part of the dimension table and does participate in the relationship; the problem is the relationship type.

Answer

The measure used is SUM of Cost instead of AVERAGE.

Answer

The age group column is not added to the matrix column field well; it should be in rows.

Answer

The age group calculated column is not part of the relationship chain; it is not being used as a filter.

Question 9

You are a Power BI report creator for a non-profit organization. You have a semantic model with a 'Donations' fact table (columns: 'DonationID', 'DonorID', 'Date', 'Amount', 'CampaignID') and a 'Donors' dimension table (columns: 'DonorID', 'DonorName', 'City', 'DonorType' (Individual/Corporate)). You need to create a report page that shows a scatter chart with 'Total Donation Amount' on the X-axis and 'Number of Donations' on the Y-axis, with each point representing a donor type. You also want to add a trend line to show the correlation. When you create the scatter chart, only one point appears (for all donors combined), instead of separate points for Individual and Corporate. What is the most likely cause?

Accepted Answer

The DonorType field is placed in the 'Values' well instead of the 'Legend' well.. In a scatter chart, to display separate points for each category (e.g., Individual and Corporate), the category field must be placed in the 'Legend' well. Placing it in the 'Values' well causes the chart to aggregate all donations into a single point, as it treats the category as a value to be aggregated. Therefore, the correct answer is B. Option A is incorrect because an inactive relationship would not prevent separate points; it would affect filtering but not the number of points. Option C is incorrect because the measures (SUM of Amount for X, COUNT of DonationID for Y) are appropriate. Option D is incorrect because scatter charts do support multiple categories via the Legend well; small multiples are not required.

Answer

The relationship between Donations and Donors is inactive.

Answer

The measures are incorrectly defined; they should use SUM and COUNT respectively.

Answer

The scatter chart does not support multiple categories; you need to use a small multiples chart.

Question 10

A company wants to create a Power BI report that shows sales performance by region. The data contains a table 'Sales' with columns: Date, Amount, RegionID, and ProductID. They also have a 'Regions' table with RegionID and RegionName. They want to display a matrix visual with RegionName on rows and Year on columns, with the sum of Amount as values. However, the report displays only 'RegionID' instead of 'RegionName'. What is the most likely cause?

Accepted Answer

There is no active relationship between the Sales and Regions tables.. If there is no active relationship between the Sales and Regions tables, Power BI cannot use the RegionName from the Regions table to filter or group the Sales data. Instead, it defaults to displaying the RegionID from the Sales table, which is the only related field available in the visual. An active relationship must exist between the two tables on the RegionID columns for RegionName to appear in the matrix.

Answer

The relationship is configured as many-to-many.

Answer

The relationship direction is set to Both.

Answer

The RegionID column in the Sales table is hidden.

Question 11

A Power BI report includes a bar chart showing total sales by product category. The report designer wants to add a trend line to the chart to show the overall sales trend over time. Which type of visual should be used instead?

Accepted Answer

Line chart. A line chart is the correct visual to show a trend over time because it plots data points connected by straight lines, making it easy to see the overall direction and pattern of total sales across a continuous time axis. Bar charts, including stacked variants, are designed for comparing discrete categories, not for displaying continuous trends.

Answer

Stacked bar chart

Answer

Scatter chart

Answer

Pie chart

Question 12

A Power BI report contains a table visual that displays employee names and their total sales. The data model includes an Employee table with columns: EmployeeID, Name, Department, and HireDate. The Sales table has columns: SaleID, EmployeeID, Amount, and SaleDate. The relationship between Employee and Sales is one-to-many. The user wants to see only employees who have made at least one sale. However, the table shows all employees, including those with no sales (blank Amount). What is the most likely reason?

Accepted Answer

There is no visual-level filter to exclude blank values.. The table visual is showing all employees due to the absence of a visual-level filter to exclude blank or zero sales amounts. In Power BI, a one-to-many relationship between Employee and Sales means that employees without sales will still appear in the visual unless explicitly filtered out, as the relationship does not automatically suppress rows from the 'one' side when there are no matching rows on the 'many' side.

Answer

The EmployeeID column in the Employee table is hidden.

Answer

The relationship is many-to-one, not one-to-many.

Answer

The relationship direction is set to Single from Employee to Sales.

Question 13

A data analyst creates a Power BI report that uses a date table with a continuous date range. They want to calculate the running total of sales over the last 12 months, ending on the last date in the current filter context. Which DAX expression should they use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)). DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH) returns a contiguous set of dates from 12 months before the last date in the current filter context up to that last date, providing an exact 12-month window. This function handles month boundaries correctly and is the standard way to calculate rolling 12-month totals in DAX. Option A uses 365 days, which can be imprecise due to leap years.

Answer

CALCULATE(SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], MAX('Date'[Date]) - 365, MAX('Date'[Date])))

Answer

TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

Answer

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

Question 14

You need to create a measure that calculates the year-over-year growth percentage for sales. Which DAX function should you use?

Accepted Answer

SAMEPERIODLASTYEAR. The SAMEPERIODLASTYEAR function is the correct choice for calculating year-over-year growth because it shifts the current filter context back by one year, returning a set of dates exactly one year prior. This allows you to compute the prior year's sales and then derive the growth percentage using a formula like (Current Sales - Prior Year Sales) / Prior Year Sales.

Answer

DATEADD

Answer

PARALLELPERIOD

Answer

PREVIOUSYEAR

Question 15

Which are valid ways to create a calculated table in Power BI? (Select all that apply)

Accepted Answer

VALUES(Customer[Country]). To create a calculated table, you need a DAX expression that returns a table object. Options A, C, D, and E all return tables: VALUES returns a single-column table of distinct values; FILTER returns a filtered subset of rows; CALENDARAUTO returns a date range table; SUMMARIZE returns a grouped table with aggregations. Option B, CALCULATE, returns a scalar value, not a table, so it is invalid.

Answer

CALCULATE(SUM(Sales[Amount]), ALL(Sales))