Microsoft Power BI Data Analyst Practice Test

DAX orders active calculation groups by their Precedence property and executes them from the highest value to the lowest. Because Time Intelligence has a precedence of 15 and Currency has 5, the engine applies the Time Intelligence calculation item first, which rewrites the measure to return the year-to-date value. After that, the Currency item runs and only adjusts the format string. If the groups were executed in the opposite order, the numeric result could be wrong, because the Currency item would change the measure before the YTD filter was applied. Creation order, slicer interaction order, and whether an item changes value or format have no effect on this sequence-only the integer precedence setting controls it.

Connecting to a Power BI semantic model from external tools such as Excel requires Build permission on the model. The workspace Viewer role gives read-only access but does not grant Build. Therefore, you assign the auditors to the Viewer role to prevent them from changing or resharing content, and then explicitly grant Build permission on the semantic model so they can create their own Excel reports. Contributor and Member roles would let them edit or publish items, which is not permitted, and Viewer without Build would block the Excel connection.

Removing unused columns before they are loaded into the model frees VertiPaq from storing their values, which lowers the in-memory footprint and speeds column scans. Hiding a column, changing its default summarization, or toggling the Auto Date/Time option does not eliminate the column's data from the model, so they have little or no impact on memory consumption or query speed.

Privacy levels are defined per data source in the global permissions store. In Power BI Desktop you open File > Options and settings > Data source settings, switch to the Global permissions tab, select the CSV data source, choose Edit Permissions, and set the Privacy Level to Organizational. Editing the M code cannot set privacy, and changing settings after publishing or inside Power Query does not update the global permissions used by all reports.

A waterfall chart is designed to illustrate how sequential positive and negative values contribute to the running total of a measure. In this scenario, each department's revenue or expense change can be displayed as individual bars that cumulatively explain the overall month-to-month profit movement. Stacked column and area charts compare absolute values rather than incremental contributions. A funnel chart shows progressive reduction through stages and is not intended for time-based variance analysis.

Power BI relationships can only be defined on a single column. When source data relies on a composite key, the typical solution is to generate an equivalent single column in each table. Concatenating ProductCode and CountryCode (for example, ProductCode | CountryCode) produces identical values in both the dimension and fact tables, enabling a one-to-many relationship from Product to Sales. Bidirectional filters, multiple active relationships, or a key created only in the fact table would not satisfy the requirement.

After the parameter is defined, it will only surface in the Power BI service if it is actively referenced by at least one query. Replacing the literal database name in the Source step with the DatabaseName parameter (for example, Sql.Database("server.database.windows.net", DatabaseName)) binds the connection string to the parameter. When the dataset is later published, the parameter is exposed in the dataset settings, and changing its value causes Power BI to connect to the corresponding database. Simply changing privacy settings, suggested values, or requirement flags does not cause the data source to use the parameter and therefore will not redirect the connection.

The errors occur because Power Query tried to interpret the text 31/12/2024 by using the en-US culture (MM/dd/yyyy). Changing the column type again with the Using locale option forces the engine to parse the text by using the culture that matches the source data (for example, English (United Kingdom)). When the correct locale is supplied, the values are converted successfully to the Date type and the refresh error disappears. Converting the column to Text avoids the error but loses the required Date type. Replacing errors with null masks the problem and still discards valid values. Renaming the column has no influence on how the underlying text is converted, so it does not fix the DataFormat.Error.

Power BI allows only one active relationship between any two tables. If you link one Date dimension to three date columns, two of the relationships must remain inactive, and report authors would have to use USERELATIONSHIP or manually toggle relationships to analyze the other dates. The recommended approach is to create a separate Date table for each role. In Power Query you can create lightweight reference copies-or in DAX, calculated tables-named Order Date, Ship Date, and Return Date. Each copy then has its own active relationship to the corresponding column in the Sales fact table, so authors can use any of the role-specific tables directly in visuals and slicers without writing DAX. The extra tables duplicate the small date dimension, but because date tables have relatively few rows, the added storage and refresh cost is negligible.

Setting the page size template to 16:9 keeps the canvas in the same widescreen aspect ratio used by most modern monitors and the Power BI default layout, without locking the page to a single pixel count. Selecting the Fit to page view mode then scales the whole canvas so it always fits inside the viewer's browser window or iframe, preventing both vertical and horizontal scrollbars. A custom 1920 × 1080 page shown at Actual size would display correctly only on screens that match those exact dimensions; smaller screens would add scrollbars and larger screens would show unused space. Fit to width eliminates horizontal scrolling but can still require vertical scrolling, making the Letter and Cortana templates unsuitable. Therefore, the 16:9 page size paired with Fit to page is the only setting combination that consistently meets the requirement.

The CALCULATE function evaluates an expression in a modified filter context. The predicate Product[Category] = "Bikes" adds a filter that keeps only rows where Category is Bikes. The REMOVEFILTERS modifier then clears any existing filters on Product[Subcategory] and Product[Color], so slicers or visual filters on those two columns do not affect the result. Filters from related tables-and from other Product columns-are left intact, so the measure returns Bike sales in the current report context.

A formula that uses ALL(Product) is not appropriate here. ALL(Product) removes filters from every column in the Product table. Although the subsequent predicate re-applies the Bikes category filter, all other Product-level filters (such as Size or Brand) would also be removed, which is broader than the stated requirement.

The SUMX/FILTER expression operates entirely within the existing filter context, so it would still honor Subcategory and Color filters and therefore fails the requirement.

KEEPFILTERS preserves the current filters and intersects them with the Bikes filter, so Subcategory and Color would remain applied. This likewise does not satisfy the need to ignore those two columns.

The measure must divide the current filter-context value of SalesAmount by a total that ignores only the filters coming from the visual's ProductSubcategory axis while preserving every filter explicitly applied elsewhere (page, report, or slicer). CALCULATE with ALLSELECTED('Product') removes the visual's row/column filters but restores the outer filter context, so slicers on Product tables (for example, ProductCategory) and filters from other tables (Date, Geography, etc.) remain active. ALL('Product') removes all Product filters, including slicer filters, producing an incorrect denominator. ALLEXCEPT keeps the ProductSubcategory filter, so the percentage is always 100 %. REMOVEFILTERS() with no arguments clears all filters in the report, returning the grand total. Re-applying the selected subcategory in a FILTER expression again yields the numerator, not the total.

To enrich each sales transaction without changing the row count, you must perform a merge, not an append. A merge joins two tables horizontally by matching one or more key columns. Using Sales as the primary (left) table with a Left Outer join adds the selected columns from Customers to every matching Sales row and returns exactly the same 75 000 rows. An append stacks tables vertically and would either double the row count or add unmatched rows, while right-anti, right-outer, or other join types would remove or add rows depending on matches.

Drill-through pages automatically receive the filter context of any fields placed in their Drill-through well. All other filters and slicer selections from the source page are passed only when the Keep all filters option is enabled in the destination page's Drill-through section. Turning on this toggle preserves the Year slicer value that was active on the summary page. Adding the Year field to tooltips, enabling cross-report drill-through, or using Sync slicers does not pass the slicer selection during a drill-through action.

When a sensitivity label is applied to a semantic model, that label is automatically inherited by any new reports that use the model, provided those reports do not already have a label set. Because the author did not assign a different label, the new report automatically received the "Highly Confidential" label from the semantic model. Exported artifacts such as PowerPoint, PDF, or Excel files inherit the label of the report at the time of export. Therefore, the exported PowerPoint file is tagged with the same "Highly Confidential" label. The other options are incorrect because Power BI does not fall back to the tenant default label, remove labeling, or apply the least restrictive label in this scenario.

The correct answer is to use the Page navigator visual. The Page navigator automatically creates a button for each page in the report. It stays synchronized with the report pages, meaning it will automatically update if pages are added, removed, or renamed, which fulfills the key requirement efficiently.

• Creating individual buttons for each page is a valid but inefficient method. It requires manual configuration for each button and must be manually updated whenever a page is added, removed, or renamed.
• The Bookmark navigator visual is used to navigate between different saved states (bookmarks) within a report, not directly between pages. While a bookmark can be set for a page view, it's not the intended or most direct tool for page navigation and doesn't automatically sync with report pages in the same way.
• Configuring automatic page refresh is unrelated to user navigation. This feature is used to automatically refresh the data on a report page from a DirectQuery source at a set interval.

A workspace only runs on dedicated resources when it is explicitly assigned to a Power BI Premium capacity (or Premium Per User). This setting is found in the workspace's Advanced settings under the Premium section. Assigning Premium capacity moves the workspace off the shared pool and unlocks features such as paginated reports. Changing user roles, enabling Build permissions, or linking to OneDrive do not influence whether the workspace is backed by dedicated capacity.

The correct answer is to use the 'Split Column by Delimiter' transformation. This feature is specifically designed to parse text that is separated by a consistent character. By selecting the hyphen (-) as the delimiter, Power Query will automatically separate the 'OrderCode' column into three new columns for Region, ProductID, and Date. You can then keep the ProductID column and remove the others.

A 'Conditional Column' is incorrect because it is used for creating new values based on 'if-then-else' logical conditions, not for extracting substrings based on a delimiter.

'Replace Values' is inefficient for this scenario because the region and date codes are likely to change from row to row, which would require an impractical number of individual replacement steps.

'Unpivot Columns' is incorrect as it is a structural transformation used to convert data from a wide format to a long format; it is not used for text manipulation within a column.

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.

RLS is enforced only for users who have the Viewer workspace role. Members, Contributors, and Admins have edit permission on the semantic model and bypass RLS filters. Granting or removing Build permission alone will not change that behavior.

Changing the group's role to Viewer ensures that RLS is applied. Viewers cannot build new content unless they also have Build permission on the specific semantic model, so you must keep or grant that permission. After the change, the users retain the ability to analyze and build reports (because of Build permission) while their queries are filtered by the RLS roles.

Adding the group to an RLS role, removing Build permission, or moving the model to another workspace would not make RLS effective for Members who inherently bypass RLS.