Forum Discussion
Drill-Through Report with Different Tables
Hi,
I'm a beginner with Power BI.
I'm trying to populate data pulled from Azure DevOps. Currently, I have a Summary page with all of my visuals and then I have another page for the drill-through report. The nature of the data is such that there is a hierarchy i.e. Epic, Features, User Story, and Tasks. On the Summary page, I used Fact Work Items table while on my Drill Through report, I used Gather Documentation table. What I want to achieve in the drill-through report page is a list of all of the items across the hierarchy for each epic selected from the Summary page. The Summary page has a table with URL, Title, State, Epic Origin, and Work Item ID. From the rows here, I should drill down to the drill through report. But the problem I'm facing right now is only on Epic level while the rest are not included. What should my next step be? I've already tried suggestions from AI: Reboot, create dim table and new relationships, etc but these don't solve the issue I'm having.
bellabelbel Hey,
- Use a shared Work Item dimension for drill‑through (not fields from separate fact tables). Create a DimWorkItem with Id, ParentId, WorkItemType, Title, State, etc. Relate Fact Work Items[WorkItemId] → DimWorkItem[Id] and Gather Documentation[WorkItemId] → DimWorkItem[Id]. Set the drill‑through field to DimWorkItem[Id] (or a surrogate key from this dimension). This ensures the drill context flows to both pages/tables.
- Drive hierarchy on the drill‑through page from that dimension. Add hierarchy logic in DimWorkItem (Path = PATH(Id, ParentId); EpicId = VALUE(PATHITEM(Path, PATHLENGTH(Path)))). Build your table visual from DimWorkItem and filter rows where [EpicId] equals the drilled value (e.g., a simple measure: ShowUnderSelectedEpic = IF(MIN(DimWorkItem[EpicId]) = SELECTEDVALUE(DimWorkItem[Id]), 1, 0) and set visual filter = 1). This will list Features, User Stories, and Tasks under the selected Epic.
Thanks
Haish K
If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.
10 Replies
- Pragati11
Super User
HI bellabelbel
Can you share the semnatic model with all the relationships that you have in place in Power BI?
You can share a screenshot of it or your pbix file by removing any sensitive information from it.
Hi, I've shared by replying to my original post
- danextian
Hi bellabelbel
Have you disabled keep all filters? In the image below, I drilled through Epic 1 but still able to show all work item types under that Epic.
Hi bellabelbel ,
When drill-through functions as you are experiencing, it is typically due to how Power BI manages filter context across tables. Although a matrix can display a hierarchy using fields from related tables, drill-through does not retain the full hierarchical context in the same way. It only transfers filters from a single table or a unified key. If the hierarchy levels are distributed across multiple tables, only the top-level filter is consistently applied, and lower-level filters are omitted resulting in only the Epic value being passed through.
This is expected behavior, not a bug, and adjusting settings such as Keep all filters will not resolve the issue. To ensure drill-through works at every level, the data model should be modified so that all drill-through fields come from one table, such as a flattened table containing Epic, Feature, User Story, and Task in each row, or by creating a composite key for the hierarchy. This approach will allow the full context to be passed to the target page.
Thank you.
I've tried removing the Keep All Filters, double check my relationships, and also create a measure to apply to just the filter but it still showed only Epic level. Did you use the same table to create this?
I've replied to my original post with additional information to describe the situation better.
For reference, here is my model and the relationship I've established between the two tables:
Here is my summary and drilled through page and the associated tables I've used for them. Despite removing Keep All Filter, I'm still only seeing Epic level in my drill through page:
Hello Again bellabelbel ,
Thank you for providing the detailed screenshots and explaining the requirement. After reviewing them, I can confirm that your data model and relationships are correctly set up, and the behavior you’re seeing is expected. In Power BI, drill through only passes the filter context from the table used in the source visual. Although Fact Work Items and GatherDocumentation Expanded are linked by Work Item Id, drill through doesn’t use that relationship to rebuild row-level or hierarchical context in another fact table. Since your summary page is based on Fact Work Items, only the Epic level can consistently be passed through, while Feature, User Story, and Task are not included when the target page uses a different table.
Changing relationship directions, toggling Keep all filters, or using measures doesn’t affect this outcome. These options impact how visuals filter data but don’t change how drill through transfers context. To show the full hierarchy under a selected Epic on the drill through page, both source and target pages need to use the same table or a shared work item dimension containing Epic, Feature, User Story, and Task in the same row context. With separate tables on each page, drill through will only return the Epic level, and there’s no additional configuration to change this behavior.
Thank you.Hi bellabelbel ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.
- HarishKM
bellabelbel Hey,
- Use a shared Work Item dimension for drill‑through (not fields from separate fact tables). Create a DimWorkItem with Id, ParentId, WorkItemType, Title, State, etc. Relate Fact Work Items[WorkItemId] → DimWorkItem[Id] and Gather Documentation[WorkItemId] → DimWorkItem[Id]. Set the drill‑through field to DimWorkItem[Id] (or a surrogate key from this dimension). This ensures the drill context flows to both pages/tables.
- Drive hierarchy on the drill‑through page from that dimension. Add hierarchy logic in DimWorkItem (Path = PATH(Id, ParentId); EpicId = VALUE(PATHITEM(Path, PATHLENGTH(Path)))). Build your table visual from DimWorkItem and filter rows where [EpicId] equals the drilled value (e.g., a simple measure: ShowUnderSelectedEpic = IF(MIN(DimWorkItem[EpicId]) = SELECTEDVALUE(DimWorkItem[Id]), 1, 0) and set visual filter = 1). This will list Features, User Stories, and Tasks under the selected Epic.
Thanks
Haish K
If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.
