PL-300 Exam Prep Part 3 | DAX & Power Query Fundamentals (Q51-75)

Question 51:

Note: This question is part of a series of questions that present the same scenario.
You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as a numerical amount representing US dollars. You need to create a reference line to show which employees are above the median salary.
Solution: You create an average line by using the Salary measure.
Does this meet the goal?

• A. Yes
• B. No

Answer: B
The average (mean) is often different from the median, especially in skewed datasets like salaries. An average line does not correctly identify the median salary.

Question 52:

Note: This question is part of a series of questions that present the same scenario.
You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as a numerical amount representing US dollars. You need to create a reference line to show which employees are above the median salary.
Solution: You create a constant line and set the value to .5.
Does this meet the goal?

• A. Yes
• B. No

Answer: B
A constant line with a value of 0.5 does not represent the median salary value, which could be any numerical amount (e.g., $50,000). The correct approach is to use a median line or a percentile line set to 50%.

Question 53:

You have a Power BI model that contains a table named Sales. The Sales table contains multiple columns including Order ID. Orders are uniquely identified by using the order ID and can have multiple order lines. You need to write a DAX measure that counts the number of orders. Which formula should you use?

• A. Count(‘Sales'[Order ID])
• B. CountA(‘Sales'[Order ID])
• C. CountRows(‘Sales’)
• D. DistinctCount(‘Sales'[Order ID])

Answer: D
Because an order can have multiple order lines, the Order ID will appear multiple times in the Sales table. To count the number of unique orders, you must use DISTINCTCOUNT on the ‘Sales'[Order ID] column.

Question 54:

HOTSPOT – You are creating a Power BI model in Power BI Desktop. You need to create a calculated table named Numbers that will contain all the integers from -100 to 100. How should you complete the DAX calculation?

Numbers = [Function]([Start], [End], [Increment])

• Function: [Option]
• Start: [Option]
• End: [Option]
• Increment: [Option]

Answer:
– Function: GENERATESERIES
– Start: -100
– End: 100
– Increment: 1
The complete expression is Numbers = GENERATESERIES(-100, 100, 1).

Question 55:

In Power Query Editor, you have a query named Sales Data. You need to create two queries named Product Dimension and Sales Fact based on the Sales Data query. The solution must minimize maintenance effort and the size of the dataset. Which two actions should you perform?

• A. Reference the Sales Data query to create the new queries.
• B. Disable the load for the Sales Fact query.
• C. Duplicate the Sales Data query to create the new queries.
• D. Clear Include in report refresh for the Sales Data query.
• E. Disable the load for the Sales Data query.

Answer: A, E
A: Referencing creates new queries that are linked to the original, which minimizes maintenance as changes to the source steps in “Sales Data” can flow through. E: Disabling the load for the original “Sales Data” query prevents it from being loaded into the data model, which reduces the dataset size. Only the final dimension and fact tables should be loaded.

Question 56:

You have a Power BI model that contains a table named Date. You need to create a calculated table based on the Date table. The calculated table must contain only unique combinations of values for Calendar Year, Calendar Quarter, and Calendar Month. Which DAX function should you include in the table definition?

• A. ADDCOLUMNS
• B. CALCULATE
• C. SUMMARIZE
• D. DATATABLE

Answer: C
The SUMMARIZE function is used to create a summary table by grouping by one or more columns, which results in the unique combinations of those columns.

Question 57:

HOTSPOT – You have a Power BI model that contains data as shown. The Date table relates to the Sales table by using the Date columns. You need to create a calculated table that will contain:
– A row for each year
– A column that contains the total sales per year
How should you complete the DAX calculation?
SalesByYear = [Function1]([Table], [GroupByColumn], "[NewColumnName]", [Expression])

• Function1: [Option]
• GroupByColumn: [Option]
• Expression: [Option]

Answer:
– Function1: SUMMARIZE
– GroupByColumn: ‘Date'[Year]
– Expression: SUM(Sales[SalesAmount])

Question 58:

You use Power Query Editor to import and preview sales data from a Microsoft Excel file as shown in the exhibit, where years are in columns. You need to shape the query to display the following three columns: Month, Sales, Year. What should you select in Power Query Editor?

• A. Merge columns
• B. Transpose
• C. Unpivot columns
• D. Pivot column

Answer: C
The data is in a pivoted format with years as columns. To transform this into a tabular format with a single ‘Year’ column and a ‘Sales’ column, you need to select the year columns and use the “Unpivot Columns” transformation.

Question 59:

HOTSPOT – You are creating a Power BI model to analyze inventory. The correct inventory quantity for a given product in a month is the last recorded value for that month. You need to write a DAX measure that will show the correct inventory value when a user analyzes inventory by year, month, or date. How should you complete the measure?
Ending Inventory = CALCULATE( SUM(Inventory[Quantity]), [Function]('Date'[Date]) )

• Function: [Option]

Answer:
– Function: LASTDATE
This is a semi-additive measure. Using LASTDATE within CALCULATE filters the context to only the last date within the selected period (day, month, quarter, or year), giving you the closing inventory balance.

Question 60:

Note: This question is part of a series of questions that present the same scenario.
You have a Power BI report that imports a date table and a sales table. The sales table has three date foreign keys: Due Date, Order Date, Delivery Date. You need to support the analysis of sales over time based on all three dates at the same time.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables. You create active relationships between the sales table and each date table.
Does this meet the goal?

• A. Yes
• B. No

Answer: A
Yes. This solution, known as the “role-playing dimension” pattern, creates three independent copies of the date table. Each can have an active relationship to one of the date keys in the sales table, allowing for independent filtering. While it increases model size, it functionally meets the requirement.

Question 61:

HOTSPOT – You are creating a Power BI report that will show the number of current employees over time from a monthly snapshot table. You need to create a semi-additive DAX measure that will return the count of employees for the last available date in a year, quarter, or month. How should you complete the measure?
Employee Count = [Function1]( [Function2](Headcount[EmployeeID]), [Function3]('Date'[Date]) )

• Function1: [Option]
• Function2: [Option]
• Function3: [Option]

Answer:
– Function1: CALCULATE
– Function2: COUNT
– Function3: LASTDATE

Question 62:

HOTSPOT – You have a query named All Sales that imports sales data into a Power BI model. You plan to create a star schema by separating columns into separate queries. The solution must use All Sales as the source for three other queries named Sales Fact, Product Dimension, and Customer Dimension, and minimize maintenance effort. What should you do to create the Sales Fact query, and for which query should you clear Enable load?

• To create the Sales Fact query, you should: [Option]
• Clear Enable load for: [Option]

Answer:
– To create the Sales Fact query, you should: Reference the All Sales query
– Clear Enable load for: All Sales

Question 63:

You have a Microsoft Power BI report with a 550 MB PBIX file size. The report is on a single page with 25 visuals. Users say that the report is slow to load and interact with. You need to recommend a solution to improve the performance. What should you recommend?

• A. Change any DAX measures to use iterator functions.
• B. Implement row-level security (RLS).
• C. Replace the default visuals with AppSource visuals.
• D. Split the visuals onto multiple pages.

Answer: D
Having 25 visuals on a single page forces Power BI to generate and run numerous queries simultaneously when the page is opened or filtered. Splitting the visuals across multiple, more focused pages is the most effective way to reduce the query load for any single view and significantly improve performance.

Question 64:

You are reviewing a Power BI data model. You have a calculated table that has the following definition:
ProductList = INTERSECT ( ProductsGroupA, ProductsGroupB )
You need to identify the results of the DAX expression. Which rows will be returned in ProductList?

• A. all the rows in ProductsGroupB that have a matching row in ProductsGroupA
• B. all the rows in both tables
• C. all the rows in ProductsGroupA that have a matching row in ProductsGroupB
• D. all the rows in ProductsGroupA that have no matching row in ProductsGroupB.

Answer: C
The INTERSECT function returns the rows of the first table (ProductsGroupA) that also appear in the second table (ProductsGroupB).

Question 65:

You have a Power BI data model that contains two tables named Sales and Date. The Sales table contains three measures named Order Quantity, Product Cost, and Sales Amount. You need to create a matrix visual. In which section of the Fields well should you place the measures?

• A. Columns
• B. Rows
• C. Values
• D. Drill through

Answer: C
In Power BI visuals, numeric measures that you want to aggregate and display (like sum, average, count, etc.) are always placed in the ‘Values’ field well.

Question 66:

HOTSPOT – You have Power BI report that contains the fields shown in the exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

• To enable drill-down from Country to State to City, you should create a: [Option]
• To ensure locations are mapped correctly, you should set the: [Option]

Answer:
– To enable drill-down from Country to State to City, you should create a: Hierarchy
– To ensure locations are mapped correctly, you should set the: Data Category

Question 67:

Question text not available in the provided document.

Answer: Not available.

Question 68:

You have a Power BI model that contains a table named Employees with columns for Employee ID, Department, and Salary. You need to create a DAX measure that will calculate the average salary of all the employees in the sales department. Which DAX expression should you use?

• A. DISTINCTCOUNT(‘Employees’[Salary])
• B. CALCULATE(DISTINCTCOUNT(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
• C. CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
• D. AVERAGE(‘Employees’[Salary])

Answer: C
The CALCULATE function modifies the context of a calculation. This expression first specifies the aggregation (AVERAGE of Salary) and then provides a filter to modify the context to only include rows where the Department is “Sales”.

Question 69:

You use Power Query Editor to preview a query that contains sales order data. The Tax Amount and Freight Amount columns are sometimes null. You need to query the data to identify metrics, including the average freight amount per order. How should you modify the query?

• A. In the Total Item Quantity column, replace the null values with 0.
• B. In the Tax Amount column, remove rows that contain null values.
• C. In the Freight Amount column, remove rows that contain null values.
• D. In the Freight Amount column, replace the null values with 0.

Answer: D
To calculate a correct average freight amount per order, orders with no freight should be included in the calculation with a value of 0. If you remove the null rows, the average will be incorrectly inflated as it would only be the average of orders that had freight. Replacing nulls with 0 ensures all orders are part of the denominator.

Question 70:

You have a Power BI data model that analyzes product sales over time. The auto date/time option for the data model is enabled. You need to reduce the size of the data model while maintaining the ability to analyze product sales by month and quarter. Which two actions should you perform?

• A. Create a relationship between the Date table and the Sales table.
• B. Disable the auto date/time option.
• C. Create a Date table and select Mark as Date Table.
• D. Disable the load on the Date table.
• E. Remove the relationship between the Product table and the Sales table.

Answer: A, C
The “auto date/time” feature creates hidden date tables for every date column in your model, which increases its size. Best practice is to disable this feature (Option B achieves this implicitly) and use a single, dedicated calendar table (Option C). You must then create an active relationship between your new date table and the fact table (Option A).

Question 71:

You have a Microsoft Power BI report with a 550 MB PBIX file size. Users say that the report is slow. You need to recommend a solution to improve the performance. What should you recommend?

• A. Implement row-level security (RLS).
• B. Remove unused columns from tables in the data model.
• C. Replace the default visuals with AppSource visuals.
• D. Enable visual interactions.

Answer: B
Removing unused columns is a fundamental best practice for optimization. It directly reduces the data model’s size, which improves refresh performance and can speed up DAX calculations by reducing memory usage and scan times.

Question 72:

HOTSPOT – You have a Power BI data model that contains a table named Stores. You need to create a calculated column named Active Store Name that meets the following requirements:
– When the value of the Status column is “A”, the value in the Store Name column must be returned.
– When the value of the Status column is NOT “A”, the value in the Store Name column that is prefixed with “Inactive – ” must be returned.
How should you complete the DAX expression?
Active Store Name = [Function](Stores[Status]="A", Stores[Store Name], "Inactive - " [Operator] Stores[Store Name])

• Function: [Option]
• Operator: [Option]

Answer:
– Function: IF
– Operator: &
The IF function is used for conditional logic. The ampersand (&) is the DAX operator for concatenating (joining) text strings.

Question 73:

You have a CSV file that contains user complaints. The file contains a column named Logged, with data in the format: 2018-12-31 at 08:59. You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy. What should you do?

• A. Apply a transformation to extract the first 11 characters of the logged column.
• B. Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of the new column to Whole Number.
• C. Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.
• D. Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.

Answer: C
Column From Examples is a powerful Power Query feature. By providing “2018-12-31” as an example, Power BI will intelligently infer that you want to extract the date portion from the text string for all rows. Setting the new column’s data type to ‘Date’ then enables the automatic date hierarchy.

Question 74:

Note: This question is part of a series of questions that present the same scenario.
From Power Query Editor, you profile the data from an IoT source. You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You create a custom column that concatenates the IoT GUID column and the IoT ID column and then delete the IoT GUID and IoT ID columns.
Does this meet the goal?

• A. Yes
• B. No

Answer: B
This solution does not address the primary goal of analyzing events by hour and day. Concatenating ID columns is unrelated to parsing the DateTime column, which is necessary for the required analysis.

Question 75:

You have a Power BI model that contains an Employee table with EmployeeID and ParentEmployeeID, forming an organizational hierarchy. You need to create a calculated column that returns the count of levels from each employee to the CEO. Which DAX expression should you use?

• A. PATHLENGTH(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]))
• B. PATHITEM(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]),1,INTEGER)
• C. PATHCONTAINS(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]),1)
• D. PATH(Employee[EmployeeID],Employee[ParentEmployeeID])

Answer: A
The PATH function generates a delimited text string representing the hierarchy path for the current row. The PATHLENGTH function then counts the number of items (levels) in that generated path string, which directly answers the question.