Count last entry per order

[joshua1990]

Hey guys!

I need to count the last entry per order.

I have an Order Header:

Order-Nr (Key)	Date
1	01.01.2021
2	01.01.2021

Then I have an Order Master

Order-Nr (Key)	Step (Key)	Date
1	01	01.01.2021
1	02	05.01.2021
1	03	07.01.2021

And I have a Step Description Table:

Step (Key)	Descr
01	XX
02	BB

What I need now is a Matrix like this:

Order-Nr	01	02	03
1			1
2	1

So this matrix will show me the last/ current step for each order.

How would you build a measure to get this result?

[HashamNiaz]

Hi !

You can use following DAX to get the desired output;

LatestStep = 
    VAR _LastOrder = MAX(OrderStep[Step])
    VAR _LatestOrder = CALCULATE(_LastOrder, ALLSELECTED(Steps[Step]))
RETURN
    IF([LatestOrder] = [LastOrder], 1, BLANK())

Here is a sample Data Model diagram;

OrderSteps

In your matrix visual on column headers, [Step] choose show items with no data.

Regards,

Hasham

[joshua1990]

@HashamNiaz Thanks a lot! Is max also working for strings?

[HashamNiaz]

You can treat Step No as Numeric, so MAX() will be robust. MAX() still works with strings but will not gurantee expected results.