Drillthrough to page using an inactive relationship

[CorDen]

Hello everyone,

I am trying to create a Power BI Desktop report that conveniently shows workitems from our Azure DevOps board. I only want to show workitems that are either Epics, Features or User Stories. Because each Epic can have multiple child Features and each Feature can in turn have multiple child User Stories, I also want to show which workitems are related so we can track the progression of completed child woritem per parent workitem. 

In order to visualize this, I extract the WorkItems table from our ADO environment using ODATA. I made sure that the ODATA query only returns the WorkItems that are relevant for me and my team. For now I would like my report to show two things:

1. A general overview of all workitems that shows information like amount of workitems per workitemtype (e.g. Epic, Feature or User Story), the name of the employee it's assigned to and the state(category) of the workitem (e.g. new, in progress or completed). 
2. A more detailed tabular page that shows which workitems are related to eachother and how many child workitems have been completed. 

I have already created two pages: Overview and WorkItems. In order to show which workitems are related to eachother based on ParentWorkItemId on the WorkItems page, I created 3 additional tables: Epics, Features and Stories. Screenshots of the pages and datamodel are included below. I would like to be able to let users of the report make a selection on the Overview page and drill through to the WorkItems page to show the selected workitems and their related parent/child workitems based on the selected workitem type (Epic/Feature/User Story). However, when I try to do this I encounter a problem because the WorkItems table can only have a single active relationship with either the Epics, Features or Stories table at a time.

Now I figured that using DAX functions like USERELATIONSHIP or RELATED might solve my problem, but I haven't been able to make it work yet. I thought about creating 3 seperate pages (1 for each workitem type) in combination with duplicating each of the workitem tables and creating a single active relationship, but that doesn't seem like an ideal solution.

I am wondering if there is a way to drill through to a page, but take into account the selected workitem type and show results on the WorkItems page by activating one of the inactive relationships (to the Epics table when workitemtype = Epic, to the Features table when workitemtype = Feature etc.). I have included screenshots of a sample report, the relationship view and the desired results below. Any and all advice is welcome. Thank you in advance.

With kind regards,

CorDen.

Overview page

WorkItems page

Model view

Example of desired result: use Epic relationship

Example of desired result: use Feature relationship

[amitchandak · Accepted solution]

@CorDen , In this case, userelationship will not work. You have inactive join because of multiple path

You can keep "workitem" independent/disconnected table

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

[CorDen]

Hi @amitchandak, thanks a lot for your response and suggested solution. I managed to get the desired result by making the following DAX formula, applying it to the table visuals and filtering on isnotblank:

Control Downdrill =

VAR _tab = SUMMARIZE(ALLSELECTED(WorkItems), WorkItems[WorkItemId])

RETURN

COUNTROWS(

    FILTER(

        UNION(

            VALUES(Epics[WorkItemId]),

            VALUES(Features[WorkItemId]),

            VALUES(Stories[WorkItemId])

        ),

        [WorkItemId] in _tab

    )

)

Thanks again!