Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dax help on record level calculation

Hello, I need some help on Power BI Desktop Dax queries could you help me to achieve the RED highlighted column or measure  
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I am near to the solution but finding difficult to resolve the wrong totals


using the below dax query:

TaskDuration =
VAR _TaskEnter = CALCULATE( MIN( Repo[DateTime] ), FILTER(Repo, Repo[Type] = "ExecutionEnteredTaskNode" && Repo[WorkflowPhaseId] = "Open" && NOT( Repo[ModifiedWorkflowPhaseId] IN {"Closed","Skip"}) && MAX(Repo[TaskTitle]) = Repo[TaskTitle] ) )

VAR _TaskSkip = CALCULATE( MIN( Repo[DateTime] ), FILTER(Repo, Repo[Type] = "ExecutionTaskNodeWasEdited" && Repo[WorkflowPhaseId] = "Open" && Repo[ModifiedWorkflowPhaseId] = "Skip" && MAX(Repo[TaskTitle]) = Repo[TaskTitle] ) )

VAR _TaskSkipEnter = CALCULATE( MIN( Repo[DateTime] ), FILTER(Repo, Repo[Type] = "ExecutionEnteredTaskNode" && Repo[WorkflowPhaseId] = "Skip" && NOT( Repo[ModifiedWorkflowPhaseId] IN {"Closed","Skip"}) && MAX(Repo[TaskTitle]) = Repo[TaskTitle] ) )

VAR _TaskExit = CALCULATE( MAX( Repo[DateTime] ), FILTER(Repo, Repo[Type] = "ExecutionTaskNodeWasEdited" && Repo[WorkflowPhaseId] IN {"Open", "Skip"} && Repo[ModifiedWorkflowPhaseId] = "Closed" && MAX(Repo[TaskTitle]) = Repo[TaskTitle] ) )

VAR _Calculation = CALCULATE( IF( _TaskSkip = BLANK(), DATEDIFF( _TaskEnter, _TaskExit, SECOND ), DATEDIFF( _TaskEnter , _TaskSkip, SECOND ) + DATEDIFF( _TaskSkipEnter , _TaskExit, SECOND ) ) )

VAR Total = ADDCOLUMNS ( SUMMARIZE ( Repo, Repo[TaskTitle] ), "Duration", _Calculation )

RETURN

_Calculation