group by a measure column?

kc_holman — Fri, 11 Aug 2023 09:01:31 GMT

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

Re: group by a measure column?

Sahir_Maharaj — Sun, 13 Aug 2023 17:53:04 GMT

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.

topic group by a measure column? in DAX Commands and Tips

group by a measure column?

Re: group by a measure column?