How to: Area and Iteration organization charts on Azure DevOps

This Power BI report shows Area and Iteration Organization Chart from respective Team Project selected

An original report is available on my GitHub repository. In this specific report, I’m using OData to connect Azure DevOps. I have access to many tables on this link where I can select different information in Azure DevOps. To connect OData, the link below was used (here was created an Organization parameter):

https://analytics.dev.azure.com/{Organization}/_odata/v4.0-preview

• After the connection, the following tables were selected:

• After that, it’s necessary to create two custom columns into Areas and Iteration tables. These columns are required to use on Hierarchy Chart by Akvelon
• Table Areas

FatherArea = IF ( Areas[Depth]=0,"",
IF(Areas[Depth]=1,Areas[AreaLevel1],
IF(Areas[Depth]=2, Areas[AreaLevel1] & "\" & Areas[AreaLevel2],
IF(Areas[Depth]=3,Areas[AreaLevel1] & "\" & Areas[AreaLevel2] & "\" & Areas[AreaLevel3],
IF(Areas[Depth]=4,Areas[AreaLevel1] & "\" & Areas[AreaLevel2] & "\" & Areas[AreaLevel3] & "\" & Areas[AreaLevel4],
IF(Areas[Depth]=5,Areas[AreaLevel1] & "\" & Areas[AreaLevel2] & "\" & Areas[AreaLevel3] & "\" & Areas[AreaLevel4] & "\" & Areas[AreaLevel5],
Areas[AreaLevel1] & "\" & Areas[AreaLevel2] & "\" & Areas[AreaLevel3] & "\" & Areas[AreaLevel4] & "\" & Areas[AreaLevel5] & "\" & Areas[AreaLevel6]
))))))
FatherNumber = LOOKUPVALUE(Areas[Number],Areas[AreaPath],Areas[FatherArea])

• Table Iterations

FatherIteration = IF (Iterations[Depth]=0,"",
IF(Iterations[Depth]=1,Iterations[IterationLevel1],
IF(Iterations[Depth]=2, Iterations[IterationLevel1] & "\" & Iterations[IterationLevel2],
IF(Iterations[Depth]=3,Iterations[IterationLevel1] & "\" & Iterations[IterationLevel2] & "\" & Iterations[IterationLevel3],
IF(Iterations[Depth]=4,Iterations[IterationLevel1] & "\" & Iterations[IterationLevel2] & "\" & Iterations[IterationLevel3] & "\" & Iterations[IterationLevel4],
IF(Iterations[Depth]=5,Iterations[IterationLevel1] & "\" & Iterations[IterationLevel2] & "\" & Iterations[IterationLevel3] & "\" & Iterations[IterationLevel4] & "\" & Iterations[IterationLevel5],
Iterations[IterationLevel1] & "\" & Iterations[IterationLevel2] & "\" & Iterations[IterationLevel3] & "\" & Iterations[IterationLevel4] & "\" & Iterations[IterationLevel5] & "\" & Iterations[IterationLevel6]
))))))
FatherNumber = LOOKUPVALUE(Iterations[Number],Iterations[IterationPath],Iterations[FatherIteration])

• After creating all columns, it’s possible to create a report using Hierarchy Chart by Akvelon:

• Area organization chart

• Iteration organization chart