Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Epic = Grand Parent, Task = Parent and Sub-task = child. I need help. I am newbie and trying to create a new column("Total Hours") that will show the sum of Sub-task hours to epic row. See example above in red for your reference.
Hi @
-
Try this,
Total Hours =
VAR total_subtask =
CALCULATE (
SUM ( 'Table'[Hours] ),
FILTER ( ALL ( 'Table' ), 'Table'[Issue-Type] = "Sub-task" )
)
RETURN
IF ( SELECTEDVALUE ( 'Table'[Issue-Type] ) = "Epic", total_subtask, BLANK () )
Result:
See sample file attached below.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xiaotang, Thanks but what if there are another epics with different issue IDs? for example https://ibb.co/5BgD9D4
Hi @Sakamoto_S
-
Thanks for your reply.
But it would be better for the precondition to be given at the beginning. Because the solutions are different.🤣
-
create the measure firstly:
_IssueID = LEFT(SELECTEDVALUE('Table'[Issue ID]),1)
then, create the measure,
Total Hours 2 =
VAR total_subtask =
CALCULATE (
SUM ( 'Table'[Hours] ),
FILTER (
ALL ( 'Table' ),
'Table'[Issue-Type] = "Sub-task"
&& LEFT ( SELECTEDVALUE ( 'Table'[Issue ID] ), 1 ) = [_IssueID]
)
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[Issue-Type] ) = "Epic"
&& LEFT ( SELECTEDVALUE ( 'Table'[Issue ID] ), 1 ) = [_IssueID],
total_subtask,
BLANK ()
)
Result:
Hope it helps!
See sample file attached below.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
Hi @v-xiaotang Thanks. I tried your query but I've encountered some errors.
The syntax for 'RETURN' is incorrect. (DAX(VAR _IssueID = LEFT ( SELECTEDVALUE ('Reggie_JIRAIssueListExport'[Issue id] ), 1 )VAR total_subtask = CALCULATE ( SUM ( 'Reggie_JIRAIssueListExport'[Hours] ), FILTER ( ALL ( 'Reggie_JIRAIssueListExport' ), 'Reggie_JIRAIssueListExport'[Issue Type] = "Sub-task" && LEFT ( SELECTEDVALUE ( Reggie_JIRAIssueListExport[Issue id], 1 ) = [_IssueID] ) )RETURN IF ( SELECTEDVALUE ( 'Reggie_JIRAIssueListExport'[Issue Type]) = "Epic" && LEFT ( SELECTEDVALUE ('Reggie_JIRAIssueListExport'[Issue id]), 1 ) = [_IssueID] total_subtask, BLANK() ))).
Hi @Sakamoto_S
sorry, I forgot to say you need to create the measure first, ( I've corrected my reply
_IssueID =
LEFT ( SELECTEDVALUE ('Reggie_JIRAIssueListExport'[Issue id] ), 1 )
then, create the measure
Total Hours 2 =
VAR total_subtask =
CALCULATE (
SUM ( 'Reggie_JIRAIssueListExport'[Hours] ),
FILTER (
ALL ( 'Reggie_JIRAIssueListExport' ),
'Reggie_JIRAIssueListExport'[Issue Type] = "Sub-task"
&& LEFT ( SELECTEDVALUE ( 'Reggie_JIRAIssueListExport'[Issue id]), 1 ) = [_IssueID]
)
)
RETURN
IF (
SELECTEDVALUE( 'Reggie_JIRAIssueListExport'[Issue Type]) = "Epic"
&& LEFT ( SELECTEDVALUE ('Reggie_JIRAIssueListExport'[Issue id]), 1 ) = [_IssueID],
total_subtask,
BLANK()
)
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
Hi @Sakamoto_S ,
You need to follow a Hierarchy parent-child setup.
Check the SQLBI post about a similar setup.
https://www.daxpatterns.com/parent-child-hierarchies/
Believe that the only question here is the fact that you have the total hours in task and in subtasks.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |