group by a measure column?

[kc_holman]

I have a table with just year column and because of row level security, some users will see different set of years than the others.

I need to add a measure that group the years into groups of 5s when order by years in ascending order.

I created a group_measure with roundup(rownumber/5,0) that groups them into 1s,2s,3s.. etc but I'm having problem using it to get the min(year) max(year) to get the GroupLabel. group_measure doesnt seem to be available in any aggregate/summarize/group function.

User1

Year    GroupLabel   group_measure 

1990    1990-2000          1

1992    1990-2000          1

1995    1990-2000          1

1997    1990-2000          1

2000    1990-2000          1

2001    2001-2015          2

2003    2001-2015          2

2006    2001-2015          2
2010    2001-2015          2
2015    2001-2015          2
2017    2017-2019          3
2018    2017-2019          3
2019    2017-2019          3

User2

Year    GroupLabel   group_measure 

1990    1990-2003          1

1992    1990-2003          1

2000    1990-2003          1

2001    1990-2003          1

2003    1990-2003          1
2017    2017-2022          2
2018    2017-2022          2
2021    2017-2022          2
2022    2017-2022          2

[Sahir_Maharaj]

Hello @kc_holman,

Can you please try:

GroupLabel = 
VAR CurrentYear = MAX('YourTable'[Year])
VAR GroupMeasure = MAX('YourTable'[group_measure])

VAR MinYearInGroup = MINX(FILTER('YourTable', 'YourTable'[group_measure] = GroupMeasure), 'YourTable'[Year])
VAR MaxYearInGroup = MAXX(FILTER('YourTable', 'YourTable'[group_measure] = GroupMeasure), 'YourTable'[Year])

RETURN
    CONCATENATE(
        MinYearInGroup,
        "-",
        IF(MaxYearInGroup - MinYearInGroup >= 4, MaxYearInGroup, MaxYearInGroup + 4)
    )

Should you require further assistance, please do not hesitate to reach out to me.