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've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.
Prerequisites
- Access level: You must be a member of a project with Basic access or higher.
- Permission: By default, project members have permission to query Analytics and create views.
- For more information about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.
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.
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 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.
Expand the Teams column
Choose the expand button.
Select the fields to flatten.
Table will now contain entity field(s).
Create a Relationship using "AreaSK"
After selecting Close & Apply, and returning to Power BI, follow these steps:
From the Modeling menus, 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
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.
Add
Team.TeamName
to Field.
For more information about working with slicers, see Slicers in Power BI.