Check for Tags Associated to a Work Item in a Power BI Report

Is a Tag Associated to a Work Item?

With Azure DevOps, you can use tags to manage your work items, such as user stories, tasks, bugs, and features. Tags are words or phrases that you can attach to work items to group, rank, or filter them. However, when you extract tags from ADO using the Analytics Views or ODATA query, they are a comma separated list of values. This makes it difficult to filter in Power BI or do calculations based on the tags.

In this blog, I will demonstrate how to check for tags associated to a work item using a calculated column in a Power BI report. This will allow you to create custom reports that show the status, progress, and quality of your work items based on the tags you use for them.

Implementation:

There are ways to achieve the following using only measures in the data model. To make it easier to debug and create filters,  the following steps use calculated columns.

Analytics Views Tag Field

In Azure DevOps, create and Analytics View and add the Tag field . Here is a screenshot of the Tags field in an analytics view.

In Power BI, connect to the analytics view in Azure DevOps or import the data. This blog post describes the entire process if you have never created an Analytics View and connected to Power BI desktop.

Select Close and Apply if in the Power Query editor to return to the Power BI report canvas.

Calculated Column

Each column targets one or more specific Tags attached to the work items. The outcome is a Boolean value if the work item meets the condition of having the associated tag(s). This is used as a filter in a measure or as a slicers.

Select the Work Item table and right click on the table and select New Column.

Single Tag Check:

This example checks for the tag iOS associated with the work item.

The code is as follows:

Bug-iOS-Tag-Check = 

var wiTags = SelectedValue('Bugs-Current'[Tags])  //This Tag column in the Analytics View called Bugs-Current. This is the current table where the calculated column is added.

var iOSCheck = Search("iOS", wiTags,1,0) //Search for the iOS tag in the variable. Return a 1 if found
var iOSVerify = if(iOSCheck > 0, 1,0) //Validates the tag is found
Return iOSVerify

Multiple Tag Check:

This example checks for multiple tags and returns a 1 if all the tags are found in the work item. It leverages the single tag check measure for the iOS tag and incorporates several other single tag checks.

Search for each of the Tags and return the position in the string if found

Add up all the positions. If > 0, then the WI has at least one of the tags

Closing:

These options are only if you know the work item tag you need to search for. Now the work item has Boolean value as a calculated column and you can filter using a slicer or code to identify the work items that meet the conditions.