Microsoft Power BI Data Analyst Practice Test

Create a Reference of the FactSales query and then add a Group By step that groups on TerritoryID and defines a Sum aggregation for SalesAmount and a Minimum aggregation for OrderDate. Because the referenced query inherits the existing steps of FactSales, you avoid duplicating the transformation logic, and-assuming the source supports query folding-the entire Group By folds back to the data source so only the aggregated rows are returned. This keeps memory consumption low while leaving the incremental-refresh policy on FactSales untouched. A Duplicate would copy every step of FactSales into an independent query, forcing you to maintain two sets of logic and still triggering a second evaluation of the 25-million-row source.

The correct answer is to use the 'Edit interactions' feature. To configure this, you first select the source visual that will initiate the action (the pie chart). Then, from the 'Format' ribbon, you select 'Edit interactions'. This action reveals small icons on the other visuals on the page. By selecting the 'Filter' icon on the target visual (the column chart), you change its behavior from the default of highlighting to filtering. 'Sync slicers' is used to synchronize the state of slicer visuals, not interactions between charts. Grouping visuals is for layout and layering purposes and does not define their filtering behavior. The 'Filters' pane is used to apply persistent filters to visuals, pages, or the entire report, not to manage the interactive filtering between two visuals based on user selection.

Granting Build permission on the semantic model allows a user with only Viewer access to discover the model and create new reports or other content that uses it. Build does not grant the ability to modify (Write) the model or to share it with additional users (Reshare). Read permission alone lets Ann view existing reports but not build new ones, while Write or Reshare permissions would exceed the requirements by allowing edits or further sharing. Therefore, Build is the minimal, correct permission.

Power BI permits only one active relationship between any two tables. To let visuals filter the Sales fact table by both OrderDateKey and ShipDateKey at the same time, each role-playing instance of the date dimension must have its own active relationship. The recommended pattern is therefore to duplicate the Date table (for example, create a Ship Date table as a calculated or reference table) and relate each copy to the appropriate date key in Sales as a one-to-many, single-direction, active relationship. This design keeps the model simple for report authors, works with row-level security, and avoids the need for USERELATIONSHIP calls. Using inactive relationships would force every measure to enable the relationship manually, while many-to-many or bi-directional settings do not resolve the single-active-relationship constraint.

The correct answer is to create a custom theme using a JSON file and import it. This is the most efficient and scalable method for ensuring brand consistency across multiple Power BI reports. A theme JSON file can define colors, fonts, visual styles, and page properties. This file can then be easily shared and imported into any Power BI report, new or existing, by selecting "Browse for themes" under the View tab.

• Using the Format Painter is incorrect because it is a manual, visual-by-visual process that is inefficient and prone to errors when applied across many reports. It does not create a reusable theme asset.
• Creating a Power BI Template (.pbit) is a valid way to start new reports with the correct theme, but it is not an efficient way to update existing reports. Applying a theme from a JSON file updates existing reports without requiring a rebuild.
• Power BI cannot directly import a PowerPoint template file (.potx) as a report theme. Themes for Power BI must be in a specific JSON format.

Publishing a .pbix from Power BI Desktop to the same workspace and with the same semantic model name prompts you to either create new items or replace the existing ones. Choosing 'Replace' overwrites the semantic model (and any reports with identical names) but preserves the semantic model's ID. This ensures that existing reports that rely on that semantic model continue to work without needing to be rebound. Deleting the semantic model first would break report connections. Importing the file through the service's 'Get data' option would create a second, separate semantic model. Using the XMLA endpoint is not possible in shared capacity and would be an overly complex solution requiring manual report rebinding.

Selecting MonthName in Data view and applying Sort by Column → MonthNumber stores a numeric sort order in the model. Any visual that uses MonthName automatically follows that order whenever the report loads or the user resets the view. No other setting establishes a persistent chronological sequence. The Format pane has no Chronological sort option, so that choice cannot work. Interactive sorting by the Total Sales measure only rearranges the bars by value, not by calendar order, and the user can easily override it. Sorting rows in Power Query affects the preview but is not preserved in the Vertipaq column store, so visuals still default to alphabetical order unless a Sort-by-Column relationship exists.

Creating a reference of the Sales_Transactions query makes the new query start from the final results of Sales_Transactions without copying any existing steps. Because it points to the original query, it inherits any later edits in Sales_Transactions and does not duplicate the data in memory; both queries can still add their own additional transformations. A duplicate, by contrast, copies all steps into a separate query that is independent of future changes and stores a separate result set, consuming additional memory. Disabling load on one query or merging queries does not satisfy the requirement to create a separate table that stays in sync while conserving resources.

A Power BI Pro-backed workspace supports a maximum of eight scheduled refreshes per day. An hourly schedule that runs from 06:00 through 22:00 requires 17 refreshes, which exceeds that limit. Moving the workspace to a Power BI Premium capacity or assigning Premium Per User will raise the limit to 48 refreshes per day, allowing the hourly schedule. Enabling incremental refresh, installing an on-premises gateway, or switching to DirectQuery does not remove the eight-per-day cap for a Pro workspace.

Power BI service can connect natively to cloud data sources without using a gateway. Azure SQL Database and files stored in SharePoint Online are cloud-hosted, so the service reaches them directly when it performs DirectQuery or a refresh. Any data source that is located inside a private network-such as an on-premises SQL Server instance-cannot be contacted directly by the Power BI service. An on-premises data gateway (standard or personal) is required to provide a secure outbound connection from the private network to the Power BI service for that source. Therefore, only the on-premises SQL Server database needs to be added to a gateway; the other two sources do not.

The correct formula is CALCULATE(SUM('Sales'[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date])). Here's a breakdown of why:

• The SAMEPERIODLASTYEAR function returns a table of dates shifted one year back from the dates in the current filter context. When a visual is filtered to March 2025, this function returns all dates for March 2024.
• The CALCULATE function then evaluates the SUM('Sales'[SalesAmount]) expression using this new table of dates as its filter context, correctly calculating the sales for the same period in the previous year.

Incorrect Answer Explanations:

• CALCULATE(SUM('Sales'[SalesAmount]), PREVIOUSYEAR('Date'[Date])) is incorrect because the PREVIOUSYEAR function returns all the dates from the entire previous year, ignoring the specific period (like a month or quarter) in the current filter context. This would return the total sales for all of 2024, not just March 2024.
• SUM('Sales'[SalesAmount]) - CALCULATE(SUM('Sales'[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date])) is incorrect because it calculates the year-over-year change or variance in sales, not the sales total from the prior year period itself.
• TOTALYTD(SUM('Sales'[SalesAmount]), 'Date'[Date]) is incorrect because TOTALYTD calculates the cumulative sum from the beginning of the year to the current date (Year-To-Date), which is a different calculation than comparing to the same period in the previous year.

Certification is a tenant-level feature. In the Power BI admin portal a tenant setting called Item certification must be enabled, and one or more security groups must be specified as authorized certifiers. A user can certify content only when they belong to one of those groups; workspace role alone does not grant this right, and certification does not depend on promotion status or Premium capacity. Once the administrator adds you to an authorized security group (or another member of that group certifies the dataset), the Certified option in the dataset's endorsement settings becomes available.

The correct answer is to remove duplicate rows and add an index column. In a star schema, the key column on the 'one' side of a one-to-many relationship must contain unique values. The best practice to resolve duplicate values in a dimension table is to remove the duplicate rows based on the business key (like 'ProductSKU') and then add an index column in Power Query. This new column acts as a surrogate key, providing a unique integer-based identifier for each row, which is optimal for model performance and relationship integrity.

Creating a composite key is a possible but less optimal solution, as joins on multiple columns or text columns are less performant than on a single integer key. Establishing a many-to-many relationship is not ideal as it is a workaround for the data quality issue, not a solution, and can lead to ambiguity and performance problems. Filtering rows from the 'Sales' fact table is incorrect as it would result in the loss of valid data and lead to inaccurate reports.

Ribbon charts are designed to display how the ranking of categories changes across a continuous axis such as time. The width and layering of each ribbon automatically represent the measure's rank in every period, making upward or downward movements easy to see. A stacked area chart shows cumulative totals rather than rank position, a clustered column chart compares absolute values but does not indicate rank shifts, and a waterfall chart highlights incremental changes to a total, not ordinal movement between categories.

Enabling 'Display' only captures the visibility of objects that were hidden or shown in the Selection pane, so the slicer panel can be revealed or concealed. Because 'Data' is cleared, the bookmark does not store slicer or filter values; whatever the reader has currently selected remains intact. Clearing 'Current page' ensures that selecting the bookmark applies its properties on the page the user is currently viewing and does not force navigation to the page where the bookmark was originally created. Choosing any option set that includes 'Data' would overwrite slicer states, and including 'Current page' could redirect the user.

A waterfall chart is designed to display a running total as values are added or subtracted. It starts with a baseline value, shows individual positive or negative contributions, and finishes with the ending value-exactly matching the requirement to move from last quarter's total, through each category's change, to the current quarter total. Stacked area and scatter charts emphasize trends or relationships but do not show additive contributions to a final result in a single bar. A gauge visual shows progress toward a target rather than individual category impacts.

Calculation items should be written generically so they can operate on any measure that the user has placed in a visual. The DAX function SELECTEDMEASURE() returns the result of the measure that is currently being evaluated, allowing you to reuse the same expression across multiple measures. Other functions such as SELECTEDVALUE(), CALCULATE(), or a fictitious CURRENTMEASURE() either retrieve a single column value, change filter context, or simply do not exist, so they cannot serve this purpose.

CLOSINGBALANCEMONTH is designed for semi-additive scenarios in which you must return the expression evaluated on the last date of the month within the current context. Unlike TOTALMTD, which sums values across all days, or LASTDATE combined with CALCULATE, which can fail when dates have no data, CLOSINGBALANCEMONTH automatically searches back to the last non-blank date. SUMX simply aggregates rows and provides no logic for end-of-period selection.

The correct method to dynamically change the color of columns based on their value is to use conditional formatting. This feature is located in the 'Format your visual' pane. By navigating to the 'Columns' section, you can access the conditional formatting (fx) dialog for the color property. Here, you can define one or more rules, such as setting the column color to green if its value is greater than 500,000. Adding a constant line from the 'Analytics' pane only adds a line to the chart; it does not change the column colors. Creating a DAX measure and adding it to the legend would alter the structure of the visual by creating new series rather than formatting the existing ones. Manually selecting columns to change their color is not a feature in Power BI for a single data series; formatting applies to the entire series or is controlled by rules.

Data-driven alerts cannot be configured on tiles that are based on a live connection to an on-premises Analysis Services model. Although pinning the card to a dashboard succeeds, the alert feature only supports tiles whose data is cached in Power BI (such as Import mode or DirectQuery datasets). Workspace roles, the Power BI mobile app, and the refresh schedule do not affect whether the alert command appears. Therefore, using a live connection to Analysis Services is the condition that prevents the user from creating the alert.