Add a Team slicer to a Power BI report

Azure DevOps Services | 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.

Sample queries

You will need to add an additional query to your Power BI report. The query below returns the mapping between Teams and Area Paths.

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

Substitution strings

Each query contains the following strings that you must substitute with your values. Do not 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 partDescription
$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, AreaPathReturning Area Path fields, to use for mapping
&$expand=Teams($select=TeamName)Returning the Team associated with the Area Path

Power BI transforms

Expand the Teams column

  1. Choose the expand button.

    Power BI + OData - expanding team column

  2. Select the fields to flatten.

    Power BI + OData - expanding team column

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

    Power BI + OData - expanding team column

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

  1. Right-click a column header and select Rename...

    Power BI Rename Columns

  2. You also may want to rename the query from the default Query1, to something more meaningful.

    Power BI Rename Query

  3. Once done, choose Close & Apply to save the query and return to Power BI.

    Power BI Close & Apply

Create a Relationship using "AreaSK"

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

  1. Select Modeling menu

  2. Select Manage Relationships

  3. Create a relationship between your Report query and the Teams query. It is likely that Power BI will auto-detect and create the relationship for you. Here is an example of a relationship between the query in the Open Bugs report and the Teams query:

    Power BI + OData - expanding an entity column

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.

Power BI + OData - expanding an entity column

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

  1. Select Visualization Slicer
  2. 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. This will work for any report, as long as its query returns AreaSK.

Team Slicer report

Additional queries

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.

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

Full list of sample reports