Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return a value from a specific row

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!

  • jeroendekker's avatar
    jeroendekker
    6 years ago

    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

3 Replies

  • jeroendekker's avatar
    jeroendekker
    Frequent Visitor
    6 years ago

    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's avatar
    mhossain
    Solution Sage
    6 years ago

    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 🙂
  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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