PL-300 Timed Hard Questions — 25 Difficult Questions

Question 1

You have a Power BI report that uses DirectQuery to a SQL Server database. Users report that the report is slow when filtering by a slicer on a large dimension table. You need to improve performance without changing the data source. Which approach should you take?

15 minute time limit — choose an answer to begin.

Accepted Answer

Set 'Assume Referential Integrity' on the relationship between tables. Setting 'Assume Referential Integrity' allows Power BI to use more efficient queries (INNER JOIN instead of OUTER JOIN) when filtering, which improves performance. Option A is wrong because creating aggregation tables is not supported in DirectQuery for this scenario. Option B is wrong because changing the storage mode to Dual is not applicable to DirectQuery sources. Option D is wrong because reducing cardinality may affect accuracy and does not address the underlying query efficiency.

Answer

Create an aggregation table on the dimension

Answer

Change the storage mode of the dimension table to Dual

Answer

Reduce the cardinality of the slicer field by grouping values

Question 2

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?

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 3

You are a Power BI developer for a financial services company. The company has a large transactional database in Azure Synapse Analytics. The database contains a table 'Transactions' with 2 billion rows. The table includes columns: TransactionID, AccountID, TransactionDate, Amount, Type (Deposit/Withdrawal), Status (Pending/Completed). You need to build a Power BI semantic model that allows executives to analyze monthly trends of completed deposit amounts by account type (e.g., Savings, Checking). The account type is in a separate 'Accounts' table (1 million rows) with columns: AccountID, AccountType, CustomerID. The model must refresh within 2 hours. Due to the large data volume, you cannot import the entire Transactions table. What should you do?

Accepted Answer

Create an aggregated table in Synapse that pre-aggregates data at the month and account type level, then use DirectQuery on the aggregated table, and use a composite model with the detail table for drill-through.. Creating an aggregated table in Azure Synapse that pre-aggregates data at the month and account type level drastically reduces the data volume. Using DirectQuery on this aggregated table allows fast querying, and a composite model with the detail table enables drill-through to individual transactions when needed. This approach meets the refresh time constraint and handles large data efficiently. Option A is incorrect because incremental refresh with DirectQuery for older data does not solve the initial load issue and can be complex. Option B is incorrect because Power BI aggregations still require the base data to be imported, which is not feasible with 2 billion rows. Option C is incorrect because DirectQuery on the full table would be too slow for analysis.

Answer

Use incremental refresh to import only recent data, and use DirectQuery for older data.

Answer

Import only the necessary columns and use Power BI aggregations to pre-aggregate.

Answer

Use DirectQuery on the full Transactions table and rely on the Synapse query optimizer.

Question 4

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. Dataflows in Power BI allow you to ingest and transform large volumes of data using the Power Query engine running in the cloud, which can handle datasets that exceed the 1 GB per dataset limit in shared capacity or the 10 GB limit in Premium capacities. By storing the transformed data in Azure Data Lake Storage Gen2, dataflows enable you to work with larger data volumes without being constrained by dataset size limits.

Answer

To avoid using an on-premises data gateway

Answer

To achieve real-time data refresh

Answer

To automatically enforce data lineage

Question 5

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 6

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

Use a Native Query that selects only the last 30 days of data.. Using a Native Query that selects only the last 30 days of data pushes the filtering logic to the Azure SQL Database, reducing the amount of data transferred over the network and imported into Power BI. This minimizes load time by avoiding the import of all 10 million rows, while still ensuring only the required 30-day window is brought into the data model.

Answer

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

Answer

Enable incremental refresh policy with a filter on the date column.

Answer

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

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 have a Power BI data model with a table named Employees that contains: EmployeeID, ManagerID, Department, and Salary. You need to create a parent-child hierarchy for the reporting structure. Which THREE actions should you perform?

Accepted Answer

Enable the 'Group on' feature in the visual to expand/collapse. Enabling the 'Group on' feature in a visual (such as a matrix or table) allows users to expand and collapse parent-child hierarchies, making the reporting structure interactive. This feature works with the hierarchy path created by calculated columns (Option B) and the self-referencing relationship (Option C) to display the drill-down behavior.

Answer

Create a separate table for managers

Answer

Set cross filter direction to Both on the relationship

Question 9

Which THREE of the following are best practices for optimizing data load performance in Power BI?

Accepted Answer

Remove unnecessary columns and rows during the import process.. Removing unnecessary columns and rows during the import process reduces the amount of data loaded into the Power BI data model, which directly decreases memory usage and refresh time. By filtering out irrelevant data early in Power Query, you minimize the data volume that must be processed and stored, leading to faster load performance.

Answer

Split a large fact table into multiple smaller fact tables.

Answer

Use DirectQuery mode instead of Import mode to reduce data load time.

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

You are a data analyst at a global retail company. You are building a Power BI semantic model to analyze sales performance across 50 countries. The data source is an Azure SQL Database with tables: Sales (SalesID, ProductID, StoreID, DateKey, Quantity, Amount), Products (ProductID, ProductName, CategoryID), Stores (StoreID, StoreName, CountryID), Countries (CountryID, CountryName), and Dates (DateKey, Date, Year, Month, Quarter). The model must support: 1) Hierarchical drill-down from Year to Quarter to Month. 2) Slicers for Country and Product Category. 3) Measures for Total Sales, Year-over-Year growth, and Moving Average (last 12 months). 4) The ability to filter by date range (e.g., last 3 months) while preserving the ability to show YoY growth for the selected period. The database contains 500 million rows in the Sales table. The company has strict performance requirements: report pages must load within 5 seconds. You need to design the model in Power BI Desktop. Which approach should you take?

Accepted Answer

Use Import storage mode for all tables with incremental refresh policy on the Sales table to load only the last 5 years of data.. Using Import mode with incremental refresh on the Sales table (e.g., last 5 years) reduces data volume significantly, leverages Power BI's in-memory compression and VertiPaq engine for fast queries, and allows creating a proper date table for hierarchical drill-down and time intelligence measures like YoY growth and moving average. Option A is wrong because DirectQuery on a 500-million-row Sales table would result in slow query performance, especially for complex measures and drill-downs, and does not leverage Power BI's caching. Option B is wrong because a composite model with DirectQuery on the large fact table still requires round trips to the source for aggregations, which can be slow and may not meet the 5-second load requirement. Option D is wrong because relying on the DateKey column alone prevents creating a proper date hierarchy (Year → Quarter → Month) and limits the use of built-in time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR), which require a continuous date table.

Answer

Use DirectQuery storage mode for all tables to ensure real-time data and aggregate queries at the source.

Answer

Use a composite model: Import for dimension tables and DirectQuery for Sales table to balance freshness and performance.

Answer

Use Import mode but do not create a date table; instead use the DateKey column from Sales for time intelligence.

Question 13

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 14

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model includes a large fact table with 100 million rows. Users are experiencing slow report performance. Which TWO actions should the developer take to improve query performance?

Accepted Answer

Create indexes on columns used in filters and relationships.. In DirectQuery mode, incremental refresh is not supported (option A is incorrect). Creating indexes on columns used in filters and relationships (option B) speeds up query execution on SQL Server. Removing unused columns from the fact table (option C) reduces the amount of data transferred per query. Hiding columns (option D) does not affect the data retrieved by queries. Adding calculated columns (option E) increases query overhead and degrades performance.

Answer

Configure incremental refresh to limit data retrieved per query.

Answer

Hide columns that are not needed in reports.

Answer

Add calculated columns to precompute aggregations.

Question 15

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 16

Which THREE of the following are best practices for designing a Power BI data model?

Accepted Answer

Implement business logic in measures rather than calculated columns. Options C, D, and E are correct. For Power BI data models, it is a best practice to implement business logic in measures rather than calculated columns (C), use a star schema design with dimension and fact tables (D), and use surrogate keys for dimension tables (E). Option A is incorrect because composite keys can complicate relationships and negatively impact performance; surrogate keys are preferred. Option B is incorrect because bidirectional cross-filtering should be used sparingly and only when necessary, not by default, as it can lead to ambiguous filter propagation and performance issues.

Answer

Use composite keys in relationships for better performance

Answer

Enable bidirectional cross-filtering by default

Question 17

Which THREE of the following are best practices when designing a Power BI data model for performance?

Accepted Answer

Hide columns that are not needed in reports.. Correct answers are A, B, and C. A: Hiding columns that are not needed reduces model size and improves clarity. B: Avoiding bi-directional cross-filtering prevents performance degradation caused by complex filter propagation. C: Using a star schema design with dimension and fact tables optimizes query performance. Option D is incorrect because using many-to-many relationships without bridge tables can cause ambiguity and poor performance. Option E is incorrect because measures are generally more efficient than calculated columns for dynamic aggregations.

Answer

Use many-to-many relationships directly without bridge tables.

Answer

Use calculated columns instead of measures for aggregations.

Question 18

You are building a Power BI model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, and TotalAmount. You also have a table 'Customers' with columns: CustomerID, CustomerName, and Segment. You need to create a relationship between Orders and Customers on CustomerID. Which relationship configuration should you choose to ensure that filtering Customers by Segment correctly filters Orders?

Accepted Answer

One-to-many relationship from Customers to Orders. A one-to-many relationship from Customers (one side) to Orders (many side) with single-direction cross-filtering ensures that filtering Customers by Segment correctly filters Orders. Option B (many-to-one) would reverse the cardinality and, by default, would not allow filtering from Customers to Orders. Option C is unnecessary and can cause ambiguity. Option D is inappropriate as one customer can have many orders.

Answer

Many-to-one relationship from Orders to Customers

Answer

Many-to-many relationship with a bridge table

Answer

One-to-one relationship

Question 19

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. Using a drillthrough page with a single visual reduces initial load because the detail visual only loads when the user drills through. This minimizes the amount of data rendered on the main report page, improving performance for large datasets.

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 20

You are reviewing a Power BI model definition with incremental refresh policy. The Sales table has an incremental refresh policy that refreshes the last 365 days. However, the report shows data for dates beyond 365 days ago. What is the most likely reason?

Accepted Answer

Incremental refresh only refreshes the data, it does not remove historical data. Incremental refresh policy only affects refresh operations; it does not delete historical data from the model. Data older than 365 days remains in the model unless explicitly removed. Option A is wrong because the policy is defined for the table, not partitions. Option C is wrong because the policy is set to refresh the last 365 days, not filter. Option D is wrong because incremental refresh does not automatically remove old data; it only refreshes recent data.

Answer

The incremental refresh policy is applied at the partition level instead of the table level

Answer

The incrementalPeriodsOffset should be set to a negative value to include historical data

Answer

The refresh policy automatically removes data older than the incremental period