Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
5 years ago

Count last Entry per Order

Hello!

I have 2 tables:

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

Order-NrDateValue
25801.02.202150
93813.12.202250

 

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

 

Order NrStepQuantity
2580150
2580250
2580350
2580450
2580550
9380150

 

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

Something like:

 0102030405
258    1
9381    

 

How would you do that using DAX Measures?

 

 

Need Help

4 Replies

  • jameszhang0805's avatar
    jameszhang0805
    Resolver IV
    5 years ago

    Please try:

    VAR _max = CALCULATE( MAX( 'Table'[Step] ) , ALLEXCEPT( 'Table' , 'Table'[Order Nr] ) )
    RETURN IF( MAX( 'Table'[Step] ) = _max , 1 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    try this
    Column =
    var maxValue =
    CALCULATE (
    MAX ( 'Table (2)'[steps] ),
    ALLEXCEPT ( 'Table (2)', 'Table (2)'[order] )
    )
    return
    if ('Table (2)'[steps]=maxValue , 1)

     

    • joshua1990's avatar
      joshua1990
      Post Prodigy
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        @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!