Forum Discussion
Anonymous
8 years agoNot applicable
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
Microsoft 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