Dax help on record level calculation

Hi Anonymous ,

 

Based on your description, I have modified your DAX:

Measure = 
VAR Total =
    ADDCOLUMNS (
        SUMMARIZE ( Repo, 'Repo'[ProcedureTitle], 'Repo'[GroupTitle], Repo[TaskTitle] ),
        "TaskEnter",
            CALCULATE (
                MIN ( Repo[Date] ),
                FILTER (
                    Repo,
                    Repo[Type] = "ExecutionEnteredTaskNode"
                        && Repo[WorkflowId] = "Open"
                        && NOT ( Repo[ModifiedWorkflowId] IN { "Closed", "Skip" } )
                            && [ProcedureTitle] = EARLIER ( Repo[ProcedureTitle] )
                            && [GroupTitle] = EARLIER ( Repo[GroupTitle] )
                            && [TaskTitle] = EARLIER ( Repo[TaskTitle] )
                )
            ),
        "TaskSkip",
            CALCULATE (
                MIN ( Repo[Date] ),
                FILTER (
                    Repo,
                    Repo[Type] = "ExecutionTaskNodeWasEdited"
                        && Repo[WorkflowId] = "Open"
                        && Repo[ModifiedWorkflowId] = "Skip"
                        && [ProcedureTitle] = EARLIER ( Repo[ProcedureTitle] )
                        && [GroupTitle] = EARLIER ( Repo[GroupTitle] )
                        && [TaskTitle] = EARLIER ( Repo[TaskTitle] )
                )
            ),
        "TaskSkipEnter",
            CALCULATE (
                MIN ( Repo[Date] ),
                FILTER (
                    Repo,
                    Repo[Type] = "ExecutionEnteredTaskNode"
                        && Repo[WorkflowId] = "Skip"
                        && NOT ( Repo[ModifiedWorkflowId] IN { "Closed", "Skip" } )
                            && [ProcedureTitle] = EARLIER ( Repo[ProcedureTitle] )
                            && [GroupTitle] = EARLIER ( Repo[GroupTitle] )
                            && [TaskTitle] = EARLIER ( Repo[TaskTitle] )
                )
            ),
        "TaskExit",
            CALCULATE (
                MAX ( Repo[Date] ),
                FILTER (
                    Repo,
                    Repo[Type] = "ExecutionTaskNodeWasEdited"
                        && Repo[WorkflowId]
                            IN { "Open", "Skip" }
                                && Repo[ModifiedWorkflowId] = "Closed"
                                && [ProcedureTitle] = EARLIER ( Repo[ProcedureTitle] )
                                && [GroupTitle] = EARLIER ( Repo[GroupTitle] )
                                && [TaskTitle] = EARLIER ( Repo[TaskTitle] )
                )
            )
    )
VAR _A =
    ADDCOLUMNS (
        Total,
        "Duration",
            IF (
                [TaskSkip] = BLANK (),
                DATEDIFF ( [TaskEnter], [TaskExit], SECOND ),
                DATEDIFF ( [TaskEnter], [TaskSkip], SECOND )
                    + DATEDIFF ( [TaskSkipEnter], [TaskExit], SECOND )
            )
    )
RETURN
    SUMX ( _A, [Duration] )

Outout:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous ,

 

This question seems to have gone beyond the initial topic.

Please consider about marking the reply to the question and create a new post on this basis, which will make the topic of the post more targeted and better help others.

Thanks in advance!

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.