Forum Discussion
Getting first year based on text column
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" )
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'.
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" )Yes, this does the trick! Thank you very much vanessafvg