Getting first year based on text column

[Richard77]

Hi all, I've been browsing the forum for a while now but haven't found a solution (nor have I been capable of adapating solutions offered on other topics so that it helps me out), so I hope you can help me out.

I have a table [production mbo_membership] with a registration of members per year. Now I want to calculate the number of members in the specific years they have been linked to project years. This I managed by some tricks resulting in a calculated column that shows the text "MAX" whenever the number of members is the highest in years linked to projects. 

Max = VAR maxValue = calculate(max('Calculated table'[Members]), ALLEXCEPT('Calculated table', 'Calculated table'[executor_id])) return if('Calculated table'[Sum members]=maxValue, "MAX")

However, if the number of members in 2020 equals the number of members in 2021, it gives both years a "MAX". To avoid this, I need an indicator somewhere that makes sure only the first year is picked in that case, so 2020.

How can I do this? I would like to show this in a table visualization, so it is placed in a row context therefore was thinking of a calculated column but neither the options with allexcept nor filters have been successful.

What would be the solution here?

[Richard77]

Would this screenshot help? The 'id' is the unique row id, the 'mbo_id' is the applicable organisation. Highlighted is mbo_id 139, whereas both 2020 and 2021 receive the label 'MAX' as this is the maximum number of 'active_members'. mbo_id 7 shows it as expected as this id has a different number of active_members in 2020 compared to 2021. If they have even more members in 2022, then 2022 should be the max. My issue is thus, as an example, with mbo_id 139 and I need either one of the years with a 'MAX'.

[vanessafvg · Accepted solution]

the issue is once you have a max member numbers, you also next to get the max year then you can use that to get the max year.

Max Lookup =
VAR maxValue =
CALCULATE ( MAX ( data[Members] ), ALLEXCEPT ( data, data[ID] ) )
VAR maxyear =
CALCULATE (
MAX ( data[year] ),
ALLEXCEPT ( data, data[ID] ),
FILTER ( data, data[Members] = maxValue )
)
RETURN
IF ( data[Members] = maxValue && data[year] = maxyear, "MAX" )

[Richard77]

Yes, this does the trick! Thank you very much @vanessafvg 

[vanessafvg]

are you able to share some sample data?