Count last Entry per Order

[joshua1990]

Hello!

I have 2 tables:

The first table (Sales Header) contains some basic information on order level. That means one row per order:

Order-Nr	Date	Value
258	01.02.2021	50
938	13.12.2022	50

Then I have the Sales Details table, which contains several rows per order:

Order Nr	Step	Quantity
258	01	50
258	02	50
258	03	50
258	04	50
258	05	50
938	01	50

Now I would like to get just the last entry per order.

Something like:

	01	02	03	04	05
258					1
938	1

How would you do that using DAX Measures?

[Anonymous]

try this

Column =

var maxValue =

CALCULATE (

MAX ( 'Table (2)'[steps] ),

ALLEXCEPT ( 'Table (2)', 'Table (2)'[order] )

)

return

if ('Table (2)'[steps]=maxValue , 1)

[joshua1990]

@Anonymous : Thanks, that works fine! What is, if a need a sub/ grand total? Then this measure will not work, right? I guess I need a countrows. How would you do that?

[Anonymous]

@joshua1990 From the visualization pane, go to the FORMAT of visual, and from there, you can keep the google buttons ON for subtotal. Pls, look at the Screenshots I have added.

Pls approve my reply and let me know if you have any further queries!

[jameszhang0805]

Please try:

VAR _max = CALCULATE( MAX( 'Table'[Step] ) , ALLEXCEPT( 'Table' , 'Table'[Order Nr] ) )

RETURN IF( MAX( 'Table'[Step] ) = _max , 1 )