cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Need help: extra restrictions to calculating changeover times

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

 

tempsnip.png

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Need help: extra restrictions to calculating changeover times

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
v-cherch-msft Super Contributor
Super Contributor

Re: Need help: extra restrictions to calculating changeover times

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Need help: extra restrictions to calculating changeover times

Hi @v-cherch-msft,

 

Thanks so much it's working out well!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 844 guests
Please welcome our newest community members: