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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.