Calculated column to count boxes moved out of the location of this box

[alexeisenhart]

I have four datasets:

Orders

• OrderID
• ReleasedDatetime
• CompleteDatetime

ShippedBoxes

• BoxID
• Location
• OrderID
• LastRobotMovementID

Movements

• MovementID
• BoxID
• FromLocation
• ToLocation
• StartDatetime
• EndDatetime

Boxes

• BoxID

The boxes are stored in lanes and a robot is used to retrieve the box (before it is placed on conveyors), so if a box is part of an order then the boxes in front of it will need to be moved to access it.

I want to add a calculated column to ShippedBoxes to track the number of boxes that the robot had to move to access the shipped box after the order was released. I've tried this a few different ways but nothing is panning out correctly.

My goal is to, for each record in ShippedBoxes, count the number of rows in Movements where the Movement[StartDatetime] is after the Order[ReleasedDatetime] and Movement[StartDatetime] is before the StartDatetime of the Movements record indicated by ShippedBoxes[LastRobotMovementID] and where the Movements[FromLocation] is the same as the FromLocation of the Movements record indicated by ShippedBoxes[LastRobotMovementID].

I've tried these forumulas, among many others, but the results are always blank. I feel like I'm close but I can't discover why it isn't working. I've also tried merging tables, but that was less fruitful.

Can anyone offer some advice?

CALCULATE(
	count(Movements[LastRobotMovementID])
	, all(Movements)
	, filter(
		Movements, Movements[StartDatetime] >= related(Orders[ReleasedDatetime])
		&& Movements[FromLocation] = LOOKUPVALUE(Movements[FromLocation], Movements[MovementID], ShippedBoxes[LastRobotMovementID])
		&& Movements[StartDatetime] < ShippedBoxes[LastRobotMovementID]
	)
)

COUNTROWS(
	filter(
		all(Movements)
		, Movements[StartDatetime] >= related(Orders[ReleasedDatetime])
			&& Movements[FromLocation] = LOOKUPVALUE(Movements[FromLocation], Movements[StartDatetime], ShippedBoxes[LastRobotMovementID])
			&& Movements[StartDatetime] < ShippedBoxes[LastRobotMovementID]
	)
)

CALCULATE(
	CALCULATE(count(Movements[LastRobotMovementID])
		, filter(
			Movements, Movements[Start Datetime] >= related(Orders[ReleasedDatetime])
			&& Movements[FromLocation] = LOOKUPVALUE(Movements[FromLocation], Movements[MovementID], max(ShippedBoxes[LastRobotMovementID]))
			&& Movements[StartDatetime] < max( ShippedBoxes[LastRobotMovementID])
		)
	)
)

[Anonymous · Accepted solution]

Hi @alexeisenhart,

You can try to use below formula if it suitable for your requirement:

Record Count=
VAR FromLocationlist =
    CALCULATETABLE (
        VALUES ( Movements[FromLocation] ),
        FILTER (
            ALL ( Movements ),
            Movements[MovementID] = EARLIER ( ShippedBoxes[LastRobotMovementID] )
        )
    )
VAR MovementStartDate =
    CALCULATE (
        MIN ( Movements[FromLocation] ),
        FILTER (
            ALL ( Movements ),
            Movements[MovementID] = EARLIER ( ShippedBoxes[LastRobotMovementID] )
        )
    )
RETURN
    CALCULATE (
        COUNT ( Movements[LastRobotMovementID] ),
        FILTER (
            ALL ( Movements ),
            Movements[FromLocation] IN FromLocationlist
                && Movements[StartDatetime] >= RELATED ( Orders[ReleasedDatetime] )
                && Movements[StartDatetime] < MovementStartDate
        )
    )

If above not help, please share sample data/pbix file for test and coding formula.

Regards,

Xiaoxin SHeng

[alexeisenhart]

Hi Xiaoxin,

Thank you so much! I had to tweak your formula a little bit, but I never would have gotten there without your help. This is the first time I've run into an applicable use of EARLIER -- that seems very powerful!

I had to pull the Order[ReleasedDatetime] out into a variable... is that because the ALL() changed the context in the final CALCULATE FILTER? Here's my final DAX forumula.

Record Count=
VAR FromLocationlist =
    CALCULATETABLE (
        VALUES ( Movements[FromLocation] ),
        FILTER (
            ALL ( Movements ),
            Movements[MovementID] = EARLIER ( ShippedBoxes[LastRobotMovementID] )
        )
    )
VAR MovementStartDate =
    CALCULATE (
        MIN ( Movements[StartDatetime] ),
        FILTER (
            ALL ( Movements ),
            Movements[MovementID] = EARLIER ( ShippedBoxes[LastRobotMovementID] )
        )
    )
VAR OrderReleasedDatetime = RELATED ( Orders[ReleasedDatetime] )

RETURN
    CALCULATE (
        COUNT ( Movements[LastRobotMovementID] ),
        FILTER (
            ALL ( Movements ),
            Movements[FromLocation] IN FromLocationlist
                && Movements[StartDatetime] >= OrderReleasedDatetime
                && Movements[StartDatetime] < MovementStartDate
        )
    )