Microsoft Certified: Power BI Data Analyst Associate — Practice Set 1

Question 1

You are a Power BI data analyst connecting to a large enterprise data warehouse. The data warehouse contains hundreds of millions of rows and your reports require near real-time data. Which Power BI storage mode should you choose for the semantic model?

Accepted Answer

DirectQuery sends queries directly to the underlying data source each time a visual is rendered, ensuring near real-time data without importing rows into Power BI. This is appropriate when the dataset is too large to import or when up-to-the-minute freshness is required.

Question 2

You are cleaning data in Power Query. A column contains product codes that should always be five characters long. You notice some values have leading spaces. Which Power Query transformation should you apply to the column?

Accepted Answer

The Trim transformation in Power Query removes leading and trailing whitespace from text values. This corrects product codes that have leading spaces without affecting the meaningful characters in the value.

Question 3

You are building a Power BI semantic model. The data model contains a sales fact table and a product dimension table. Your organization adds new products frequently. You want to avoid refreshing and reimporting all data when new products are added. Which Power Query feature should you use to manage this efficiently?

Accepted Answer

Incremental refresh allows Power BI to refresh only new or changed data rather than reimporting the entire table. Configuring incremental refresh on the product dimension table ensures new products are picked up efficiently without reloading historical data.

Question 4

Your organization receives monthly sales data as individual CSV files, one per month. You need to combine all files from a folder into a single table in Power BI. Which Power Query approach should you use?

Accepted Answer

The folder connector in Power Query allows you to connect to a folder and use the 'Combine & Transform' option to automatically combine all files with a consistent schema into a single table. This is the most efficient approach and automatically picks up new files added to the folder.

Question 5

You are profiling data in Power Query for a date column in a sales table. The Column Distribution view shows that 3% of rows have null values in the date column. Your reports use this column as a filter and null dates cause incorrect calculations. What should you do to resolve this?

Accepted Answer

Filtering out rows with null dates in Power Query is the recommended approach when null values represent missing or invalid records that should be excluded from analysis. This ensures that downstream calculations and filters work correctly on clean data.

Question 6

Your Power BI semantic model contains a sales fact table and a date dimension table. You need to enable time intelligence calculations such as year-to-date and same-period-last-year comparisons. What is a prerequisite for these calculations to work correctly?

Accepted Answer

DAX time intelligence functions require a properly marked date table that contains a contiguous range of dates without gaps. Marking the table as a date table in Power BI tells the engine to use it for time-based calculations like YTD, QTD, and same-period comparisons.

Question 7

You are building a Power BI semantic model. A customer dimension table contains rows for both individual customers and corporate accounts. Report users need to filter by either customer type. You need to create a measure that calculates the total revenue for the currently selected customer type. Which DAX function is essential for this measure?

Accepted Answer

CALCULATE is the fundamental DAX function for modifying filter context. It evaluates an expression — in this case a revenue sum — within a modified filter context, making it the essential building block for any measure that needs to respect or override slicer and filter selections.

Question 8

You are designing a Power BI semantic model for a retail company. The company sells products through both physical stores and an online channel. The same date dimension must be used for both a store opening date and a transaction date in the fact table. How should you model this in Power BI?

Accepted Answer

This is the role-playing dimension pattern in Power BI. When one dimension table relates to a fact table through multiple date columns, you create one active relationship and additional inactive relationships. The USERELATIONSHIP function activates an inactive relationship within a specific DAX measure calculation.

Question 9

You have a Power BI semantic model with a large fact table that has 500 million rows. Report users are experiencing slow query times. You analyze the model using Performance Analyzer. Which action is most likely to improve performance?

Accepted Answer

Removing unused columns reduces the in-memory footprint of the model, and applying aggregations reduces the granularity of data that needs to be scanned during queries. Both are recommended model optimization techniques identified in the PL-300 study guide under optimizing model performance.

Question 10

You are building a Power BI semantic model for a financial reporting solution. You need to create a measure that calculates cumulative sales from the beginning of the current fiscal year to the selected date. The fiscal year starts on July 1. Which DAX function should you use as the basis for this measure?

Accepted Answer

TOTALYTD accepts an optional year-end date parameter. For a fiscal year ending June 30, you specify '6/30' as the year-end date: TOTALYTD([Sales], Dates[Date], "6/30"). This correctly computes the cumulative total from July 1 of the fiscal year to the selected date.

Question 11

You are building a Power BI report to show the trend of monthly sales revenue over the past two years. Which visual type is most appropriate for this requirement?

Accepted Answer

A line chart is the standard visual for showing trends over time. Placing month on the X-axis and revenue on the Y-axis clearly communicates how revenue changes over the two-year period, making it easy for users to identify patterns and trends.

Question 12

You are designing a Power BI report for executives. The report has multiple pages covering different business areas. Users should be able to navigate between pages using clearly labelled buttons. How should you implement this navigation?

Accepted Answer

Power BI report buttons support a Page navigation action type that allows users to jump to a specific report page when clicked. This is the recommended approach for building custom report navigation menus that improve usability and storytelling.

Question 13

You are building a Power BI report that will be viewed on mobile devices. The desktop layout has multiple complex visuals. You need to ensure the report provides an optimal experience on smartphones. What should you do?

Accepted Answer

Power BI Desktop includes a mobile layout editor that allows you to design a separate optimized view for smartphones by selecting which visuals to include and how to arrange them for a portrait phone screen. This is built into the same report file.

Question 14

You have a Power BI report with a bar chart showing sales by product category. You want users to be able to click on a category bar and navigate to a detailed report page that filters data to that specific category. What feature should you configure?

Accepted Answer

Drillthrough in Power BI allows users to right-click (or click a designated button) on a data point in one visual and navigate to a detail page filtered to that data point. You configure drillthrough by adding the field (product category) to the drillthrough filter well on the destination page.

Question 15

You are a Power BI data analyst preparing a report for stakeholders with accessibility requirements. You need to ensure the report is accessible to users who rely on screen readers and keyboard navigation. Which two actions should you take? Choose 2.

Accepted Answer

Power BI includes accessibility features specifically for screen reader compatibility. Adding alt text to visuals provides descriptive text that screen readers can announce. Configuring tab order via the Selection pane ensures keyboard users can navigate through report elements in a logical sequence.

Question 16

You are analyzing a scatter chart in a Power BI report that plots customer spend versus number of purchases. You notice a cluster of customers that appears to behave differently from the main group. You want Power BI to automatically identify and label these distinct customer groups. Which Power BI feature should you use?

Accepted Answer

Power BI's clustering feature, available on scatter charts, uses machine learning algorithms to automatically identify and label distinct groups of data points. Each cluster is automatically colored and named, making it easy to identify behavioral segments without manual analysis.

Question 17

Your organization has a Power BI workspace containing reports used by the finance department. You need to give a new analyst read-only access to all content in the workspace without the ability to publish or modify reports. Which workspace role should you assign?

Accepted Answer

The Viewer workspace role provides read-only access to all content in a Power BI workspace. Viewers can read reports and dashboards but cannot publish, edit, or manage workspace content. This is the appropriate role for a read-only analyst.

Question 18

You are managing a Power BI semantic model that connects to an on-premises SQL Server database. The data must be refreshed every day at 6:00 AM. What must be in place to enable scheduled refresh for this semantic model?

Accepted Answer

Scheduled refresh of a Power BI Import-mode semantic model that connects to an on-premises SQL Server requires an on-premises data gateway. The gateway is configured with the data source credentials and acts as the bridge that allows the Power BI service to access the on-premises database during refresh.

Question 19

You are a Power BI data analyst responsible for a semantic model used by the sales team. Different regional managers should only see data for their own region. You need to implement this data restriction without creating separate reports for each region. What should you implement?

Accepted Answer

Row-level security (RLS) in Power BI allows you to define roles with DAX filter expressions that restrict which rows of data a user sees based on their identity. Regional managers assigned to their respective RLS roles will automatically see only their region's data in any report built on the semantic model.

Question 20

You have published a Power BI report to a workspace and want to make it available to a broad group of users in your organization as a curated app experience. Users should not need to be members of the workspace. What should you do?

Accepted Answer

Power BI apps provide a curated distribution mechanism. You publish an app from a workspace and configure its audience, which can include security groups or the entire organization. App recipients can access the content without being workspace members, and the app experience is separate from the workspace editing view.

Question 21

You are a Power BI data analyst. Your semantic model has an Orders fact table with an OrderDate column and a Products dimension table. Reports use a date slicer to filter orders. Business users report that selecting a single day returns no data even though orders exist for that date. You investigate and find the OrderDate column is stored as a DateTime data type including time components. What should you do to resolve this?

Accepted Answer

When a DateTime column with time components is related to a date table that contains only date values, the relationship join does not match correctly for date filtering. Converting the column to the Date data type in Power Query removes the time component before the data is loaded, ensuring the relationship works correctly with the date slicer.

Question 22

You are optimizing a Power BI semantic model that is experiencing slow report rendering. Using Performance Analyzer, you identify that two DAX measures have high execution times and two visuals have slow direct query times. Which two actions should you take to improve performance? Choose 2.

Accepted Answer

Using DAX variables avoids re-evaluating the same expression multiple times, which is a common cause of slow measure execution. Reducing data granularity in Power Query decreases the number of rows processed during visual queries, directly improving rendering times for both Import and DirectQuery visuals.

Question 23

You are building a Power BI report for a retail operations team. The team wants to see which product categories had unusually high or low sales compared to the expected range for a given week. The report should automatically highlight anomalies without requiring the analyst to define specific thresholds. Which Power BI capability should you use?

Accepted Answer

Power BI's anomaly detection feature, available in the Analytics pane for line charts, uses machine learning to automatically identify data points that fall outside the expected range. Anomalies are highlighted directly on the chart without requiring the analyst to pre-define thresholds.

Question 24

Your organization has classified a Power BI semantic model as Highly Confidential. You need to ensure that whenever a report built on this model is exported to Excel or PDF, the exported file automatically carries the same Highly Confidential classification label. What should you configure?

Accepted Answer

Microsoft Purview sensitivity labels applied to Power BI semantic models inherit to reports built on those models. When users export data to Excel or PDF, the sensitivity label is automatically applied to the exported file, ensuring consistent data protection across downstream artifacts.

Question 25

You are building a Power BI report page that uses a date slicer. When a user selects a region from a map visual, you need the date slicer to remain unaffected while all other visuals respond to the selection. How should you configure this?

Accepted Answer

Power BI's Edit Interactions feature allows you to control how selections in one visual affect other visuals. By selecting the map visual and setting its interaction with the date slicer to None, the slicer will not respond to selections made in the map, while other visuals continue to be filtered normally.

PL-300 — Microsoft Power BI Data Analyst