To provide summary statistics regarding data completeness (null counts) and value distributions (min, max, average), you should use the Table.Profile function in Power Query. First, you initiate the process by creating a blank query to house the custom script. Next, you input the M formula =Table.Profile(#"SalesDetail"), which generates a new table containing the required statistical profile for every column in the SalesDetail table. Finally, to make these insights accessible to end-users as requested, you create a visual (such as a standard table or matrix) on the report canvas using the output of the profiling query.

Microsoft Learn: Table.Profile. "Returns a description of the columns of the table... The resulting table includes the following columns: Column, Min, Max, Average, StandardDeviation, Count, NullCount, DistinctCount."

Source: https://learn.microsoft.com/en-us/powerquery-m/table-profile

Microsoft Learn: Profile data in Power BI. Discusses profiling capabilities and the underlying M logic used to assess column quality and distribution.

Source: https://learn.microsoft.com/en-us/power-bi/transform-model/data-profiling-power-bi

A line chart is the most effective visualization for showing the trend of a continuous value, such as account balances, over a period of time (quarters). Filtering to a specific category like "long-term liabilities" allows for focused analysis of how that component of the balance sheet is changing.

A ribbon chart is uniquely suited to show not only the value of different categories over time but also how their rank changes. This is ideal for a balance sheet, as it can clearly illustrate which accounts are growing or shrinking in proportion to others from one quarter to the next, providing insight into the changing financial structure.

B: A clustered column chart showing all account categories without filters would be visually cluttered and difficult to interpret, obscuring key trends.

C: While a column chart can show trends, a line chart (Option A) is generally the preferred and clearer visualization for continuous time-series data.

D: A pie chart is poor for time-series analysis and becomes unreadable with the numerous categories typical of a balance sheet, violating visualization best practices.

1. Microsoft Power BI Documentation

"Line charts in Power BI": "Line charts are a great choice... to visualize data over a long period of time." This supports using a line chart to show balances by quarter (Option A).

2. Microsoft Power BI Documentation

"Create ribbon charts in Power BI": "Ribbon charts are best for showing a category's rank change. The ribbon for the category with the highest rank (value) is always displayed on top of the other ribbons." This directly supports using a ribbon chart to analyze the changing composition and ranking of accounts over time (Option E).

3. Microsoft Power BI Documentation

"Visualization types in Power BI": This document describes the appropriate use cases for various visuals. It notes that pie charts are best with fewer than eight categories and that clustered column charts are for comparing multiple measures

which can become cluttered without filtering (supporting the rejection of Options D and B).

The proposed solution is incorrect. For semantic models using Import storage mode, the on-premises data gateway is only used during a scheduled or on-demand data refresh to pull data from the on-premises source into the Power BI service.

The dashboard cache update frequency controls how often dashboard tiles query the semantic model that is already hosted within the Power BI service. This process does not interact with the on-premises data source and therefore does not use the data gateway. To reduce data sent through the gateway, one would need to reduce the frequency of the semantic model refresh itself or reduce the volume of data being imported (e.g., by filtering rows or columns in Power Query).

A. Yes: This is incorrect because changing the dashboard cache update frequency does not affect the data transfer through the gateway for an Import mode model.

1. Microsoft Learn. (2024). Data refresh in Power BI.

Section: Semantic model refresh

Content: "When you refresh data

Power BI must query the underlying data sources... For data sources that reside on-premises in your organization's network

an on-premises data gateway is required to connect to them." This confirms the gateway's role is during the semantic model refresh.

2. Microsoft Learn. (2024). Data refresh in Power BI.

Section: Dashboard tile refresh

Content: "A dashboard tile refresh updates the cache for tile visuals on the dashboard

once data changes. This process happens about every hour... Tile refreshes don't trigger a semantic model refresh." This distinguishes dashboard updates from the semantic model refresh process that uses the gateway.

3. Microsoft Learn. (2023). Configure scheduled refresh.

Section: Gateway connection

Content: "If your semantic model connects to on-premises data sources

you must install and configure a data gateway to enable a connection to your data." This explicitly links the gateway's function to connecting to the on-premises source for the semantic model

which occurs during refresh.

The first step (#"Var1") is designed to filter out unnecessary columns. By using Table.RemovedColumns (representing the standard Table.RemoveColumns function), the query discards the Alternate Key and non-English names, ensuring that the EnglishProductCategoryName remains available for the subsequent step. The second step (#"Var2") uses a list of lists format ({{Old, New}}), which is the required syntax for Table.RenameColumns. This step renames the remaining English column to "Category" and the related table column to "Subcategory" to match the desired user-friendly model.

Microsoft Learn: Table.RemoveColumns. "Removes the specified columns from the table... Table.RemoveColumns(table as table, columns as any, optional missingField as nullable number) as table."

Source: https://learn.microsoft.com/en-us/powerquery-m/table-removecolumns

Microsoft Learn: Table.RenameColumns. "Returns a table with the columns renamed as specified... Table.RenameColumns(table as table, renames as list, ...)."

Source: https://learn.microsoft.com/en-us/powerquery-m/table-renamecolumns

The Azure DevOps (Boards only) connector in Power BI is specifically designed to connect to Azure DevOps Services (the cloud version) and leverage Analytics views. Analytics views provide a simplified way to specify and apply server-side filters to the data before it is loaded into Power BI. This directly meets the requirements to use Analytics views and filter data from the cloud, making it the most appropriate and efficient choice for creating a bug tracking dashboard.

A. OData queries: This is a method, not a specific connector. While the underlying connection uses OData, you would use a connector like 'OData feed' or the dedicated 'Azure DevOps' connector to execute them.

C. Azure DevOps Server (Boards only): This connector is for the on-premises version of Azure DevOps (formerly TFS), not the cloud service specified in the scenario.

D. OData feed: This is a generic connector. While Azure DevOps Analytics exposes an OData endpoint, the dedicated Azure DevOps (Boards only) connector is the recommended method as it simplifies authentication and is built to work directly with Analytics views.

1. Microsoft Learn. (2023). Connect to Analytics with the Power BI Data Connector.

Section: "Prerequisites" and "Connect with the Power BI Data Connector".

Content: This document explicitly states

"The Power BI Data Connector uses Analytics views." It then details the process of connecting to Azure DevOps from Power BI Desktop

showing the selection of the "Azure DevOps (Boards only)" connector to access and use pre-defined or custom Analytics views.

2. Microsoft Learn. (2023). About Power BI integration.

Section: "Supported connection options".

Content: This page compares the different methods for connecting Power BI to Azure DevOps. It highlights that the "Power BI Connector" is the preferred option for users with few or no T-SQL skills because it supports "Analytics views" which handle server-side filtering and data aggregation.

3. Microsoft Learn. (2023). What are Analytics views?.

Section: "Analytics view".

Content: This document defines Analytics views as a feature in Azure DevOps that "provides a simplified way to specify the filter criteria for a Power BI report based on Analytics data." This confirms that using Analytics views is the correct approach for server-side filtering.

To embed visualizations in a public website using the "Publish to web" feature, specific conditions must be met. The content must be a report, not a dashboard, and it must reside in an app workspace, not in "My Workspace".

Visual2 is a report in an app workspace, which is a valid configuration for public embedding.

Visual4 is also a report in an app workspace, created by a user with a Premium Per User (PPU) license, which also supports this feature.

Therefore, only Visual2 and Visual4 can be embedded into a public website.

A. Visual1: This report is in "My Workspace." The "Publish to web" feature is not available for content in "My Workspace."

C. Visual3: This is a dashboard and is in "My Workspace." "Publish to web" is not supported for dashboards or for content in "My Workspace."

E. Visual5: This is a dashboard. The "Publish to web" feature is only available for reports, not dashboards.

1. Microsoft Power BI Documentation

"Publish to web from Power BI":

Section: "How to use Publish to web" and "Limitations".

Content: The documentation explicitly states

"You can't publish to web from My Workspace." It also clarifies

"You can publish reports to the web. You can't publish dashboards or paginated reports to the web." This directly supports why Visuals 1

3

and 5 are incorrect and why Visuals 2 and 4 are correct.

2. Microsoft Learn

"Create and manage workspaces in Power BI":

Module: "Create and manage workspaces in Power BI"

Unit: "Distribute a report or dashboard"

Content: This module distinguishes between sharing methods. It details that "Publish to web" is a specific feature for reports in app workspaces intended for a public audience

contrasting it with content in "My Workspace

" which is for personal use.

3. Microsoft Power BI Documentation

"Power BI Premium Per User":

Section: "Feature comparison" table.

Content: The feature comparison table confirms that both Power BI Pro and Premium Per User (PPU) licenses have the capability for "Publishing to web (public)." This validates that the license type for Visual4 is sufficient.

The objective is to create a measure that calculates the number of deliveries based on the destination location. The provided data model shows an active relationship between Dim Location and Fact Deliveries on the origin location, and an inactive relationship on the destination location.

To perform a calculation using an inactive relationship, you must use the CALCULATE function to modify the filter context. The USERELATIONSHIP function is then used as a filter argument within CALCULATE to activate the specified inactive relationship for the duration of that specific calculation.

Therefore, the correct DAX expression is:

Deliveries by Destination = CALCULATE([Deliveries], USERELATIONSHIP('Dim Location'[Location ID], 'Fact Deliveries'[Destination Location ID]))

Microsoft Documentation, Data Analysis Expressions (DAX) Reference:

CALCULATE function: The documentation states, "Evaluates an expression in a modified filter context." This is the primary function needed to change how the [Deliveries] measure is calculated.

Source: Microsoft Docs, "CALCULATE function (DAX) - DAX".

USERELATIONSHIP function: The official documentation specifies, "USERELATIONSHIP specifies the relationship to be used in a specific calculation... It is used within functions that take a filter as an argument, such as the CALCULATE... functions." This confirms its role as the modifier inside CALCULATE to activate the inactive path.

Source: Microsoft Docs, "USERELATIONSHIP function (DAX) - DAX".

Russo, M., & Ferrari, A. (2020). Definitive Guide to DAX: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel (2nd ed.). Microsoft Press.

Chapter 5, "Working with multiple tables": This chapter details the concept of active and inactive relationships. It explains that only one relationship between two tables can be active at a time and that USERELATIONSHIP must be used within a CALCULATE function to enable calculations over an inactive relationship. (Specific patterns are discussed in the "Multiple relationships between tables" section).

To meet the reporting requirement of counting the total number of sales, a measure is the correct DAX object to create. Measures are designed for aggregations and are calculated dynamically at query time based on the filter context of the report (e.g., slicers, visuals). The COUNTROWS(Sales) function is the most efficient and direct method to count the total number of rows in the 'Sales' table, where each row represents a distinct sales transaction. This approach provides an accurate, context-aware total for reporting visuals.

A. Summing an identifier column (salesid) is a meaningless calculation that does not represent a valid business metric.

B. A calculated column is inefficient for this purpose as it would store the same total count in every row, unnecessarily increasing model size.

D. This option combines two errors: performing a meaningless aggregation (SUM on an ID) and using an inefficient calculated column for a report-level aggregation.

1. Microsoft Learn. (2023). Use DAX in Power BI Desktop. In the "Tutorial: Create your own measures in Power BI Desktop" section

it is explained that measures are used for creating dynamic aggregation calculations. The example for counting order lines uses COUNTROWS('Sales')

directly supporting the chosen answer.

Reference: Module "Model data in Power BI"

Unit "Create DAX calculations in Power BI Desktop"

Section "Tutorial: Create your own measures in Power BI Desktop".

2. Microsoft Docs. (2023). COUNTROWS function (DAX). The official documentation states

"This function counts the number of rows in the specified table

or in a table defined by an expression." This confirms it is the correct function for counting transactions.

Reference: Data Analysis Expressions (DAX) reference

COUNTROWS function documentation.

3. Microsoft Learn. (2023). Calculated columns vs. measures. This documentation explicitly contrasts the two

stating

"Measures are used when you display resulting values in a report... that reflect user selections." This confirms a measure is the correct choice over a calculated column for this reporting scenario.

Reference: Module "Model data in Power BI"

Unit "Introduction to creating DAX calculations in Power BI Desktop"

Section "Calculated columns vs. measures".

Visual 1: Treemap A treemap is the ideal choice for displaying the distribution of hierarchical data. It represents data as a set of nested rectangles, where the size of each rectangle is proportional to a specific measure. This structure naturally visualizes the composition of the whole and the contribution of each hierarchical level, making it perfect for understanding distributions within a hierarchy.

Visual 2: Slicer A slicer is the standard Power BI visual used for filtering data on a report page. It allows users to select one or more values to filter other visuals. Critically, the slicer visual includes a built-in search functionality, which directly meets the requirement to "search and then filter by values."

Microsoft Learn, Power BI Documentation, "Treemaps in Power BI."

This document states, "Treemaps are charts of colored rectangles, with size representing value. They can be hierarchical, with rectangles nested within the main rectangles." This confirms their primary use for visualizing hierarchical data distribution.

Microsoft Learn, Power BI Documentation, "Slicers in Power BI."

The official documentation describes slicers as a way to filter other visuals on the page. In the section "Format slicers," it details the search option: "For some slicer types, you can format a search box... The search box is a great way to find items in a long list." This directly corresponds to the requirement for Visual 2.

The DAX functions RELATED and RELATEDTABLE are used to traverse relationships between tables to access data. Frequent use of these functions can indicate a highly normalized model, which may lead to complex DAX and potential performance issues. Merging tables in Power Query is a denormalization technique. By combining related tables into a single, wider table, the required columns become directly accessible within the same table context. This eliminates the need to use RELATED or RELATEDTABLE to navigate relationships, thereby simplifying the DAX expressions and often improving model performance.

B. Hiding columns only affects the report view for end-users. It does not change the underlying model structure or the need for DAX to traverse relationships for calculations.

C. Splitting the model into multiple models is a scalability strategy for very large datasets. It increases overall complexity and does not simplify the DAX within each model.

D. Transposing is a specific data shaping operation that swaps rows and columns. It is irrelevant to simplifying DAX expressions that depend on table relationships.

1. Microsoft Learn: Understand star schema and the importance for Power BI. In the section "Denormalize snowflake dimensions

" the documentation states

"A snowflake dimension should be denormalized into a single table... You can use Power Query to consolidate the columns... It's a process of denormalization." This directly supports merging tables to simplify the model.

2. Microsoft Learn: Merge queries (Power Query). This document describes the technical process of merging

which is the implementation of the recommended solution. It states

"a merge query joins two existing tables together based on matching values from one or multiple columns."

3. Microsoft Learn: DAX function reference: RELATED. The documentation for the RELATED function specifies that it "requires that a relationship exists... When the function follows a relationship... it examines the value... and fetches the value... from the specified column in the related table." This confirms that by merging

the need for the function is removed as the data is no longer in a "related table."