Dynamic data in a calculated column based on row count.

[nhale777]

I have a calculated column in one one of my tables that looks at an individuals age and then assigns them into a specific age grouping.  Very simple but I need to know based on the current company and date range selected on my report if any of the age groups will have less than a certain number of individuals,  if they do then I need to broaden the age grouping.  Not sure how to accomplish this in DAX.   Here is the current code,  any help is much appreciated.  Thanks.

IF (

Value([Age]) >= 60,

"60 & up",

IF (

Value([Age]) >= 55,

"55-59",

IF (

Value([Age]) >= 50,

"50-54",

IF (

Value([Age]) >= 45,

"45-49",

IF (

Value([Age]) >= 40,

"40-44",

IF (

Value([Age]) >= 35,

"35-39",

IF (

Value([AGE]) >= 30,

"30-34", "< 30" )

)

)

)

)

)

)

[v-yanjiang-msft · Accepted solution]

Hi @nhale777 ,

According to your description, I create a sample table:

You want to know if any of the age groups will have less than a certain number of individuals, here's my solution:

Create a group table containing all the groups.

Then create a column to calculate the number of individuals belonging to each group:

Column =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Age]
            >= LEFT (
                [Group],
                SEARCH ( "&", [Group],, SEARCH ( "-", [Group],, SEARCH ( "<", [Group],, 0 ) ) ) - 1
            )
            && 'Table'[Age]
                < RIGHT (
                    [Group],
                    LEN ( [Group] )
                        - SEARCH ( "&", [Group],, SEARCH ( "-", [Group],, SEARCH ( "<", [Group],, 0 ) ) )
                )
    )
)

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.