How to display Epic->Feature->Story->GrandStory in PowerBI

[ranjeewas]

Hi

I’m genrating a PowerBI report from AzureDevOps (ADO). I have a problem when displaying  ADO WorkItems-hierarchy in PowerBI.

I’m able to display Epic1->Feature1->Story1 hierarchy very easily by creating new tables using the WorkItemType in WorkItems table as below.

Epic-Table = CALCULATETABLE('WorkItems', 'WorkItems-ST'[WorkItemType] = "Epic")

Feature-Table = CALCULATETABLE('WorkItems', 'WorkItems-ST'[WorkItemType] = "Feature")

Story-Table =  CALCULATETABLE('WorkItems', 'WorkItems-ST'[WorkItemType] = "User Story")

But, I found a new WorkItem inside newly created "Story-Table", where it’s ParentWorkItemID is equal to a WorkItemID inside the same Story-Table.

Eg:

Story-Table:

Title	WorkItemID	ParentWorkItemID	Comment
Story1	1001	950	This ParentWorkItemID is equal to the WorkItemID in "Feature" Table (Upper level)
Story2	1002	1001	This ParentWorkItemID is equal to the WorkItemID in same "Story" Table.

How can I display Epic1->Feature1->Story1->Story2 heirarchy?

DAX is preferred.

[Anonymous]

Hi @ranjeewas 

If I understand correctly, are you needing to get the WorkItemID of the next level of each row? Then you can try the following method.

ChildWorkItemID = LOOKUPVALUE('Table'[WorkItemID], 'Table'[ParentWorkItemID], 'Table'[WorkItemID])

Output:

If I have misunderstood you, please provide some sample data and the expected results based on the sample data so that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ranjeewas]

Thanks @Anonymous. Thats what I need.
But I'm getting "A table of multiple values was supplied where a single value was expected"  warning. 

I tried below. It returns blank 

ChildWorkItemID = CALCULATE (FIRSTNONBLANK ('Table'[WorkItemID], 1 ),FILTER ( ALL ('Table'), 'Table'[WorkItemID] = 'Table'[ParentWorkItemID]))

Unfortunately, I'm not in a position to share the actual data (due to security reasons).

Table Structure:

Feature-Table:

Title	WorkItemID	ParentWorkItemID
Section-1	985	885
Section-2	986	884

Story-Table:

Title	WorkItemID	ParentWorkItemID
Colors	1000	985
Animals	1001	986
Red	1002	1000
Blue	1003	1000
Lion	1004	1001
Green	1005	1000
Tiger	1006	1001

My requirement is to display records as below through a PowerBI Matrix. Currently I'm getting issues when  trying to view the items in RED.

Section-1 (From Feature-Table)

|----------Colors (From Story-Table)

|----------Red (From Story-Table)

|----------Blue (From Story-Table)

|----------Green (From Story-Table)

Section-1

|----------Animals

|----------Lion (From Story-Table)

|----------Tiger (From Story-Table)

[Anonymous · Accepted solution]

Hi @ranjeewas 

I would like to apologize for the belated reply.

You can try the following:

1. Create a calculated table

Table = 
VAR _vtable = 
    FILTER ( 'Story', NOT 'Story'[ParentWorkItemID] IN { 1000, 1001 } )
VAR _vtable2 =
    EXCEPT ( 'Story', _vtable )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN (
                SELECTCOLUMNS (
                    _vtable,
                    "_Title", 'Story'[Title],
                    "_workID", 'Story'[WorkItemID]
                ),
                SELECTCOLUMNS (
                    _vtable2,
                    "_Title2", 'Story'[Title],
                    "_ID2", 'Story'[ParentWorkItemID]
                )
            ),
            [_workID] = [_ID2]
        ),
        [_Title],
        [_Title2]
    )

2. Create relationships between three tables

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ranjeewas]

Thanks a lot! Appriciate your help on this.

My only concer is below Filter (I need to hardcode the IDs):

Table = 
VAR _vtable = 
    FILTER ( 'Story', NOT 'Story'[ParentWorkItemID] IN { 1000, 1001 } )
VAR _vtable2 =
    EXCEPT ( 'Story', _vtable )