Forum Discussion

Sakamoto_S's avatar
Sakamoto_S
Frequent Visitor
5 years ago

Hierarchy - Column

  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 e...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
v-xiaotang's avatar
v-xiaotang
Community Support
5 years ago

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.

  • Sakamoto_S's avatar
    Sakamoto_S
    Frequent Visitor
    5 years ago

    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() ))).

    .pbix file here 

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      5 years ago

      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.