Leadtime between time and certain conditions

[Hennie7863]

Hi,

Question about calculating Leadtimes between certain moments in time with a condition. 

I've included an example  :

I have indicators that determines the start of a new phase per process. So I have to determine the time for phase 1 and phase 2. I can't assign the phase to certain processsteps because the end of phase and the start of the next phase is determined by the same processtep.

Hennie

[Eric_Zhang]

@Hennie7863

You can create two calculated columns. See more in the attached pbix file.

phase2 proceStepID =
MINX (
    FILTER (
        FILTER ( yourTable, EARLIER ( yourTable[ProcesID] ) = yourTable[ProcesID] ),
        yourTable[Phase2] = 1
    ),
    yourTable[ProcesStepID]
)

phases =
SWITCH (
    TRUE (),
    yourTable[ProcesStepID] <= yourTable[phase2 proceStepID], "phase1",
    "phase2"
)

[Hennie7863]

Hi Eric,

Thank you for the proposed solution. Much appreciated. 

Although it 's in the right direction, It seems that the values for for procesID 3 and 4 are not correct. There are values for ProcesID 3 and 4 and those should be 0. 

If I expand the example with some more phases, the MINX-FILTER-FILTER DAX expression is only working for two phases, IMO. But in my reallife scenario I have multiple phases (to be exact 5 phases).

So I thought to create separate columns for calculation of time for phase1, phase2, phase3, etc, But that will give me a circular dependenc error

EndProcesStepID =
MINX (
FILTER (
FILTER ( FactProces, EARLIER ( FactProces[ProcesID] ) = FactProces[ProcesID] ),
FactProces[End] = 1
),
FactProces[ProcesStepID]
)

 =
SWITCH (
TRUE (),
FactProces[ProcesStepID] <= FactProces[EndproceStepID], [SumTimeInSec]
)

A circular dependency was detected: 'FactProces'[CalculatedColumn1],'FactProces'[CalculatedColumn1],'FactProces'[Phase1],'FactProces'[Phase1],'FactProces'[CalculatedColumn1].

Thanks again.

Hennie

ps. Maybe i can avoid the cirular dependency error by determing the processteps in my SQL query...

[Hennie7863]

Hi Eric,

Thank you for the proposed solution. Much appreciated. 

Although it 's in the right direction, It seems that the values for for procesID 3 and 4 are not correct. There are values for ProcesID 3 and 4 and those should be 0. 

Thanks again.

Hennie