Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help: Calculating with restrictions

Dear DAX expert,

 

Previously I calculated the change over time between production orders with the following DAX:

 

Omsteltijden in seconden = 
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; SECOND )
Valid outcome = 
SUMX (
    RegistratieMaasland;
     IF( [Omsteltijden in seconden] >=0
            && [Omsteltijden in seconden] <= 1800;
            [Omsteltijden in seconden];
        BLANK())
     )

 

Now I would like to change the valid outcome formula in a way that the outcomes are only valid when subnummer between productID has changed. This basically means that the changeover between an order is real (because of the change of subnummer). 

 

Check herebelow for an example.

 

All help is greatly appreciated, if its not clear please ask,

 

 

  • Hi Anonymous

     

    You may try to use below measure:

     

    Valid outcome =
    VAR lastSubnumber =
    LOOKUPVALUE (
    RegistratieMaasland[Subnumber],
    RegistratieMaasland[ProductieID], MAX ( RegistratieMaasland[ProductieID] ) - 1
    )
    RETURN
    IF (
    MAX ( RegistratieMaasland[Subnumber] ) = lastSubnumber,
    BLANK (),
    [Omsteltijden in seconden]
    )

    Regards,

     

    Cherie

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous

     

    You may try to use below measure:

     

    Valid outcome =
    VAR lastSubnumber =
    LOOKUPVALUE (
    RegistratieMaasland[Subnumber],
    RegistratieMaasland[ProductieID], MAX ( RegistratieMaasland[ProductieID] ) - 1
    )
    RETURN
    IF (
    MAX ( RegistratieMaasland[Subnumber] ) = lastSubnumber,
    BLANK (),
    [Omsteltijden in seconden]
    )

    Regards,

     

    Cherie