PL-300 Practice Quiz: Optimize a Model for Performance in Power BI

Instructions

• This quiz contains 40 questions covering all concepts from “Optimize a model for performance in Power BI”
• Choose the best answer for each question
• Some questions may have multiple correct answers (marked as “Select all that apply”)
• Answers and explanations are provided at the end

Section 1: Performance Optimization Fundamentals (Questions 1-8)

Question 1: What is the primary goal of performance optimization in Power BI? - A) To make the semantic model more complex - B) To make changes to the current state of the semantic model so it runs more efficiently - C) To add more visuals to reports - D) To increase the file size of the model

Question 2: You are working as a Power BI developer. Users complain that report pages take too long to load and tables don’t update quickly when filters are applied. What should be your FIRST step? - A) Delete all visuals and start over - B) Identify where the source of the problem is by using Performance Analyzer - C) Immediately switch to DirectQuery mode - D) Add more RAM to the server

Question 3: Which of the following are benefits of optimizing a semantic model? (Select all that apply) - A) Uses less resources (memory) - B) Achieves faster data refresh - C) Improves rendering of visuals in reports - D) Automatically fixes all DAX formulas

Question 4: What percentage of a data analyst’s time is typically spent working with data, according to the module? - A) 50 percent - B) 70 percent - C) 90 percent - D) 100 percent

Question 5: Which of the following design decisions should you make to optimize a semantic model? (Select all that apply) - A) Ensure correct data types are used - B) Remove unnecessary columns and rows - C) Avoid repeated values - D) Keep all columns for potential future use - E) Surface numeric columns as measures - F) Reduce column cardinality

Question 6: Poor performance in Power BI is most often a direct result of: - A) Insufficient user training - B) A poorly designed semantic model or inefficient DAX calculations - C) Network connectivity issues only - D) Power BI service limitations

Question 7: A smaller sized semantic model provides which benefits? (Select all that apply) - A) Faster data refresh - B) Faster calculations - C) Faster rendering of visuals - D) More complex relationships - E) Uses less memory

Question 8: You need to minimize the size of a semantic model. Which approach should you take? - A) Add more calculated columns - B) Import all available data from the source - C) Remove unnecessary columns and rows - D) Create multiple relationships between the same tables

Section 2: Performance Analyzer & Troubleshooting (Questions 9-16)

Question 9: Before running Performance Analyzer, what should you do to ensure accurate results? (Select all that apply) - A) Clear the visual cache - B) Clear the data engine cache - C) Close and reopen Power BI Desktop - D) Add a blank page and save the file

Question 10: How do you clear the visual cache in Power BI Desktop? - A) Use the Clear Cache button in Performance Analyzer - B) Add a blank page, save with that page selected, close Power BI Desktop, and reopen it - C) Restart your computer - D) Delete all visuals from the report

Question 11: In Performance Analyzer, what are the three categories of tasks measured for each visual? (Select all that apply) - A) DAX query - B) Visual display - C) Other - D) Network latency - E) Memory allocation

Question 12: You run Performance Analyzer and notice that the “Other” category shows a long duration. What does this indicate? - A) The visual itself is poorly designed - B) The DAX query needs optimization - C) Other visuals on the page need optimization, or there are too many visuals - D) The data source connection is slow

Question 13: A DAX query in Performance Analyzer shows a duration of 250 milliseconds. What should you do? - A) Ignore it as it’s acceptable performance - B) Investigate it as it exceeds the 120ms threshold - C) Delete the measure immediately - D) Switch to DirectQuery mode

Question 14: You want to analyze queries in more detail beyond what Performance Analyzer shows. Which tool should you use? - A) Power Query Editor - B) DAX Studio - C) Excel Power Pivot - D) SQL Server Management Studio

Question 15: To improve visual performance, you should consider: (Select all that apply) - A) Reducing the number of visuals on the report page - B) Using drill-through pages instead of multiple visuals - C) Adding more than 100 fields to each visual - D) Using report page tooltips for additional details - E) Reducing the number of fields in each visual

Question 16: You have a visual with 150 fields. What is the most likely impact on performance? - A) No impact - Power BI can handle unlimited fields - B) Slow loading times because it exceeds the 100 field limit - C) Improved performance due to more data - D) Automatic aggregation will occur

Section 3: DAX Query Optimization (Questions 17-22)

Question 17: You have the following DAX measure:

Sales YoY Growth =
DIVIDE(
    ([Sales] - CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH))),
    CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH))
)

What is the problem with this formula? - A) It uses incorrect functions - B) It evaluates the same expression twice, reducing performance - C) It has syntax errors - D) It doesn’t calculate year-over-year growth correctly

Question 18: Which of the following is the BEST way to optimize the formula in Question 17? - A) Use EARLIER function - B) Use variables (VAR) to store repeated calculations - C) Split it into multiple measures - D) Use SUMX instead of CALCULATE

Question 19: What are the advantages of using variables in DAX? (Select all that apply) - A) Improved performance - B) Improved readability - C) Simplified debugging - D) Reduced complexity - E) Automatic data refresh

Question 20: You replaced a FILTER function with KEEPFILTERS in a DAX measure and re-ran Performance Analyzer. What should you do to ensure valid comparison? - A) Nothing - just compare the results - B) Clear the data cache before re-running - C) Restart Power BI Desktop - D) Publish to Power BI Service first

Question 21: Which DAX function should you generally prefer for better performance when filtering? - A) FILTER with complex conditions - B) KEEPFILTERS for simple conditions - C) ALL - D) ALLEXCEPT

Question 22: Variables in DAX are declared using which keyword? - A) DIM - B) LET - C) VAR - D) DECLARE

Section 4: Metadata & Model Analysis (Questions 23-28)

Question 23: You need to analyze metadata in your semantic model. Where should you perform this analysis? - A) In the Power BI Service - B) In Power Query Editor - C) In DAX Studio only - D) In the Data view

Question 24: Which Power Query features help you analyze column metadata? (Select all that apply) - A) Column quality - B) Column distribution - C) Column profile - D) Column aggregation

Question 25: In Power Query, Column Distribution shows: - A) Only the distinct values count - B) Only the unique values count - C) Both distinct and unique values count - D) The data type of the column

Question 26: You have a column in Power Query showing 95% valid data, 3% errors, and 2% empty. What should you do? - A) Ignore it and proceed - B) Investigate the errors and empty values - C) Delete the entire column - D) Change the data type

Question 27: By default, Power Query column profiling analyzes: - A) The entire dataset - B) The top 100 rows - C) The top 1000 rows - D) The bottom 1000 rows

Question 28: You should rename queries in Power Query to: - A) Make them more complex - B) Make them more obvious and user-friendly - C) Match the source table names exactly - D) Include special characters

Section 5: Relationships & Cardinality (Questions 29-33)

Question 29: What is cardinality in the context of Power BI? - A) Only the number of rows in a table - B) The uniqueness of values in a column and the direction of relationships - C) The size of the data model - D) The number of tables in the model

Question 30: Which relationship cardinality is most common in Power BI? - A) One-to-one (1:1) - B) Many-to-one (:1) - C) One-to-many (1:) - D) Many-to-many (:)

Question 31: Lower cardinality in columns leads to: - A) Worse performance - B) More optimized performance - C) No change in performance - D) Increased file size

Question 32: When creating relationships between tables, both columns must have: - A) The same column name - B) The same data type - C) The same number of rows - D) The same number of unique values

Question 33: Which data type performs better for relationship columns? - A) Text - B) Integer/Whole number - C) Decimal - D) Boolean

Section 6: Auto Date/Time & Features (Questions 34-36)

Question 34: The Auto date/time feature in Power BI: - A) Always improves performance - B) Creates hidden calculated tables for date columns - C) Should always be enabled - D) Reduces model size

Question 35: When should you DISABLE the Auto date/time option? - A) When working with calendar time periods - B) When your data source already has a date dimension table - C) When you have simplistic model requirements - D) Never - it should always be enabled

Question 36: Where can you configure the Auto date/time option? (Select all that apply) - A) Globally for all Power BI Desktop files - B) For the current file only - C) In the Power BI Service - D) Through File > Options and settings > Options > Data Load

Section 7: Data Reduction & Aggregations (Questions 37-44)

Question 37: You have a fact table with millions of transaction rows. What is an effective technique to reduce model size? - A) Delete all historical data - B) Use a summary table that aggregates data by date, customer, and product - C) Switch all tables to DirectQuery - D) Remove all relationships

Question 38: What is a potential disadvantage of using aggregated data? - A) Faster query performance - B) Loss of ability to drill into detailed data - C) Reduced model size - D) Improved refresh times

Question 39: In a Composite model, you can set the Storage Mode property for each table to: - A) Import only - B) DirectQuery only - C) Import, DirectQuery, or Dual - D) Aggregated only

Question 40: You create aggregations in Power Query by grouping OrderDate and summarizing SalesAmount. Your original table had 100,000 rows. After aggregation by date, you have 365 rows. What is the approximate data reduction? - A) 50% reduction - B) 75% reduction - C) 99% reduction - D) 25% reduction

Question 41: Where do you manage aggregations after creating them in Power BI Desktop? - A) By right-clicking the table and selecting “Manage aggregations” - B) In Power Query Editor only - C) In the Modeling tab - D) In the Power BI Service

Question 42: Organizations should use aggregations when: (Select all that apply) - A) Working with large volumes of data - B) Experiencing slow data refresh - C) Having a large semantic model - D) Anticipating future growth of the model - E) Working with small datasets only

Question 43: When creating aggregations in Power Query, which operations can you perform? (Select all that apply) - A) Sum - B) Count - C) Average - D) Minimum - E) Maximum

Question 44: What happens to query performance after properly implementing aggregations? - A) Query performance decreases - B) Query performance improves because aggregated data is cached - C) No change in query performance - D) Queries fail

Section 8: DirectQuery Optimization (Questions 45-52)

Question 45: In DirectQuery mode, where is the data stored? - A) In the Power BI Desktop file - B) In the Power BI Service - C) In the source database - Power BI connects directly - D) In memory

Question 46: When should you use DirectQuery instead of Import mode? (Select all that apply) - A) Data changes frequently and near real-time reporting is required - B) Need to handle large data without pre-aggregation - C) Data sovereignty restrictions apply - D) Working with small, static datasets - E) Connecting to multidimensional sources like SAP BW

Question 47: What is a major limitation of DirectQuery mode? - A) Cannot create any visuals - B) Performance depends heavily on the underlying data source - C) Cannot use DAX at all - D) Limited to 100 rows of data

Question 48: In DirectQuery mode, when are queries sent to the underlying source? - A) Only when you refresh the dataset - B) When you initially load the data into Power BI Desktop - C) When visuals are added or interacted with in reports - D) Never - data is cached

Question 49: You are using DirectQuery and experiencing slow report performance. What should you optimize FIRST? - A) Add more visuals to the report - B) The source database performance - C) The Power BI Service capacity - D) The network infrastructure

Question 50: Which of the following are database optimization techniques for DirectQuery? (Select all that apply) - A) Avoid complex calculated columns - B) Review and optimize table indexes - C) Push expressions back to the source - D) Import all data instead - E) Add surrogate key columns to dimension tables

Question 51: You need to reduce the number of queries sent to a DirectQuery source. Which Query reduction options can you enable? (Select all that apply) - A) Reduce number of queries sent by disabling default visual interactions - B) Add an apply button to slicers - C) Add an apply button to filters - D) Delete all slicers from the report

Question 52: What happens when you initially use Get Data with DirectQuery in Power BI Desktop? - A) All data is imported - B) Only the schema is loaded - C) No connection is made - D) Data is compressed

Section 9: Advanced Scenarios (Questions 53-60)

Question 53: You have a DirectQuery model with 20 visuals on a report page. 10 users open the report simultaneously. How many queries (minimum) will be sent to the data source? - A) 20 queries - B) 100 queries - C) 200 queries - D) 10 queries

Question 54: You want to combine Import and DirectQuery modes in the same model. Which feature enables this? - A) Hybrid mode - B) Composite model - C) Dual mode - D) Mixed mode

Question 55: A measure displays consistently high duration in Performance Analyzer across multiple tests. The issue is MOST likely: - A) The visual design - B) Network latency - C) The DAX formula needs optimization - D) Power BI Service limitations

Question 56: You need to optimize a model for both detailed transaction reporting and high-level summary reports. What approach should you use? - A) Use only Import mode with all transaction details - B) Use only DirectQuery for everything - C) Use a Composite model with aggregated Import tables and DirectQuery for details - D) Create two separate models

Question 57: Which statement about column cardinality is TRUE? - A) High cardinality means many repeated values - B) Low cardinality means many unique values - C) High cardinality means many unique values - D) Cardinality doesn’t affect performance

Question 58: You analyze a semantic model and find it has redundant columns containing ID values with thousands of unique rows that aren’t used in relationships or reports. What should you do? - A) Keep them for future use - B) Remove them to reduce model size - C) Create relationships with them - D) Change their data type

Question 59: In Power Query, you see that a query name is “Table_20231015_final_v2”. What should you do? - A) Keep the name as it’s specific - B) Rename it to something more meaningful and user-friendly - C) Delete the query - D) Copy the query

Question 60: You want to analyze DAX query performance in detail. Which tool provides the most comprehensive analysis? - A) Performance Analyzer alone - B) Power Query Editor - C) DAX Studio - D) Power BI Service

Answer Key and Explanations

Section 1: Performance Optimization Fundamentals

Q1: B - Performance optimization involves making changes to the current state of the semantic model so it runs more efficiently.

Q2: B - Always identify the source of performance problems first using Performance Analyzer before taking action.

Q3: A, B, C - Optimized models use less memory, achieve faster refresh, and improve visual rendering. They don’t automatically fix DAX formulas.

Q4: C - According to the module, data analysts spend approximately 90% of their time working with data.

Q5: A, B, C, E, F - All are correct except “Keep all columns for potential future use” - you should remove unnecessary columns.

Q6: B - Poor performance is most often caused by poorly designed semantic models or inefficient DAX calculations.

Q7: A, B, C, E - Smaller models provide faster refresh, calculations, and rendering, and use less memory. They don’t create more complex relationships.

Q8: C - Removing unnecessary columns and rows minimizes model size.

Section 2: Performance Analyzer & Troubleshooting

Q9: A, B, C, D - All are necessary to ensure accurate Performance Analyzer results.

Q10: B - Add a blank page, save with that page selected, close and reopen Power BI Desktop.

Q11: A, B, C - The three categories are DAX query, Visual display, and Other.

Q12: C - Long “Other” duration indicates other visuals need optimization or there are too many visuals.

Q13: B - DAX queries taking longer than 120ms should be investigated.

Q14: B - DAX Studio provides detailed DAX query analysis beyond Performance Analyzer.

Q15: A, B, D, E - All except adding more than 100 fields (which hurts performance).

Q16: B - Visuals with more than 100 fields will load slowly and exceed the recommended limit.

Section 3: DAX Query Optimization

Q17: B - The formula evaluates the PARALLELPERIOD calculation twice, which is inefficient.

Q18: B - Using variables (VAR) to store repeated calculations is the best optimization.

Q19: A, B, C, D - Variables improve performance, readability, debugging, and reduce complexity.

Q20: B - Always clear the data cache before re-running Performance Analyzer for valid comparisons.

Q21: B - KEEPFILTERS generally performs better for simple filtering conditions.

Q22: C - Variables are declared using the VAR keyword in DAX.

Section 4: Metadata & Model Analysis

Q23: B - Metadata analysis is performed in Power Query Editor.

Q24: A, B, C - Column quality, distribution, and profile help analyze metadata.

Q25: C - Column Distribution shows both distinct and unique values count.

Q26: B - Investigate errors and empty values to understand and fix data quality issues.

Q27: C - By default, column profiling analyzes the top 1000 rows.

Q28: B - Rename queries to make them more obvious and user-friendly.

Section 5: Relationships & Cardinality

Q29: B - Cardinality describes the uniqueness of values in columns and relationship direction.

Q30: B - Many-to-one (*:1) is the most common relationship type.

Q31: B - Lower cardinality leads to more optimized performance.

Q32: B - Both columns in a relationship must have the same data type.

Q33: B - Integer/Whole number data types perform better than Text.

Section 6: Auto Date/Time & Features

Q34: B - Auto date/time creates hidden calculated tables for date columns.

Q35: B - Disable Auto date/time when your data source already has a date dimension table.

Q36: A, B, D - You can configure it globally or for current file through File > Options > Data Load.

Section 7: Data Reduction & Aggregations

Q37: B - Using summary tables with aggregated data is an effective size reduction technique.

Q38: B - The disadvantage is potential loss of ability to drill into detailed data.

Q39: C - In Composite models, you can set each table to Import, DirectQuery, or Dual.

Q40: C - Reducing from 100,000 to 365 rows is approximately 99% reduction.

Q41: A - Right-click the table and select “Manage aggregations”.

Q42: A, B, C, D - All except “Working with small datasets only” - aggregations are for large datasets.

Q43: A, B, C, D, E - All these operations can be performed in Power Query aggregations.

Q44: B - Query performance improves because aggregated data is cached.

Section 8: DirectQuery Optimization

Q45: C - In DirectQuery, data stays in the source database and Power BI connects directly.

Q46: A, B, C, E - All except “Working with small, static datasets” which should use Import.

Q47: B - Performance depends heavily on the underlying data source.

Q48: C - Queries are sent when visuals are added or interacted with.

Q49: B - Optimize the source database first as it has the biggest impact.

Q50: A, B, C, E - All except “Import all data instead” which defeats DirectQuery purpose.

Q51: A, B, C - All are valid query reduction options.

Q52: B - Only the schema is loaded initially in DirectQuery mode.

Section 9: Advanced Scenarios

Q53: C - 20 visuals × 10 users = 200 queries minimum (could be more if visuals send multiple queries).

Q54: B - Composite model enables combining Import and DirectQuery modes.

Q55: C - Consistently high duration across tests indicates the DAX formula needs optimization.

Q56: C - Use Composite model with aggregated Import tables for summaries and DirectQuery for transaction details.

Q57: C - High cardinality means many unique values; it negatively affects performance.

Q58: B - Remove redundant unused columns to reduce model size and improve performance.

Q59: B - Rename to something meaningful and user-friendly.

Q60: C - DAX Studio provides the most comprehensive DAX query performance analysis.

Scoring Guide:

• 54-60 correct: Excellent! You have mastered Power BI performance optimization
• 48-53 correct: Very Good - You have strong understanding with minor gaps
• 42-47 correct: Good - Solid foundation, review weak areas
• 36-41 correct: Fair - Need more study on several topics
• Below 36: Need significant review of performance optimization concepts

Key Topics to Review Based on Wrong Answers:

• Questions 1-8: Fundamental optimization concepts
• Questions 9-16: Performance Analyzer usage
• Questions 17-22: DAX optimization and variables
• Questions 23-28: Metadata analysis
• Questions 29-33: Cardinality and relationships
• Questions 34-36: Auto date/time feature
• Questions 37-44: Aggregations and data reduction
• Questions 45-52: DirectQuery optimization
• Questions 53-60: Advanced scenarios and best practices