topic Return a value from a specific row in DAX Commands and Tips

Return a value from a specific row

Anonymous — Tue, 25 Aug 2020 21:07:18 GMT

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!

Re: Return a value from a specific row

mhossain — Tue, 25 Aug 2020 21:58:42 GMT

Anonymous

Try this

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

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

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

Re: Return a value from a specific row

jeroendekker — Tue, 25 Aug 2020 22:02:38 GMT

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

Re: Return a value from a specific row

mahoneypat — Tue, 25 Aug 2020 22:35:02 GMT

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