Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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


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!


Super User
Super User

@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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.