Hierarchy Question?

[J_o_n_a_s]

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:

Issue	Name of Parent Issue	Hours
A-1		1
A-2		2
B-1		3
B-2		4
B-3		5
B-22	B-2	6
B-33	B-3	7
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

[Anonymous]

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)

 

[mhossain · Accepted solution]

@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

[J_o_n_a_s]

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

So then I ended up with this:

[J_o_n_a_s]

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)?

[Anand24]

@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]

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:

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