Return a value from a specific row

[bigascon]

I feel like this should be easy but I am having trouble.

pretty much what I have is a table with these values:

ChallengeID     Progress     UserCount

ABD-5FGD         1200               67

ABD-5FGD          457              150

ACG-HIFR           267                84

ACG-HIFR         3700                35

I want to create a new column that returns the userCount that is related to the maximum value of progress for that specific challengeID. In the case above the table should look like this:

ChallengeID     Progress     UserCount     NewColumn

ABD-5FGD         1200               67                  67

ABD-5FGD          457              150                  67

ACG-HIFR           267                84                  35

ACG-HIFR         3700                35                  35

I don't really even know where to start or if this is even possible. Help would be much appreciated. Thank you!

[mahoneypat]

Please try this column expression

NewColumn 2 =
VAR maxthisID =
CALCULATE (
MAX ( Challenge[Progress] ),
ALLEXCEPT ( Challenge, Challenge[ChallengeID] )
)
RETURN
CALCULATE (
MAX ( Challenge[UserCount] ),
ALLEXCEPT ( Challenge, Challenge[ChallengeID] ),
Challenge[Progress] = maxthisID
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jeroendekker · Accepted solution]

Hi Bigascon,

If you need it as a calculated column this should work. I am not sure if it is the fastest way. Maybe somebody has a faster way:)

Usercount max progress =
//calculate maxprogress for challengeid
VAR MAXPROGRESS =
    CALCULATE (
        MAX ( 'example'[Progress] ),
        FILTER (
            'example',
            'example'[ChallengeID] = EARLIER ( 'example'[ChallengeID] )
        )
    ) //calculate usercount for row that contains maxprogress (highest if 2 rows are equal in progress  
VAR USERCOUNT =
    CALCULATE (
        MAXX (
            FILTER ( 'example', 'example'[Progress] = MAXPROGRESS ),
            'example'[UserCount]
        ),
        ALLEXCEPT ( 'example', 'example'[ChallengeID] )
    )
RETURN
    USERCOUNT

[mhossain]

@bigascon 

Try this

NewColumn = CALCULATE(MAX('Table'[UserCount]),

ALLEXCEPT('Table','Table'[ChallengeID]))

Let me know if it helps, please change 'Table' to your table name 🙂