cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
belvoir99
Helper I
Helper I

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!

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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] )

11053.jpg

Best Regards,

Community Support Team _ Jing Zhang

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

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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] )

11053.jpg

Best Regards,

Community Support Team _ Jing Zhang

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

View solution in original post

@v-jingzhang - thanks so much! Works perfectly! 😀

harshnathani
Super User III
Super User III

Hi @belvoir99 ,

 

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

 

Regards,

HN

hi @harshnathani 

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

 

Thanks.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors