Create a Power BI report filtered by team using a custom Analytics view

Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020 | Azure DevOps Server 2019

Analytics views support field criteria to filter work items based on teams. However, there's no team-specific field available to support filtering a Power BI report. While each work item is associated with a specific area path, area paths can be associated with more than one team. Because of this one-to-many association, Analytics doesn't provide a team-specific field.

However, you can still filter on a team using the steps provided in this article. The general process introduces a mapping table between the Analytics view and the teams entity.

Note

In a similar manner, limitations exist in determining the board-specific column of a work item within an Analytics view. However, the guidance provided in this article won't work for board locations due to the dependency on the selected historical data in the view.

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.

Add the AreaSK field to your Analytics view

The default fields included within the default Analytics views don't include the fields necessary to create the relationship mapping in Power BI. Use the following steps to introduce the mapping table to your model and build the necessary relationships to support filtering on teams.

  1. Edit your Analytics view.
  2. In the Field tab, add the AreaSK field.
  3. Save the updated view.
  4. Load the Power BI pbix file associated with your Analytics view in Power BI Desktop.
  5. Refresh the view and verify that the AreaSK field appears as expected.

Add tables for teams

The next step is to add the Teams entity to the Power BI data model and generate the necessary mapping table to create the relationships in Power BI. This process requires that you add three queries through the Power Query Editor.

  1. Load the Power BI pbix file associated with your view in Power BI Desktop.

  2. Choose Get Data.

  3. Select the Blank Query option.

    Blank Query

  4. Open Advanced Editor.

    Advanced Editor

  5. Add the following query code, replacing organization information and team names to match your Analytics view.

    let
        #"Get table" = VSTS.Feed("https://{OrganizationName}.analytics.visualstudio.com/_odata/v1.0/Teams?$select=TeamName,TeamSK&$filter=TeamName eq '{TeamName1}' or TeamName eq '{TeamName2}"),
        #"Select columns" = Table.SelectColumns(#"Get table", {"TeamName", "TeamSK"})
    in
        #"Select columns"
    
  6. Rename the query to Teams.

    Change Query Name to Teams

  7. From the Home tab, choose New Source to add another blank query and rename it to Areas.

  8. Open Advanced Editor and add the following query code, replacing organization information to match your view.

    let
        #"Get table" = VSTS.Feed("https://{OrganizationName}.analytics.visualstudio.com/_odata/v1.0/Areas?$select=AreaName,AreaSK"),
        #"Select columns" = Table.SelectColumns(#"Get table", {"AreaName", "AreaSK"})
    in
        #"Select columns"
    
  9. From the Home tab, choose New Source to add another blank query and rename it to AreaToTeam.

  10. Open Advanced Editor and add the following query code, replacing organization information to match your view.

    let
        #"Get table" = VSTS.Feed("https://{OrganizationName}.analytics.visualstudio.com/_odata/v1.0/Areas?$select=AreaSK&$expand=Teams($select=TeamSK)"),
        #"Select columns" = Table.SelectColumns(#"Get table", {"AreaSK", "Teams"}),
        #"Expand Teams" = Table.ExpandTableColumn(#"Select columns", "Teams", {"TeamSK"}, {"TeamSK"})
    in
        #"Expand Teams"
    

    Important

    The process of adding the three queries will only work for Area Paths that map to 1,000 teams or fewer.

  11. On the Home tab, choose Close & Apply.

    Power BI Desktop, Home, Close & Apply

  12. Next, choose Refresh to add AreaSK to the view.

Create the Power BI relationship mappings

The last step is to create the necessary relationships in Power BI.

  1. Open the Relationships view.

    Open the Relationships view

  2. From the Home tab, open Manage Relationships.

    Power BI Desktop, Home, Manage Relationships

  3. In the Manage Relationships dialog:
    a. Delete any relationships that might have been automatically detected.
    b. Choose New to create a bidirectional Many to One relationship between your View and Area. To learn more, see Bidirectional cross-filtering using DirectQuery in Power BI Desktop.

    Power BI Desktop, Manage Relationships dialog, View To Area

  4. Create a bidirectional One to Many relationship between Areas and AreaToTeam.

    Power BI Desktop, Home, Manage Relationships, Areas and AreaToTeam Mapping

  5. Create a bidirectional Many to One relationship between AreaToTeam and Teams.

    Power BI Desktop, Home, Manage Relationships, AreaToTeam and Teams Mapping

  6. Return to the Report view, and open the context menu for TeamName and TeamSK fields and choose the Hide option.

    Hide Area

  7. Hide corresponding SKs in your View and Team tables.

Filter a report view on teams

Now that you have the mappings in place, you can filter a report view based on Teams. To begin, start by adding a slicer to your report based on Team Name (remove Blank if needed).

Power BI Desktop, Team Slicer

Now you can filter all visualization on a report using a slicer or any other supported filtering functionality in Power BI.

Power BI Desktop, Count Filtered by Team