How to obtain a last values within a column based on sequence number/ counter

[Anonymous]

Hi All,

Can someone please guide me as to how to obtain values from other columns based on the maximum value from the counter column (in this case SeqNum)?

For example: For 'LoadNum' = 347374643, I have 'SeqNum' ranging from 1 - 6, however I only require to display values from 'Destination Zip' and 'DestinationWarehouse' where 'SeqNum' = 6, i.e. 'Destination Zip' = 8491322 and 'DestinationWarehouse' = JA Beverage Sage Kashima Plant.

Similarly, for LoadNum: 347374644, I only require to display values from 'Destination Zip' and 'DestinationWarehouse', where 'SeqNum' = 2.

However, LoadNum: 347374651, has only 1 line item with SeqNum = 1, then corresponding values from other two columns needs to be displayed.

Note: 'Last Value' function works for 'SeqNum', but not for 'DestinationZip' and 'DestinationWarehouse' as numbers and alphabets won't be maximum for these columns. Values will be random in these columns.

Thanks in advance.

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think you can simply create a measure in filterpane to filter data.

Like this:

Measure = 
VAR a =
    MAXX (
        FILTER ( ALL ( 'Table' ), [LoadNum] = SELECTEDVALUE ( 'Table'[LoadNum] ) ),
        [Seqnum]
    )
RETURN
    IF ( MAX(  'Table'[Seqnum] ) = a, 1, 0 )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Jihwan_Kim, thanks for reverting.

I tried applying the suggested formula, however doesn't seem to work.

Unable to attach the pbix file here or data file here.

[Krutigawale33]

Hello @Anonymous ,

Create the below measures

Seq_Num = var _max=MAX(Table[SeqNum]) return CALCULATE(MAX(Table[SeqNum]),ALLEXCEPT(Table,Table[LoadNum]),Table[SeqNum]=_max)

Destination_Zip = var _max=MAX(Table[SeqNum]) return CALCULATE(MAX(Table[Destination Zip]),ALLEXCEPT(Table,Table[LoadNum]),Table[SeqNum]=_max)

Destination_Warehouse = var _max=MAX(Table[SeqNum]) return CALCULATE(MAX(Table[DestionationWarehouse]),ALLEXCEPT(Table,Table[LoadNum]),Table[SeqNum]=_max)

[Anonymous]

I beleive, it's mostly because the 'MAX' function isn't working as expected in this case.

[Anonymous]

Hi @Krutigawale33 , thanks for reverting, however the code doesn't seem to work as expected with the data as below above in the query.

[Jihwan_Kim]

Hi, @Anonymous 

Please try to create the below DAX measures.

But those still depend on the filter context. If those do not work, please kindly share the sample pbix file, then I can try to have a look and come up with a desirable solution.

Destination Zip =

VAR maxseqnum = MAX('DataTable'[SeqNum])

RETURN

CALCULATE( SELECTEDVALUE('DataTable'[DestinationZip]), 'DataTable'[SeqNum] = maxseqnum)

Destination Warehouse =

VAR maxseqnum = MAX('DataTable'[SeqNum])

RETURN

CALCULATE( SELECTEDVALUE('DataTable'[DestinationWarehouse]), 'DataTable'[SeqNum] = maxseqnum)

Jihwan Kim

If this post helps, then please consider accept it as the solution to help the other members find it more quickly.