PL-300 Hard Questions — 25 Difficult Questions

Question 1

You have a Power BI model with tables: Sales (DateKey, Amount) and Date (DateKey, Year, Month, Day). The measure above is intended to show sales for the current year ignoring any filters on date. However, when placed in a matrix with Month on rows, it returns the same value for every month. What is the most likely reason?

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

Accepted Answer

The FILTER function removes month context from the Date table. The FILTER function uses ALL(Date) which removes all filters from the Date table, including the month filter from the matrix row context. Therefore, the measure always returns the total sales for the entire current year regardless of the month. Option B is correct. Option A is wrong because the expression is valid syntactically. Option C is wrong because there is no relationship ambiguity issue. Option D is wrong because the table name is used correctly.

Answer

The CALCULATE function is misused and should be replaced with SUMX

Answer

The Date table is not marked as a date table

Answer

The relationship between Sales and Date is inactive

Question 2

Which TWO are valid reasons to use a dataflow in Power BI when preparing data?

Accepted Answer

To handle large data volumes that exceed dataset limits. Option A is correct because dataflows store transformed data in Common Data Model (CDM) entities in Azure Data Lake Storage Gen2, so they can process and persist large data volumes that would otherwise exceed a single dataset's size or memory limits. Option B is correct because a dataflow's CDM entities can be consumed by multiple Power BI datasets, letting you define the transformation logic once and reuse the same cleansed data across many datasets instead of duplicating ETL work. Option C is not a valid reason, since dataflows that connect to on-premises sources still require an on-premises data gateway; dataflows do not eliminate that need. Option D is not correct because dataflows are refreshed on a scheduled (batch) basis and are not a mechanism for real-time streaming, which is handled by streaming datasets or push datasets. Option E is not correct because data lineage is a metadata/governance feature surfaced automatically in the workspace, not something a dataflow is used to enforce.

Answer

To avoid using an on-premises data gateway

Answer

To achieve real-time data refresh

Answer

To automatically enforce data lineage

Question 3

Which TWO are best practices for optimizing Power Query performance? (Choose two.)

Accepted Answer

Disable the 'Enable background refresh' option in the query settings.. Disabling 'Enable background refresh' prevents Power Query from running queries in the background while you continue working in Power BI Desktop. This ensures that query execution is synchronous, which can improve performance by avoiding resource contention and allowing you to monitor progress directly. Background refresh can cause delays and unexpected behavior when multiple queries run simultaneously, especially with large data sources.

Answer

Merge tables as early as possible in the query to combine data.

Answer

Keep all columns in the table to avoid missing data.

Answer

Split columns by delimiter to normalize data.

Question 4

You are connecting Power BI to an Azure SQL Database. The database contains a table 'Orders' with 10 million rows. You need to minimize the data load time and ensure that only the most recent 30 days of data are imported. Which approach should you use?

Accepted Answer

Enable incremental refresh policy with a filter on the date column.. Both a native query with a date filter (D) and an incremental refresh policy with a date filter (B) minimize data load by pushing filtering to the source and importing only the recent 30 days. Incremental refresh is not limited to ongoing refreshes; it can also control the initial load range.

Answer

Import the entire table and apply a date filter in Power Query Editor.

Answer

Use DirectQuery mode and apply a date filter in the report.

Question 5

A Power BI data model includes a table 'Orders' with columns OrderID, CustomerID, OrderDate, SalesAmount. The model also has a 'Date' table and a 'Customer' table. The relationships are: Orders[CustomerID] -> Customer[CustomerID] (many-to-one, single direction) and Orders[OrderDate] -> Date[Date] (many-to-one, single direction). A user creates a measure that sums SalesAmount and then filters by a slicer on Customer[City]. The slicer works correctly. However, when the user adds another slicer on Date[Year], the measure does not respect both slicers simultaneously. What is the most likely cause?

Accepted Answer

The measure might be using ALL or ALLEXCEPT that removes the filter context from the Date table.. The measure likely uses ALL or ALLEXCEPT, which removes the filter context from the Date table. Even though the relationships are correctly configured and filters from the Date slicer propagate to Orders via the single-direction relationship, if the measure explicitly ignores those filters using a function like ALL(Date[Year]) or ALLEXCEPT(Orders, ...), the Date slicer will have no effect on the measure. This is a common DAX mistake where filter removal functions override slicer selections.

Answer

The Customer and Date tables are not related to each other.

Answer

The relationship between Orders and Date is inactive.

Answer

The relationships are set to single direction, so filters from Date do not propagate to Orders.

Question 6

Which THREE of the following are valid reasons to create a calculated table in Power BI?

Accepted Answer

Create a summary table that aggregates data from another table. Option B is correct because calculated tables are commonly used to build summary or aggregate tables (for example, using SUMMARIZE or GROUPBY) that consolidate data from existing tables for reporting and model efficiency. Option D is correct because calculated tables can produce a crossjoin of two dimension tables (for example, using CROSSJOIN or GENERATE), which is useful for scenarios like creating a bridge or a many-to-many relationship helper table. Option E is correct because calculated tables are the standard way to generate a date table in DAX (for example, using CALENDAR or CALENDARAUTO) when no suitable date table exists in the source data. Option A is not valid because incremental refresh is configured on the dataset/table refresh policy, not replaced by a calculated table. Option C is not valid because modifying source data before loading is the role of Power Query transformations, not calculated tables, which are computed after data is loaded into the model.

Answer

Replace incremental refresh

Answer

Modify the source data before loading

Question 7

You are troubleshooting a Power Query transformation that groups sales data by ProductID. The query runs slowly and you suspect the filter is being applied after loading all rows. What change would improve performance by pushing the filter to the source?

Accepted Answer

Replace the first three lines with a native SQL query that includes the WHERE clause. Pushing filter logic to the source database via a native SQL query with a WHERE clause reduces the amount of data loaded into Power Query. This leverages query folding, which allows the source (e.g., SQL Server) to perform the filtering before data is transferred, significantly improving performance for large datasets.

Answer

Disable the 'Enable load' option for the SalesTable

Answer

Use CALCULATE in DAX to filter

Answer

Add a 'Table.Buffer' step after the filter

Question 8

You are a Power BI data analyst at a university. You import a table named Enrollments from an on-premises SQL Server database. The table contains a column named StudentEmail that is stored as text and sometimes has leading or trailing spaces. You need to remove these spaces so that the column can be used to create a relationship with a Students table that also has a StudentEmail column. You must perform this cleanup in Power Query, and the transformation must apply to the entire column without creating a new column. Which transformation should you use?

Accepted Answer

On the Transform tab, select Format and then Trim.. Trim is the Power Query transformation that removes leading and trailing whitespace from text values in place. Applying it from the Transform tab modifies the existing StudentEmail column, so the cleaned values can match the Students table and support the relationship. The Add Column variant would create a separate column, and Clean or Replace Values do not correctly remove only the surrounding spaces.

Answer

On the Add Column tab, select Format and then Trim.

Answer

On the Transform tab, select Replace Values and replace a space with an empty value.

Answer

On the Transform tab, select Format and then Clean.

Question 9

You are designing a report for executives. The report contains a matrix visual with many rows and columns. Users complain that the visual is slow to render. Which design change would most improve performance?

Accepted Answer

Use a drill-down hierarchy instead of expanding all levels.. Aggregating data at a higher level reduces the number of data points, improving performance. Drilling down can still provide detail when needed.

Answer

Switch to a table visual.

Answer

Add more filters to the visual.

Answer

Increase the number of measures in the matrix.

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

Refer to the exhibit. You have defined row-level security (RLS) in a Power BI semantic model using Role-level security. The JSON shows three rules for the 'Sales' and 'Targets' tables. The user 'john@contoso.com' belongs to this role. John's manager column in the Sales table has the value 'john@contoso.com'. What data will John see when he queries the model?

Accepted Answer

Sales rows where Region is 'West' and Manager is 'john@contoso.com'; Targets rows where Region is 'West'.. RLS rules are combined with AND logic. John will see only Sales rows where Region is 'West' AND Manager is 'john@contoso.com'. For Targets, only rows with Region 'West'. Option B is incorrect because OR is not used. Option C is incorrect because both rules on Sales apply. Option D is incorrect because all rules apply.

Answer

All Sales and Targets data because John is a manager.

Answer

Sales rows where Region is 'West' OR Manager is 'john@contoso.com'; Targets rows where Region is 'West'.

Answer

Only Sales rows where Manager is 'john@contoso.com'; no Targets restriction.

Question 12

Refer to the exhibit. You have the above Power Query M expression. You notice that the query is taking a long time to load. You suspect that query folding is not occurring for the filter on the Year column. What is the most likely reason?

Accepted Answer

The Year column is stored as text in the database, and Power Query cannot fold the numeric comparison.. When the Year column is stored as text in the database, Power Query cannot fold a numeric comparison (e.g., `Year > 2020`) into the source query. Query folding requires data type compatibility between the filter predicate and the source column; a text-to-integer comparison forces Power Query to fetch all rows and apply the filter locally, causing slow performance.

Answer

The Year column is of type datetime, but the filter is an integer.

Answer

The query uses a native SQL query instead of a table reference.

Answer

The data source is using DirectQuery mode, which prevents folding.

Question 13

A Power BI developer is troubleshooting a report that uses a calculated table. The calculated table is defined as: 'Sales Summary = SUMMARIZE(Sales, Sales[ProductID], "Total Sales", SUM(Sales[Amount]))'. Users report that the 'Total Sales' column shows incorrect values when slicers are applied to the report. What is the most likely cause?

Accepted Answer

Calculated tables are static and do not respond to slicer selections.. Calculated tables in Power BI are evaluated at data refresh time and stored in the model as static data. They do not respond to slicer selections or any other report-level filters because they are not recalculated in the query context. Therefore, the 'Total Sales' column in the 'Sales Summary' table will always show the same aggregated values regardless of slicer interactions, which is why users see incorrect values when applying slicers.

Answer

The calculated table lacks a relationship to the Sales table.

Answer

The SUMMARIZE function syntax is incorrect.

Answer

The calculated table is not marked as a date table.

Question 14

You are a data analyst for a global retail company. The company uses Power BI Premium capacity. You are building a dataset that combines sales data from three sources:
1. An Azure SQL Database that stores transactional sales data (10 million rows per day, retained for 5 years).
2. A SharePoint Online folder containing monthly Excel reports from regional offices (each report has a different structure).
3. A Dataverse table that contains customer feedback scores.

Requirements:
- The dataset must support near real-time reporting for the current month's sales (maximum 15-minute latency).
- Historical sales data (older than current month) can be refreshed daily.
- Customer feedback scores should be updated every hour.
- The Excel reports from SharePoint must be combined into a single table with consistent columns.
- The final dataset should be optimized for fast query performance.

You need to design the data preparation strategy. What should you do?

Accepted Answer

Use a composite model: DirectQuery for the current month's sales data from Azure SQL, and Import mode for historical sales (with incremental refresh) and for customer feedback (with hourly refresh). Combine SharePoint files using Power Query and load them into the model using Import mode. Set up a DirectQuery connection for near real-time.. It uses a composite model to meet all requirements: DirectQuery for near real-time current-month sales (≤15-minute latency), Import mode with incremental refresh for historical sales (daily refresh), Import mode for customer feedback (hourly refresh), and Power Query to combine SharePoint Excel files into a consistent table. This approach balances real-time needs with query performance and refresh flexibility, leveraging Power BI Premium's composite model capabilities.

Answer

Use DirectQuery for all data sources and create views in Azure SQL to transform the SharePoint and Dataverse data. Use Power Query to combine SharePoint files in a view.

Answer

Import all data into Power BI using Import mode. Schedule refreshes every 15 minutes for the current month and daily for historical data.

Answer

Use Azure Data Factory to copy all data to Azure SQL Database, then connect Power BI using DirectQuery.

Question 15

You are designing a Power BI report for a sales team. The team needs to see revenue by product category, but also want to view daily trends for a selected product. The data has over 10 million rows. What visual design approach minimizes report load time?

Accepted Answer

Create a drillthrough page with a line chart showing daily revenue, and use category page as source. Option D is correct because a drillthrough page keeps the main category page lightweight and only loads the daily revenue line chart when a user right-clicks a product and drills through, so the 10 million-row detail query runs on demand rather than on every page render. This design also lets the drillthrough page be filtered to the selected product, reducing the data volume processed by the line chart. Options A and C are wrong because rendering both category and daily-trend visuals on one page forces all queries, including the high-cardinality daily aggregation, to execute on initial load. Option B is wrong because bookmarks only toggle visual visibility on the same page; hidden visuals and their underlying queries can still be evaluated, so it does not reliably reduce load time.

Answer

Use a custom visual that combines both views in one chart

Answer

Use bookmarks to switch between category view and daily trend view

Answer

Place a stacked column chart showing all categories and a line chart for trend on the same page

Question 16

You are a Power BI data analyst for a subscription software company. You import a table named Subscriptions from an OData feed. The table contains a column named BillingPeriod that stores values such as 'Monthly', 'Annual', and 'Quarterly'. A report author needs a numeric column that converts each value to the number of months in the billing period (1, 12, and 3 respectively) so that revenue can be normalized. You must add this column in Power Query without changing the source system. What should you do?

Accepted Answer

Add a conditional column that maps each text value to its corresponding number of months, then change the new column's data type to Whole Number.. A conditional column in Power Query explicitly maps each billing period label to its numeric month count, which is deterministic and easy to audit. Setting the new column to Whole Number makes it usable for arithmetic in the model. The other options either rely on fragile inference, fail outright on non-numeric text, or place the logic in the wrong layer.

Answer

Use the Column From Examples feature, entering '1' next to 'Monthly' and letting Power BI infer the remaining mappings.

Answer

Create a DAX calculated column in the semantic model that uses SWITCH to translate each billing period into months.

Answer

Change the data type of the BillingPeriod column to Whole Number and rely on Power Query's automatic value conversion.

Question 17

You are a Power BI data analyst at a bank. You import a table named Transactions from a SQL Server database. The table has a column named Amount stored as a decimal number and a column named TransactionDate stored as a date. You need to create a new column that contains the cumulative running total of Amount ordered by TransactionDate. The running total must reset for each AccountID. You want to implement this in Power Query without writing any custom M code. Which approach should you use?

Accepted Answer

Sort the table by AccountID and TransactionDate, then use the Add Column tab and select Index Column, and then use the Running Total transformation from the Add Column tab.. The Running Total transformation in Power Query, combined with sorting by AccountID and TransactionDate, produces a cumulative sum of Amount that resets per account. It is a built-in operation that requires no custom M code. The other options either require custom formulas, do not compute a running total, or use grouping that does not provide the desired result.

Answer

Use the Group By transformation to group by AccountID, then apply the All Rows operation, and finally expand the rows to calculate the running total.

Answer

Sort the table by AccountID and TransactionDate, then use the Add Column tab and select Index Column, and then use the Table.AddIndexColumn function with a custom formula.

Answer

Sort the table by AccountID and TransactionDate, then use the Add Index Column transformation, and finally use a custom column to calculate the running total.

Question 18

You are creating a Power BI report that includes a matrix visual showing sales by country and product category. Users need to be able to drill down from country to city, and then to store. They also want to see the data at all levels simultaneously. Which configuration should you apply?

Accepted Answer

Add Country, City, and Store to the Rows well and set 'Stepped layout' to off, then use the 'Expand all' feature.. To show all hierarchy levels simultaneously in a matrix visual, you need to turn off stepped layout and then expand all levels. Stepped layout indents levels, which can make it harder to see all data at once. The 'Expand all' feature, available after turning off stepped layout, displays all levels in a flat table-like format. This is the correct approach.

Answer

Add Country, City, and Store to the Rows well and enable 'Stepped layout'.

Answer

Add Country, City, and Store to the Rows well and enable 'Expand all' by default in the field well.

Answer

Add Country, City, and Store to the Rows well and enable 'Show all levels' in the formatting options.

Question 19

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 20

You are modeling data for a subscription business in Power BI Desktop. The Subscriptions table has StartDate and EndDate columns, and the Dates table is marked as the official date table. Analysts need a measure that counts subscriptions that were active on any given day selected in a slicer from Dates, and the relationship between Subscriptions and Dates must remain inactive to avoid ambiguity. Which approach should you use?

Accepted Answer

Create a measure that uses FILTER over the Subscriptions table comparing StartDate and EndDate to the selected date range, evaluated inside CALCULATE.. Counting subscriptions active on a selected date requires an interval test across two date columns, which no single relationship can express. Iterating the fact table with FILTER and comparing both StartDate and EndDate to the slicer context captures the overlap precisely. USERELATIONSHIP handles only one relationship, calculated columns cannot react to slicer selections, and cross-filter direction does not perform interval logic.

Answer

Change the relationship between Subscriptions and Dates to bidirectional cross-filtering and count the Subscriptions rows.

Answer

Create a measure that uses CALCULATE with USERELATIONSHIP to activate the relationship between Subscriptions and Dates inside the measure.

Answer

Create a calculated column on Subscriptions that flags active rows, then use COUNTROWS on the filtered Subscriptions table in the measure.