PL-300 Practice Test Part 4 | Model Optimization & DAX Measures (Q76-100)

Question 76:

You have a Microsoft Power BI report. The size of the PBIX file is 550 MB. The report uses an imported dataset with 12 million rows. The report is a single page containing 25 visuals. Users say that the report is slow to load and interact with. You need to recommend a solution to improve performance. What should you recommend?

• A. Replace the default visuals with AppSource visuals.
• B. Remove unused columns from tables in the data model.
• C. Change the imported dataset to DirectQuery.
• D. Increase the number of times that the dataset is refreshed.

Answer: B
Removing unused columns is a key optimization technique. It reduces the model’s size in memory, which leads to faster data processing, quicker DAX calculations, and improved overall report performance.

Question 77:

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. Change the data type of the Logged column to Date.
• B. Split the Logged column by using ” at ” as the delimiter.
• C. Add a conditional column that outputs 2018 if the Logged column starts with 2018.
• D. Apply the Parse function from the Date transformations options to the Logged column.

Answer: B
Splitting the column by the delimiter ” at ” will separate the date part (“2018-12-31”) from the time part. Power Query is intelligent enough to recognize the first resulting column as a date, which can then be used for analysis with a date hierarchy.

Question 78:

You have a Power BI report that contains a page with a shape (Shape1), a card (Sales Summary), and a bar chart (Sales by Region). You need to ensure that Sales Summary renders on top of Shape1. What should you modify?

• A. Tab order in the Selection pane
• B. Layer order in the Selection pane
• C. Maintain layer order in the General visual settings
• D. Vertical alignment in the Canvas settings

Answer: B
The Layer order in the Selection pane controls the stacking order (z-index) of objects on the canvas. Moving an item higher in the list brings it forward, making it render on top of items lower in the list.

Question 79:

HOTSPOT – You have the Power BI data model shown in the exhibit. The data model must support analysis of total sales by product by order month, quantities sold by order day, and number of sales transactions by order quarter. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

• Auto date/time is enabled.
• You can analyze sales by order date.
• The model size is optimized.

Answer:
– Auto date/time is enabled: Yes (The calendar icon next to the OrderDate field indicates the presence of a hidden auto date/time table).
– You can analyze sales by order date: Yes (The enabled auto date/time feature creates the necessary hierarchy for analysis).
– The model size is optimized: No (Auto date/time creates hidden tables for each date field, which increases the model size. Best practice for optimization is to disable it and use a single, dedicated date table).

Question 80:

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

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

Answer: A
The “Column From Examples” feature in Power Query allows you to provide a sample of the desired output. By typing “2018-12-31,” Power Query infers the transformation to extract the date portion. Explicitly setting the data type of this new column to ‘Date’ ensures it can be used with date hierarchies.

Question 81:

You are implementing dynamic row-level security (RLS) in a Power BI data model. You need to create a table filter to ensure users see only their own employee data. The DAX expression must work in both Power BI Desktop and the Power BI service. Which expression should you use?

• A. [Email Address] – USERNAME()
• B. [Employee Name] – USERPRINCIPALNAME()
• C. [Employee Name] = USERNAME()
• D. [Email Address] = USERPRINCIPALNAME()

Answer: D
USERPRINCIPALNAME() returns the User Principal Name (UPN), which is typically the user’s email address (e.g., user@domain.com). This function works consistently in both Power BI Desktop and the Power BI service, making it the standard for dynamic RLS based on the logged-in user.

Question 82:

You are building a data model and use Power Query to load a query that contains renamed and custom columns. Later, you attempt to reload the query and receive the error: “Expression.Error: The column ‘Category’ of the table wasn’t found.” What are two possible causes of the error?

• A. The column was removed from the source file.
• B. The column was renamed in the source file.
• C. The file is no longer in the specified location.
• D. The data type of the column was changed.

Answer: A, B
This error occurs when a step in your Power Query transformation references a column name that no longer exists at that point in the process. This is most commonly caused by the column being either completely removed or renamed in the original source file, so the initial “Source” step of the query fails to find it.

Question 83:

You have data formatted as shown in a table with a ‘Machine-User’ column. You need to create a clustered bar chart that separates Machine and User, as shown in the exhibit. What should you do?

• A. From Power Query Editor, split the Machine-User column by using a delimiter.
• B. From Power Query Editor, create a column that contains the last three digits of the Machine-User column.
• C. In a DAX function, create two calculated columns named Machine and User by using the SUBSTITUTE function.
• D. In a DAX function, create two measures named Machine and User by using the SUBSTITUTE function.

Answer: A
The most direct way to separate a combined column like ‘Machine-User’ into two distinct columns is by using the “Split Column” transformation in Power Query Editor, specifying the hyphen (-) as the delimiter. This is a data modeling step that should be done before creating visuals.

Question 84:

DRAG DROP – You need to create a date table in Power BI that must contain 10 full calendar years, including the current year. How should you complete the DAX expression?

Date = CALENDAR( DATE(YEAR(TODAY()) - 9, 1, 1), DATE(YEAR(TODAY()), 12, 31) )

Fill in the blanks for a similar expression using different functions.

Answer: The provided DAX is correct. An alternative using variables could be:
VAR V_MinYear = YEAR ( TODAY () ) - 9
VAR V_MaxYear = YEAR ( TODAY () )
RETURN CALENDAR ( DATE ( V_MinYear, 1, 1 ), DATE ( V_MaxYear, 12, 31 ) )

Question 85:

Note: This question is part of a series of questions that present the same scenario.
You have a Power BI report with a sales table that has three date keys. You need to support analysis based on all three dates.
Solution: You create measures that use the USERELATIONSHIP DAX function to filter sales on the active relationship between the sales table and the date table.
Does this meet the goal?

• A. Yes
• B. No

Answer: B
No. The USERELATIONSHIP function is specifically used to activate an *inactive* relationship for the duration of a calculation. It cannot be used with an already active relationship.

Question 86:

HOTSPOT – You have a Power BI report that contains a measure named Total Sales. You need to create a new measure that will return the sum of Total Sales for a year up to a selected date. How should you complete the DAX expression?

Sales YTD = [Function1]([Measure], [DateColumn])

• Function1: [Option]
• Measure: [Option]
• DateColumn: [Option]

Answer:
– Function1: TOTALYTD
– Measure: [Total Sales]
– DateColumn: ‘Date'[Date]

Question 87:

DRAG DROP – You have a Sales table and a Transaction Size table. You need to create a calculated column in Sales to classify each transaction as small, medium, or large based on the Sales Amount. How should you complete the code?

=CALCULATE( VALUES('Transaction Size'[Category]), [Filter_Expression] )

Answer: The filter expression should be:
FILTER('Transaction Size', Sales[Sales Amount] >= 'Transaction Size'[Min] && Sales[Sales Amount] < 'Transaction Size'[Max])
This creates a relationship-on-the-fly between the tables for each row of the Sales table to find the correct category.

Question 88:

You have a Power BI report for the procurement department with a Suppliers table and a LineItems table. The report contains visuals for Supplier Name, Invoice Count, and Total Price. You need to minimize the size of the dataset without affecting the visuals. What should you do?

• A. Merge Suppliers and LineItems.
• B. Remove the LineItems[Description] column.
• C. Remove the rows from LineItems where LineItems[Invoice Date] is before the beginning of last month.
• D. Group LineItems by LineItems[Invoice ID] and LineItems[Invoice Date] with a sum of LineItems[Price].

Answer: B
The visuals use Supplier Name, Invoice ID (for counting), and Price. The 'Description' column is not used in any visual. Removing this high-cardinality text column is an effective way to reduce the dataset size without impacting the required analysis.

Question 89:

You have a Power BI report for marketing with a Posts table and a Traffic table. The dataset takes a long time to refresh. You need to modify the queries to reduce load times. Which two actions will reduce the load times?

• A. Remove the rows in Posts in which Posts[Publish Date] is in the last seven days.
• B. Remove the rows in Traffic in which Traffic[URL Visited] does not contain "blog".
• C. Remove Traffic[IP Address], Traffic[Browser Agent], and Traffic[Referring URL].
• D. Remove Posts[Full Text] and Posts[Summary].
• E. Remove the rows in Traffic in which Traffic[Referring URL] does not start with "/".

Answer: B, D
D: The visuals do not use 'Full Text' or 'Summary', which are likely large text columns. Removing them will significantly reduce data volume. B: Filtering the Traffic table to only include relevant 'blog' URLs reduces the number of rows that need to be processed and loaded, which improves performance.

Question 90:

HOTSPOT - You are creating a quick measure as shown in the exhibit. You need to create a monthly rolling average measure for Sales over time. How should you configure the quick measure calculation?

• Base value: [Option]
• Date: [Option]
• Period: [Option]
• Number of periods: [Option]

Answer:
- Calculation: Rolling average
- Base value: Sum of Sales
- Date: 'Date'[Date]
- Period: Months

Question 91:

You have the Power BI data model shown in the exhibit. The Sales table contains records from the last five years. You plan to create a measure to return the total sales of March 2021 when March 2022 is selected. Which DAX expression should you use?

• A. CALCULATE(SUM(Sales[Sales]), PREVIOUSYEAR(dimDate[Date]))
• B. TOTALYTD(SUM(Sales[Sales]), dimDate[Date])
• C. CALCULATE(SUM(Sales[Sales]), SAMEPERIODLASTYEAR(dimDate[Date]))
• D. SUM(Sales[Sales])

Answer: C
The SAMEPERIODLASTYEAR function is specifically designed for this purpose. It takes a set of dates in the current context (e.g., March 2022) and returns a table of dates shifted back one year (March 2021). CALCULATE then uses this table as a filter to sum the sales for that previous period.

Question 92:

You use Power BI Desktop to load data from a SQL Server database. While waiting for the data to load, you receive a timeout error. You need to resolve the error. What are two ways to achieve the goal?

• A. Reduce the number of rows and columns returned by each query.
• B. Split long-running queries into subsets of columns and use Power Query to merge the queries.
• C. Use Power Query to combine long-running queries into one query.
• D. Disable query folding on long running queries.

Answer: A, B
Timeout errors occur when a query takes too long to execute. A: The most direct solution is to reduce the amount of data being requested, either by filtering rows (e.g., with a WHERE clause) or selecting fewer columns. B: If you cannot reduce the data, you can sometimes break a very wide, slow query into multiple, faster queries (each with a subset of columns) and then merge them back together in Power Query. This can sometimes bypass source-side performance issues.

Question 93:

Note: This question is part of a series... From Power Query Editor, you profile IoT data. You need to analyze events by hour and day of the year and improve performance.
Solution: You split the IoT DateTime column into a column named Date and a column named Time.
Does this meet the goal?

• A. Yes
• B. No

Answer: A
Yes. Splitting the DateTime column directly enables analysis by date and by time. It also improves performance because columns with lower cardinality (fewer unique values), like a Date column or a Time column, compress much better in the data model than a high-cardinality DateTime column.

Question 94:

Note: This question is part of a series... From Power Query Editor, you profile IoT data. You need to analyze events by hour and day of the year and improve performance. The IoT GUID and IoT ID columns are unique to each row.
Solution: You remove the IoT GUID column and retain the IoT ID column.
Does this meet the goal?

• A. Yes
• B. No

Answer: B
No. While removing a high-cardinality column like a GUID does improve performance, this solution completely fails to address the primary requirement, which is to enable analysis by the hour and day of the year. The DateTime column remains untouched.

Question 95:

Note: This question is part of a series... From Power Query Editor, you profile IoT data. You need to analyze events by hour and day of the year and improve performance.
Solution: You change the IoT DateTime column to the Date data type.
Does this meet the goal?

• A. Yes
• B. No

Answer: B
No. Changing the data type of the IoT DateTime column to 'Date' would discard the time information. This makes it impossible to analyze events by the hour, which is a key requirement.

Question 96:

DRAG DROP - You have a data model with a Products table and a Sales table. You have a report-level filter on Products. You need to create a measure that will return the percent of total sales for each product, respecting the report-level filter when calculating the total. How should you complete the DAX measure?

% of Total Sales = DIVIDE( SUM(Sales[SalesAmount]), [Function1](SUM(Sales[SalesAmount]), [Function2](Products)) )

Answer:
- Function1: CALCULATE
- Function2: ALLSELECTED
ALLSELECTED removes the filters from the visual context (the specific product) but respects filters coming from outside the visual (the report-level filter). This allows you to calculate the correct percentage against the filtered total.

Question 97:

You are creating a sales report in Power BI. You have a Store table with columns for City, State/Province, and Country. You plan to create a map visual that will show store locations and provide the ability to drill down from Country to State/Province to City. What should you do to ensure that the locations are mapped properly?

• A. Change the data type of City, State/Province, and Country.
• B. Set Summarization for City, State/Province, and Country to Don't summarize.
• C. Set the data category of City, State/Province, and Country.
• D. Create a calculated column that concatenates the values in City, State/Province, and Country.

Answer: C
Setting the Data Category for each column (e.g., setting the 'City' column to the 'City' category) provides a hint to the Power BI mapping engine, helping it to disambiguate locations (e.g., Paris, Texas vs. Paris, France) and render them correctly.

Question 98:

You are creating a sales report for the NorthWest region from a SQL Server view. The report requires analysis of order count, total sales by date, customer count, and average quantity per order. You need to reduce data refresh and report query times. Which two actions should you perform?

• A. Set the data type for SalesOrderNumber to Decimal Number.
• B. Remove the CustomerKey and ProductKey columns.
• C. Remove the TaxAmt and Freight columns.
• D. Filter the data to only the NorthWest region sales territory.

Answer: C, D
D: Filtering the data to only the required region ('NorthWest') at the source is the most effective way to reduce the number of rows processed. C: Since TaxAmt and Freight are not required for the specified analysis, removing these columns reduces the data volume and improves performance.

Question 99:

DRAG DROP - You are creating a Power BI model. You have a single table named Product with fields for ID, Name, Color, Category, and Total Sales. You need to create a calculated table that shows only the top 8 products based on the highest value in Total Sales. How should you complete the DAX expression?

Top 8 Products = [Function1](8, [Table], [OrderByExpression], [Order])

Answer:
- Function1: TOPN
- Table: Product
- OrderByExpression: [Total Sales]
- Order: DESC

Question 100:

HOTSPOT - You are building a financial report. You have a table named financials with Date and Sales columns. You need to create a measure that calculates the relative change in sales as compared to the previous quarter. How should you complete the measure?

VAR PreviousQuarter = CALCULATE([Total Sales], [Function]('financials'[Date]))
RETURN DIVIDE(([Total Sales] - PreviousQuarter), PreviousQuarter)

• Function: [Option]

Answer:
- Function: PREVIOUSQUARTER
The PREVIOUSQUARTER time intelligence function returns a table of dates from the quarter immediately preceding the current context, which is exactly what is needed to calculate the sales for the previous quarter.