Created DevOps Tree Structure...but Cannot Get Single User Filter.

[dkgibbs]

Hello!

Our company works predominantly with Dynamics 365 custom implementations for other companies and we manage our work in DevOps. Some of our larger clients want us to work within their DevOps site though so I pull our DevOps site data via Odata and our clients sites into a Power BI report to help us manage all the work items that we currently have across all customers.

I was able to work out how to replicate the parent/child tree structure that DevOps has (I am very open to suggestions on how to restructure it) but I have one significant draw back in that I can't sort out how to filter by who the ticket is assigned to across the various tiers using a single filter.

My Objective: Allow users to view the DevOps tickets in a tree Parent/Child matrix AND let them set a single filter that will show them the tickets that are assigned to that user.

Here is my current structure and why I have set it up this way:

I took a copy of the the WorkItems table from DevOps and started breaking it apart by first identifying all the tickets that did not have a parent ticket. I determined that these were my tier 1 tickets (I labeled them P1 - I can't remember why...) I filtered the list to only show those values. Then, I took the same WorkItems table, created another copy, filtered all the tickets that I determined were p1 tickets, and then repeated the process to find my next tier of items, all the way down until I found the very last level in our tree structure, which happens to be nested 6 tiers down. Here is a visual of my table and relationship structure:

From this, I get this output in my Matrix:

I am currently getting each line name by concatenating the "parent" ticket number to that ticket's number, the ticket type, and then the title of the ticket. So the line that starts with "108 - 109 | Epic"  means that the parent is 108 and 109 is the actual ticket. I will be removing the parent ticket number from the line name when I'm done, it just make it really easy to validate right now while I'm building it out. I also don't really like that I need to concatenate the various details from the ticket to see it all on one line, but I don't know a better way to do it. In my screenshot, the items that I put a green circle next to are from the table, All_WorkItems_p1. Yellow is from All_WorkItems_p2 and red is from All_WorkItems_p3. 

If the tickets in the table below are assigned to either User A, B or C, here is how I would like the visual to respond if I am able to filter by a single filter:

Filter for User A - Tickets 108, 109, and 113 are all still visible, the rest disappear.

Filter for User B - Tickets 108, 109, and 110 are still visible, the rest disappear.

Filter for User C - Tickets 108 and 122 are still visible, the rest disappear. 

Its important to be able to see the parental structure so that if you are looking for tickets that are assigned to a specific user, you can see where those tickets fit in.

I appreciate any input in advance and am very open to suggestions on how to resolve this whether it is adding another table, restructuring my tables/relationships, creating a custom/calculated column, or even a matrix.

Thank you!

Dana

[parry2k]

@dkgibbs it is a really interesting question. I have a few solutions in my head that most likely will work but might not be the smarter solution of my liking. I think it needs a bit of thinking, probably a change in the data model and a solution on top of that.

I am personally interested in tackling this because nature of the question. If you assist me by providing some sample dummy data in Excel with different use cases I would like to put together a solution. You can share files using OneDrive/google drive or send me an email directly. My email is in the signature of the post. Thanks!

[dkgibbs]

Thank you so much! I wasn't able to attach the sample data here so I have just emailed it to you instead.