Forum Discussion

belvoir99's avatar
belvoir99
Icon for Resolver III rankResolver III
5 years ago
Solved

Calculated column based maximum value of another column for a set of data

StudentScoreTermYearClassCalendarYearLatestYearNewYear
John311Swallows202020202
James212Swallows202020202
Jane412Blackbird202020203
Mary313Blackbird202020203
John421Swallows202020202
James222Swallows202020202
Jane522Blackbird202020203
Mary323Blackbird202020203
John311Swallows201920201
James212Swallows201920202
Jane412Blackbird201920202
Mary313Blackbird201920203

There are 4 pupils: John and James are in the same class Swallows but not in the same Year. In other words, the Swallows class contains two Years.

Similarly, Jane & Mary are in the Blackbirds class but in Years 2 and 3 respectively.

I need a NewYear column, in a more complex example, whereby we put all students into the 'higher' class but only if the CalendarYear is the LatestYear

So, in 2020 Calendar Year, John is moved from Year 1 to Year 2 and Jane is moved from Year 2 to Year 3.

I need a column as it is going on the X axis of a bar chart.

I can't use Power Query as the Class is selected on a Slicer (at least I think that is right).

Thus, (I think!) I want to return the maximum value of the Year for that Class if the CalendarYear = LatestYear, otherwise just return the Year

I’ve tried various combinations of MAX, MAXX, FILTER and EARLIER but have got completely stuck!

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi belvoir99 

    Please try the DAX code below.

    Column = 
VAR class = Students[Class]
VAR maxYear = MAXX ( FILTER ( Students, Students[Class] = class ), Students[Year] )
RETURN
    IF ( Students[CalendarYear] = Students[LatestYear], maxYear, Students[Year] )

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi belvoir99 

    Please try the DAX code below.

    Column = 
VAR class = Students[Class]
VAR maxYear = MAXX ( FILTER ( Students, Students[Class] = class ), Students[Year] )
RETURN
    IF ( Students[CalendarYear] = Students[LatestYear], maxYear, Students[Year] )

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi belvoir99 ,

     

    Can you share the expected output column too in the dataset shared

     

    Regards,

    HN

    • belvoir99's avatar
      belvoir99
      Icon for Resolver III rankResolver III
      5 years ago

      hi Anonymous 

      It's the last column on the right called NewYear which I want to create. Hope that helps.

       

      Thanks.