Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
belvoir99
Resolver I
Resolver 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.

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

harshnathani
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.