Dax help on record level calculation

[praveenlc]

Hello, I need some help on Power BI Desktop Dax queries

could you help me to achieve the RED highlighted column or measure

[v-jianboli-msft · Accepted solution]

Hi @praveenlc ,

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 · Accepted solution]

Hi @praveenlc ,

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.

[v-jianboli-msft · Accepted solution]

Hi @praveenlc ,

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.

[praveenlc]

hi @v-jianboli-msft could you help me on the below please, I tried ranking, Index

Need to find the duration based on EndTime - StartTime from the image below. Please!!!

OriginalDate	Type	ProcedureTitle	GroupTitle	TaskNodeId	TaskTitle	Status
2022-08-22T08:15:49	ExecutionEnteredGroupNode	130053	Blisteratrice Smontaggio LO	-	-	Incomplete
2022-08-22T08:15:50	ExecutionExitedGroupNode	130053	Blisteratrice Smontaggio LO	-	-	Incomplete
2022-08-22T08:15:50	ExecutionEnteredTaskNode	130053	Blisteratrice Smontaggio LO	Blisteratrice_Smontaggio_LO_1	Impostare modalità CF	Open
2022-08-22T08:15:57	ExecutionEnteredTaskNode	130053	Blisteratrice Smontaggio LO	Blisteratrice_Smontaggio_LO_2	Caricatore prodotto	Open
2022-08-22T08:15:57	ExecutionExitedTaskNode	130053	Blisteratrice Smontaggio LO	Blisteratrice_Smontaggio_LO_1	Impostare modalità CF	Closed
2022-08-22T08:15:57	ExecutionTaskNodeWasEdited	130053	Blisteratrice Smontaggio LO	Blisteratrice_Smontaggio_LO_1	Impostare modalità CF	Open
2022-08-22T08:16:04	ExecutionTaskNodeWasEdited	130053	Blisteratrice Smontaggio LO	Blisteratrice_Smontaggio_LO_2	Caricatore prodotto	Open
2022-08-22T08:16:05	ExecutionEnteredGroupNode	130053	Blisteratrice Smontaggio LO	-	-	Complete
2022-08-22T08:16:05	ExecutionExitedTaskNode	130053	Blisteratrice Smontaggio LO	Blisteratrice_Smontaggio_LO_2	Caricatore prodotto	Closed
2022-08-22T08:16:13	ExecutionExitedGroupNode	130053	Blisteratrice Smontaggio LO	-	-	Complete
2022-08-22T08:28:52	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T08:28:59	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T08:29:30	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:04	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:09	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:31	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:32	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:49	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:54	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:54:54	ExecutionEnteredTaskNode	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Open
2022-08-22T10:54:59	ExecutionTaskNodeWasEdited	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Open
2022-08-22T10:55:00	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T10:55:00	ExecutionExitedTaskNode	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Skip
2022-08-22T11:06:36	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T11:08:59	ExecutionExitedTaskNode	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Skip
2022-08-22T11:09:07	ExecutionEnteredTaskNode	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Skip
2022-08-22T11:09:07	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Incomplete
2022-08-22T11:09:14	ExecutionTaskNodeWasEdited	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Skip
2022-08-22T11:09:15	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Complete
2022-08-22T11:09:15	ExecutionExitedTaskNode	130053	Blisteratrice Montaggio LO	Blisteratrice_Montaggio_LO_1	Caricatore prodotto	Closed
2022-08-22T16:29:48	ExecutionEnteredGroupNode	130053	Blisteratrice Montaggio LO	-	-	Complete
2022-08-22T16:30:09	ExecutionExitedGroupNode	130053	Blisteratrice Montaggio LO	-	-	Complete

[praveenlc]

Hello @v-jianboli-msft
Thank you! for the quick measure help, it works for my problem. But I guess I was not clear in explaing the real problem. I am attaching link to powerbi file in which I have created the usecase and senario. Please refer the table "Repo" and guide me to resolve.

Thanks in Advance!!!

https://www.dropbox.com/scl/fo/3s93re4anirz07bdzzu3e/h?dl=0&rlkey=gmote7izvampzn52xgau70d1j

[praveenlc]

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