Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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 IDAmount ChosenCustomer IDDesired OutputRow Notes
1100100 because it's not most recent choice for the customer
225100 because it did not complete an order indicated by the confirmation number (always "123456789")
30100 because all zeros are never recorded as anything but 0
420200 because it's not most recent choice for the customer
50200 because all zeros are never recorded as anything but 0
62002200Recorded because the customer ID has a confirmation number and it's the last amount chosen indicated by Sequence ID
7123456789200 because all confirmation numbers are never recorded as anything but 0
850350Recorded because the customer ID has a confirmation number and it's the last amount chosen indicated by Sequence ID
9123456789300 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
v-chuncz-msft
Community Support
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.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.