sample-boards-teamslicer.md

title	titleSuffix	description	ms.subservice	ms.custom	ms.author	author	ms.topic	monikerRange	ms.date
Add a Team slicer to a Power BI report	Azure DevOps	Learn how to add a team slicer to a Power BI report.	azure-devops-analytics	powerbisample, engagement-fy23	chcomley	chcomley	sample	>= azure-devops-2019	12/16/2022

Add a team slicer to a Power BI report

[!INCLUDE version-gt-eq-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.

[!INCLUDE temp]

[!INCLUDE prerequisites-simple]

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.

Power BI query

[!INCLUDE temp]

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

OData query

[!INCLUDE temp]

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

---

Substitution strings and query breakdown

[!INCLUDE temp]

• {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.

:::row::: :::column span="1"::: Query part :::column-end::: :::column span="1"::: Description :::column-end::: :::row-end:::

:::row::: :::column span="1"::: $filter=startswith(AreaPath, '{areapath}') :::column-end::: :::column span="1"::: Return all teams mapped to an Area Path at or under the specified {areapath}. To include all teams in a project, omit this statement. :::column-end::: :::row-end::: :::row::: :::column span="1"::: &$select=AreaSK, AreaPath :::column-end::: :::column span="1"::: Return Area Path fields, to use for mapping. :::column-end::: :::row-end::: :::row::: :::column span="1"::: &$expand=Teams($select=TeamName) :::column-end::: :::column span="1"::: Return the team associated with the Area Path. :::column-end::: :::row-end:::

[!INCLUDE temp]

Expand the Teams column

1. Choose the expand button.

   [!div class="mx-imgBorder"]

2. Select the fields to flatten.

   [!div class="mx-imgBorder"]

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

   [!div class="mx-imgBorder"]

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:

   [!div class="mx-imgBorder"]

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.

   :::image type="content" source="media/reports-boards/team-slicer-visualizations.png" alt-text="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.

Related articles

[!INCLUDE temp]