Find Max Value in Table based on value in another column

[vincenardo]

I want to find the max value for each name.

Here is a sample of my data;

I added tried using this formula in a calculated column (MAX LEVEL) -

MAX LEVEL = MAXX(FILTER('HC-1 INSTALL PBI Service Resources with Skill','HC-1 INSTALL PBI Service Resources with Skill'[Name]='HC-1 INSTALL PBI Service Resources with Skill'[Name]),'HC-1 INSTALL PBI Service Resources with Skill'[Installer Skill Name Lvl])

But that formula is finding the max value for ALL the rows and I want to filter based on the NAME.

For Example, for Ahmad Mohammadi above, the max value should be 3.

Thanks in advance!

[AlB]

@vincenardo 

or alternatively, leveraging context transition:

MAX LEVEL =
CALCULATE (
    MAX ( 'HC-1 INSTALL PBI Service Resources with Skill'[Installer Skill Name Lvl] ),
    ALLEXCEPT (
        'HC-1 INSTALL PBI Service Resources with Skill',
        'HC-1 INSTALL PBI Service Resources with Skill'[Name]
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @vincenardo 

MAX LEVEL =
MAXX (
    FILTER (
        'HC-1 INSTALL PBI Service Resources with Skill',
        'HC-1 INSTALL PBI Service Resources with Skill'[Name]
            = EARLIER ( 'HC-1 INSTALL PBI Service Resources with Skill'[Name] )
    ),
    'HC-1 INSTALL PBI Service Resources with Skill'[Installer Skill Name Lvl]
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.