Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Need help calculating total changeover time per supervisor

Dear DAX experts,

 

In a previous thread I calculated (with the help of Dale) the total changeover time between production orders with the following dax:

 

Omsteltijden in minuten = 
VAR nextProductID =
    CALCULATE (
        MIN ( RegistratieMaasland[ProductieID] );
        FILTER (
            ALL ( RegistratieMaasland );
            RegistratieMaasland[Aansturing] = MIN ( RegistratieMaasland[Aansturing] )
                && RegistratieMaasland[Lijn] = MIN ( RegistratieMaasland[Lijn] )
                && RegistratieMaasland[ProductieID] > MIN ( RegistratieMaasland[ProductieID] )
        )
    )
VAR endTime =
    MIN ( RegistratieMaasland[Eindtijd def] )
VAR nextStartTime =
    CALCULATE (
        MIN ( RegistratieMaasland[Begintijd def] );
        FILTER ( ALL ( RegistratieMaasland ); RegistratieMaasland[ProductieID] = nextProductID )
    )
RETURN
    DATEDIFF ( endTime; nextStartTime;MINUTE )

This had the following impact on the outcomes:

 

 

Then we created a filter to filter out invalid outcomes (such as shift changes, or faulty inserts). Rows with a "1" are valid, and the "0" stands for invalid. I used the following DAX:

 

Valid outcome = 
SUMX (
    'RegistratieMaasland';
    IF (
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE ();
        1;
        0
    )
)

Now finaly I would like to know the total changeover time (in minutes) per supervisor (aansturing), only for the valid outcomes.

 

All help is appreciated,

 

Need Help

6 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

    I have modified your [Valid outcome] measure as this:

    Valid outcome = 
SUMX (
    'Measures omsteltijden',
    IF (
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE (),
        [Omsteltijden in minuten],
        0
    )
)

    And I have created a new measure to calculate the [changeover time per supervisor]:

    changeover time per supervisor = AVERAGEX('Measures omsteltijden','Measures omsteltijden'[Valid outcome])

    Result:

    You could also download the pbix to have a view:

    https://www.dropbox.com/s/5jnwlwpdza0r61r/Need%20help%20calculating%20total%20changeover%20time%20per%20supervisor.pbix?dl=0

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-danhe-msft,

       

      Thanks for helping me.

       

      In your picture there is one invalid count, which is 125 minutes. This has probably been a stop and not a changeover. 

       

      Would you be able to modify the formula so that all 'omsteltijden in minuten' <0 and >30 are BLANK as a valid outcome. In this way they are excluded as part of the average.

       

      Best,

      Luuk

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

        From your description, I have modified my formula:

        Valid outcome = 
SUMX (
    'Measures omsteltijden',
    IF ([Omsteltijden in minuten]=BLANK(),0,
        IF(
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE (),
        0,
        [Omsteltijden in minuten]
        )
    )
)
        changeover time per supervisor = AVERAGEX('Measures omsteltijden','Measures omsteltijden'[Valid outcome])

        Result:

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He