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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
J_o_n_a_s
Helper I
Helper I

Hierarchy Question?

Hi all,
 
I am not sure if this falls under hierarchies, but I didn't manage to solve this that way. 
 
So I have this table:
 
IssueName of Parent IssueHours
A-1 1
A-2 2
B-1 3
B-2 4
B-3 5
B-22B-26
B-33B-37
C-1 8
D-1 9
E-1 10
 
The first column shows a list of Issues. However in reality B-22 and B-33 are sub tasks of B-2 and B-3 respectively (which are the Parent Issues).
 
So my question is that how can I create a hierarchy (or some other solution) where if I want to know the hours related to B-2 it will give me 10 (4+6) and for B-3 a result of 12 (5+7).
 
Thanks for your help
Jonas
1 ACCEPTED SOLUTION
mhossain
Solution Sage
Solution Sage

@J_o_n_a_s 

 

You can create a mapping table like below and create relationship with your table, and then below mapping table will work like Level1 and Level2. Hope this helps.

 

Issue Name of Parent Issue
A-1 A-1
A-2 A-2
B-1 B-1
B-2 B-2
B-3 B-3
B-22 B-2
B-33 B-3
C-1 C-1
D-1 D-1
E-1 E-1

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

or, if you dont want to go that route; this measure should give you what you want:

TotalHours = 
var currentIssue = SELECTEDVALUE(issues[Issue])
var isParent = if(not(isblank(lookupvalue(issues[Name of Parent Issue],issues[Name of Parent Issue],currentIssue))),1,0)
var parentHours = calculate(sum(issues[Hours]),filter(all(issues),currentIssue=issues[Name of Parent Issue]))
var issueHours = calculate(sum(issues[Hours]))
return

 if(isParent, parentHours+issueHours ,issueHours)

Capture2.PNG 

mhossain
Solution Sage
Solution Sage

@J_o_n_a_s 

 

You can create a mapping table like below and create relationship with your table, and then below mapping table will work like Level1 and Level2. Hope this helps.

 

Issue Name of Parent Issue
A-1 A-1
A-2 A-2
B-1 B-1
B-2 B-2
B-3 B-3
B-22 B-2
B-33 B-3
C-1 C-1
D-1 D-1
E-1 E-1

Thanks. I found this mapping table clear. I created it and then added another column showing just the sub tasks:
 
J_o_n_a_s_2-1611908509226.png

 

So then I ended up with this:
 
J_o_n_a_s_3-1611908509227.png

 

 
J_o_n_a_s
Helper I
Helper I

That's true. Thanks. But what if I would like to see the hours that correspond to each of them (B-2 and B-22)?

@J_o_n_a_s ,

The you can just pull the original column(Issue).

What is you expected result? Can you provide it in tabular format?

Anand24
Super User
Super User

Hi @J_o_n_a_s ,

 

You can simple create a new column and use that.

DAX of new column:

New Issue = IF('Table'[Name of Parent Issue]=BLANK(),'Table'[Issue],'Table'[Name of Parent Issue])

 

Image Reference:

col.PNG

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.