Filter Work Items by Tags
Creating a report in Power BI using work items from Azure DevOps (ADO) offers a powerful way to visualize and analyze project data. One particularly effective feature is the ability to filter work items by tags. Tags allow teams to categorize and segment their work items according to various criteria, such as priority, team, or sprint. When these tagged work items are imported into Power BI, users can create dynamic and interactive reports that provide a focused view of specific aspects of the project.
This blog post shows how to create a filtered table of workitem tags to use as the source of a slicer on the Power BI report. This allows the user to dynamically filter the workitems containing the tags.
Implementation Steps:
This process requires three tables. The first table is the workitems in Analytics Views (AV) or an ODATA query. This blog post shows how to create an AV and connect to Power BI with an AV.
The second table is a list of workitem IDs and the associated tags. Follow the steps in this blog post to setup this table with an AV. Call this table Tag-WorkItem.
The third table is the list of tags used in the slicers.
Create a Table of Distinct Tags
The following steps describe the process for creating table of distinct tags after creating the table of workitems and related tags. For this example, the table is called Tags-WI.
Right click on the table and select Reference. This creates a new table based on the workitem and associated tags table.
Rename the table to something like Tag-List.
Format the Data in Power Query
Highligh the TagName field and right click and select Remove Other Columns
Trim the leading blank characters in the tags if present. Select Transform -> Format -> Trim
Remove duplicates by right clicking on the TagNames field and select Remove Duplicates.
Remove empty values by right clicking on the TagNames field and select Filter -> Remove Empty.
Data Model Relationships:
Create the relationship between the Tag-List and Tag-Workitem table using the TagName field in the relationship.
Make sure to set the filtering bidirectional between the Tag-Workitems and the Workitems table. This provides the ability to filter based on the Tag-List table.
In the screenshot below, the Workitem table is called Bugs-Current.
Filter Work Items by Tag with a Slicer:
The last step in filtering work items by tags is to add a slicer to the report and name it Tags.
Add the TagNames field from the Tag-List report to the slicer. This now controls the filtering of the work items on the report based on the tags selected.
The Tags slicer now filters the report to the work items that have the tag associated to it!