Need help: extra restrictions to calculating changeover times

[Anonymous]

Dear DAX expert,

Previously I have posted a question on changeover times. The following DAX was used:

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 )

The (highlighted) subnummer in the picture below is like a unique product code. Basically when a production order changes from subnummer it means the line should be prepared for the new order. Sometimes little modifications have to be issued in order to start, sometimes they are larger.

Request 1:

In the picture below a changeover has taken place between order 6023 and 6024, the whole changeover took around 5 minutes total. Now in order to identify difficulties when changing over to a certain subnummer, I would like to have the "omsteltijden in minuten" one row down. So the 5 minutes in the example must be booked on subnummer 300-381, and not on 300-1200.

If possible please modify the above DAX (Omsteltijden in minuten). 

Request 2:

Secondly I was wondering if any of you experts is able to write some DAX to modify the valid outcome. Changing from a production order to another could have 3 reasons:

1. Changing from subnummer

2. Changing the amount of people on the production line ("Aantal mensen")

3. Accidently stopping the program will log an end time, in this case no subnummer nor amount of people has changed

I only want the "Omsteltijden in minuten" to be valid if my supervisors (aansturing) change from subnummer. If possible please modify the DAX below (valid outcome).

The DAX I used for the valid outcome:

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

All help is appreciated, many thanks!

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to use below measure for your data. For the request 2, i'm not fully understand it. Maybe you need add an index for subnummer. Then use IF function to check the subnummer is changed or not.

Omsteltijden in minuten =
VAR lastProductID =
    CALCULATE (
        MIN ( RegistratieMaasland[ProductieID] ),
        FILTER (
            ALL ( RegistratieMaasland ),
            RegistratieMaasland[Aansturing] = MIN ( RegistratieMaasland[Aansturing] )
                && RegistratieMaasland[Lijn] = MIN ( RegistratieMaasland[Lijn] )
                && RegistratieMaasland[ProductieID]
                    = MIN ( RegistratieMaasland[ProductieID] ) - 1
        )
    )
VAR endTime =
    MIN ( RegistratieMaasland[Begintijd def] )
VAR startTime =
    CALCULATE (
        MIN ( RegistratieMaasland[Eindtijd def] ),
        FILTER (
            ALL ( RegistratieMaasland ),
            RegistratieMaasland[ProductieID] = lastProductID
        )
    )
RETURN
    DATEDIFF ( StartTime, endTime, MINUTE )

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft,

Thanks so much it's working out well!