Forum Discussion

Hennie7863's avatar
Hennie7863
Frequent Visitor
8 years ago

Leadtime between time and certain conditions

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...
Need Help
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    Hennie7863
    Frequent Visitor
    8 years ago

    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

     

  • Hennie7863's avatar
    Hennie7863
    Frequent Visitor
    8 years ago

    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...