Редактиране

Споделяне чрез

Add a team slicer to a Power BI report

  • Статия

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Often Power BI reports include data from multiple teams for aggregation and comparison. This article shows you how to add a Team slicer to an existing Power BI report. The Team slicer allows you to filter the report data by Teams, rather than Area Path.

Important

The Team filter requires that the "AreaSK" field is included in the query used by the report you want to filter. All of the queries provided in the sample reports already include "AreaSK". If you've created your own query, make sure it returns "AreaSK" in either the $select or groupby() clauses.

Note

This article assumes you read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

Prerequisites

Sample queries

To use a team slicer in a Power BI report, you'll need to add another query to your Power BI report. The following query returns the mapping between teams and Area Paths.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v1.0/Areas?"
        &"$filter=startswith(AreaPath,'{areapath}') "
            &"&$select=AreaSK,AreaPath "
            &"&$expand=Teams($select=TeamName) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Substitution strings and query breakdown

Substitute the following strings with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

  • {organization} - Your organization name
  • {project} - Your team project name, or omit "/{project}" entirely, for a cross-project query
  • {areapath} - Your Area Path. Example format: Project\Level1\Level2.

Query breakdown

The following table describes each part of the query.

Query part

Description

$filter=startswith(AreaPath, '{areapath}')

Return all teams mapped to an Area Path at or under the specified {areapath}. To include all teams in a project, omit this statement.

&$select=AreaSK, AreaPath

Return Area Path fields, to use for mapping.

&$expand=Teams($select=TeamName)

Return the team associated with the Area Path.

(Optional) Rename query

You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.

Screenshot of Power BI query menu options, rename query.

Expand the Teams column

  1. Choose the expand button.

    Screenshot showing choose the expand button.

  2. Select the fields to flatten.

    Screenshot that shows how ot select the fields to flatten.

  3. Table will now contain entity field(s).

    Screenshot of table that now contains entity fields.

Create a Relationship using "AreaSK"

After selecting Close & Apply, and returning to Power BI, follow these steps:

  1. From the Modeling menus, select Manage Relationships.

  2. Create a relationship between your Report query and the Teams query. It's likely that Power BI will autodetect and create the relationship for you. Here's an example of a relationship between the query in the Open Bugs report and the Teams query:

    Screenshot that shows example of relationship between queries.

Add the Team Filter to an existing report

  1. On the page you want to add the team filter to, make sure no charts are selected, and then choose Slicer from the Visualizations pane.

    Screenshot of Power BI Visualizations and Fields selections for team slicer.

  2. Add Team.TeamName to Field.

For more information about working with slicers, see Slicers in Power BI.