Forum Discussion
joshua1990
Post Prodigy
Post ProdigyCount last entry per order
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?
3 Replies
HashamNiazSolution Sage
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
joshua1990HashamNiaz 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.