How to calculate chained worklleaves duration only once

[henkka]

Hi,

I have a calculated column which calcutes the total duration for the chained workleaves. Now I have a problem for example there is three one day work leaves that are continuing the previous one but how can I calculate the total duration so that only the firs workleave gets a total duration and the followign are null or zero.

ChainedWorkLeavesDuration =

VAR CurrentWorkLeaveId = FactWorkleaves[Workleave.id]

VAR ChainedWorkLeaves =

FILTER ( 

FactWorkleaves , PATHCONTAINS ( FactWorkleaves[path] , CurrentWorkLeaveId )

           )

RETURN  

SUMX ( ChainedWorkLeaves , FactWorkleaves[Duration] )

Now the table calculates like this

Workleave.id	Previouschainedworkleave.id	path	Duration	Total duration
111		111	1	1
222	111	111 | 222	1	2
333	222	111|222|333	1	3
444		444	1	1
555		555	1	1

but the correct end result would be

Workleave.id	Previouschainedworkleave.id	path	Duration	Total duration
111		111	1	3
222	111	111 | 222	1	0
333	222	111|222|333	1	0
444		444	1	1
555		555	1	1

[barritown · Accepted solution]

Hi @henkka,

I'd propose to solve you problem with a calculation column like this:

And in the text format for convenience:

Total duration = 
VAR CurrentWorkLeaveID = [Workleave.id]
RETURN SUMX ( 
            FILTER ( 
                ADDCOLUMNS ( ALL ( FactWorkleaves ), 
                             "Flag", 
                             FIND ( CurrentWorkLeaveID, [path], 1, BLANK () ) = 1 ), 
                [Flag] = TRUE () ), 
            [Duration] ) + 0

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian