Add a team slicer to a Power BI report

Article
02/09/2022
6 minutes to read

Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020 | 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 have created your own query, make sure it returns "AreaSK" in either the $select or groupby() clauses.

Note

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

Prerequisites

You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your *Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

Sample queries

You'll need to add another query to your Power BI report. The query below returns the mapping between Teams and Area Paths.

Power BI query
OData query

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review 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

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v1.0/Areas?
        $filter=startswith(AreaPath,'{areapath}')
            &$select=AreaSK,AreaPath
            &$expand=Teams($select=TeamName)

Substitution strings

Each query contains the following strings that you must substitute 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

Returning Area Path fields, to use for mapping

&$expand=Teams($select=TeamName)

Returning the Team associated with the Area Path

Query filters

To determine available query filters, query the metadata as described in Explore the Analytics OData metadata. You can filter your queries using any of the NavigationPropertyBinding Path values listed under an EntitySet. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType. Each EntitySet corresponds to an EntityType.

For example, the EntitySet Name="WorkItemSnapshot" corresponds to the EntityType Name="WorkItemSnapshot". The OData metadata for EntitySet Name="WorkItemSnapshot" is as shown below for v4.0-preview. You can add filters based on any of the listed NavigationPropertyBinding Path values.

<EntitySet Name="WorkItemSnapshot" EntityType="Microsoft.VisualStudio.Services.Analytics.Model.WorkItemSnapshot">
  <NavigationPropertyBinding Path="Date" Target="Dates"/>
  <NavigationPropertyBinding Path="RevisedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="Teams" Target="Teams"/>
  <NavigationPropertyBinding Path="Processes" Target="Processes"/>
  <NavigationPropertyBinding Path="Project" Target="Projects"/>
  <NavigationPropertyBinding Path="Area" Target="Areas"/>
  <NavigationPropertyBinding Path="Iteration" Target="Iterations"/>
  <NavigationPropertyBinding Path="AssignedTo" Target="Users"/>
  <NavigationPropertyBinding Path="ChangedBy" Target="Users"/>
  <NavigationPropertyBinding Path="CreatedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ActivatedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ClosedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ResolvedBy" Target="Users"/>
  <NavigationPropertyBinding Path="Tags" Target="Tags"/>
  <NavigationPropertyBinding Path="ChangedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ClosedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="CreatedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ResolvedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="StateChangeOn" Target="Dates"/>
  <NavigationPropertyBinding Path="InProgressOn" Target="Dates"/>
  <NavigationPropertyBinding Path="CompletedOn" Target="Dates"/>
</EntitySet>

Power BI transforms

Expand the Teams column

Choose the expand button.
Select the fields to flatten.
Table will now contain entity field(s).

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

Right-click a column header and select Rename...
You also may want to rename the query from the default Query1, to something more meaningful.
Once done, choose Close & Apply to save the query and return to Power BI.

Create a Relationship using "AreaSK"

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

Select Modeling menu
Select Manage Relationships
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:

Add the Team Filter to an existing report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

The example assumes that no one renamed any columns.

To add a team filter to the Power BI report, follow these steps:

Select Visualization Slicer
Add the "Team.TeamName" field to Field

The example below shows the Open Bugs report with a Team filter added. Selecting a team in the slicer filters the results of the Open Bugs report. It will work for any report, as long as its query returns AreaSK.

Team Slicer report

Additional queries

You can use the following additional queries to create different but similar reports. You can use these queries with the steps defined above.

Filter by Teams, rather than Area Path

This query is the same as the one used above, except it filters by Team Name rather than Area Path.

Power BI query
OData query

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

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

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v1.0/Areas?
        $filter=(Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname})
            &$select=AreaSK,AreaPath
            &$expand=Teams($select=TeamName)

Add a team slicer to a Power BI report

Prerequisites

Sample queries

Substitution strings

Query breakdown

Query filters

Power BI transforms

Expand the Teams column

Rename fields and query, then Close & Apply

Create a Relationship using "AreaSK"

Add the Team Filter to an existing report

Additional queries

Filter by Teams, rather than Area Path

Full list of sample reports

Feedback

Add a team slicer to a Power BI report

Prerequisites

Sample queries

Substitution strings

Query breakdown

Query filters

Power BI transforms

Expand the Teams column

Rename fields and query, then Close & Apply

Create a Relationship using "AreaSK"

Add the Team Filter to an existing report

Additional queries

Filter by Teams, rather than Area Path

Full list of sample reports

Related articles

Feedback