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

Need Help

3 Replies

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