Forum Discussion

Richard77's avatar
Richard77
Resolver I
3 years ago
Solved

Getting first year based on text column

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?
Need Help
  • vanessafvg's avatar
    vanessafvg
    3 years ago

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

4 Replies

  • Richard77's avatar
    Richard77
    Resolver I
    3 years ago

    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's avatar
      vanessafvg
      Community Champion
      3 years ago

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