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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dkgibbs
Frequent Visitor

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

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:

 

dkgibbs_0-1694795518231.png

 

From this, I get this output in my Matrix:

 

dkgibbs_2-1694796871238.png

 

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. 

 

dkgibbs_4-1694797708218.png

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

2 REPLIES 2
parry2k
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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.