Forum Discussion

vincenardo's avatar
vincenardo
Helper I
3 years ago
Solved

Find Max Value in Table based on value in another column

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!

 

 

Need Help
  • AlB's avatar
    AlB
    3 years ago

    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.

     

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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.

     

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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.