cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Finding latest value using Sequence ID and grouped by Customer ID

We currently have a form where people can select a desired amount. I would like to record this amount, but people can change their answer on the form before it is submitted. This means that every amount selection is recorded, but I need to find the last one chosen. I also have to group/reset the last amount for every customer. Additionally, I have to only record those who have completed the task (indicated with a confirmation number at the end of the session). Below is what the table would look like:

 Sequence ID Amount Chosen Customer ID Desired Output Row Notes 1 100 1 0 0 because it's not most recent choice for the customer 2 25 1 0 0 because it did not complete an order indicated by the confirmation number (always "123456789") 3 0 1 0 0 because all zeros are never recorded as anything but 0 4 20 2 0 0 because it's not most recent choice for the customer 5 0 2 0 0 because all zeros are never recorded as anything but 0 6 200 2 200 Recorded because the customer ID has a confirmation number and it's the last amount chosen indicated by Sequence ID 7 123456789 2 0 0 because all confirmation numbers are never recorded as anything but 0 8 50 3 50 Recorded because the customer ID has a confirmation number and it's the last amount chosen indicated by Sequence ID 9 123456789 3 0 0 because all confirmation numbers are never recorded as anything but 0

This is the formula I was trying to use in the new column, but I still can't figure out how to keep it all grouped by Customer ID:

Column =
//returns a text as a number
VALUE(
//Returns the "Amount Chosen" for the "Sequence ID" with the value indicated below in the Maxx function
LOOKUPVALUE (
//Result Column
'Table'[Amount Chosen],
//Search Column
'Table'[Sequency ID],
//Search Value
//Returns the max value in a table (I need it be "Customer ID")
MAXX (
//Makes "Customer ID" a value regardless of filters in the calculate table
//Does not group/constrain the max to the "Customer ID" level
KEEPFILTERS ( VALUES ( 'Table'[Customer ID] ) )
//Finds max "Sequence ID" that doesn't have confirmation number (123456789) or 0
, CALCULATE ( MAX ( 'Table'[Sequence ID] )
, FILTER('Table', 'Table'[Amount Chosen] <> 123456789)
, FILTER('Table', 'Table'[Amount Chosen] <> 0)
))))

//Multiplies the max amount that isn't a 0 or confirmation number by 1 and everything else by 0
* MAXX('Table',if('Table'[Amount Chosen] = 123456789,1,0))

The end goal would be to be able to sum them all without having to break it down by Customer ID in the visual (ie: I don't want to have a separate line per Customer ID in a table)

Any help would be greatly appreciated.

Thank you.

1 ACCEPTED SOLUTION
Community Support

@Anonymous,

You may refer to the DAX below.

```Column =
IF (
RANKX (
FILTER (
'Table',
'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
&& 'Table'[Amount Chosen] <> 123456789
),
'Table'[Sequence ID],
,
DESC
)
= 1
&& 'Table'[Amount Chosen] <> 123456789,
'Table'[Amount Chosen],
0
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

@Anonymous,

You may refer to the DAX below.

```Column =
IF (
RANKX (
FILTER (
'Table',
'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
&& 'Table'[Amount Chosen] <> 123456789
),
'Table'[Sequence ID],
,
DESC
)
= 1
&& 'Table'[Amount Chosen] <> 123456789,
'Table'[Amount Chosen],
0
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.