How can I change groupings in DAx for Age Groups

[Karolina411]

Hello:  My issue is in SQL my ages seem to be grouping incorrectly:

Case when E.PatientAge between '0 years' and '17 years' AND
E.PatientAge between '0 month' and '204 months' AND
E.PatientAge between '0 days' and '1200 days' then 'Between 0 and 17 Years'
when E.PatientAge between '18 years' and '25 years' then 'Between 18 and 25 Years'
when E.PatientAge between '26 years 'and '40 years' then 'Between 26 and 40 Years'
when E.PatientAge between '41 years' and '65 years' then 'Between 41 Years and 65 Years'
when E.PatientAge between '66 years' and '80 years' then 'Between 66 Years and 80 Years'
when E.PatientAge >= '80 years' then 'More than 80 but less then 100 Years'
when E.PatientAge >= '100 years' then 'More than 100 Years'
Else E.PatientAge end as 'PatientAgeGroup'

So when I bring them into Power Bi they look like this below.  Is there a way to reassign the proper age in DAX?

[amitchandak · Accepted solution]

@Karolina411 , PatientAge  should ideally be in numbers.

You can use switch true

Example

Switch(True(),

[PatientAge] >=  '0 years'  && [PatientAge] <= '17 years' &&
[PatientAge] >= '0 month' && [PatientAge] <= ' '204 months' &&

[PatientAge] >= '0 days'  && [PatientAge] <= '1200 days' , "Between 0 and 17 Years"

// Add other condition

)

Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56

[Anonymous · Accepted solution]

HI @Karolina411,

You can try to use the following calculated column formulas if helps: ( I add the dynamic conditions and offset units to handle different units of values)

formula =
VAR currPatientAge = Table[PatientAge]
VAR interval = 15
VAR AgeValue =
    VALUE ( PATHITEM ( SUBSTITUTE ( currPatientAge, " ", "|" ), 1 ) )
VAR yearFlag =
    SEARCH ( "year", currPatientAge, 1 ) > 0
VAR MonthFlag =
    SEARCH ( "month", currPatientAge, 1 ) > 0
VAR dayFlag =
    SEARCH ( "day", currPatientAge, 1 ) > 0
VAR offset =
    IF ( yearFlag, 1, IF ( MonthFlag, 12, IF ( dayFlag, 365 ) ) )
VAR age = AgeValue / offset
RETURN
    IF (
        age <= 25,
        IF ( age <= 17, "0 ~ 17", "18 ~ 25" ),
        IF (
            age >= 100,
            ">100",
            IF (
                age >= 80,
                "80 ~ 100",
                VAR multiplier =
                    INT ( DIVIDE ( age - 25, interval ) )
                RETURN
                    IF (
                        age > 25 + multiplier * interval
                            && age <= 25 + ( multiplier + 1 ) * interval,
                        ( 25 + multiplier * 15 + 1 ) & "~" & ( 25 + ( multiplier + 1 ) * 15 )
                    )
            )
        )
    )

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Karolina411 , PatientAge  should ideally be in numbers.

You can use switch true

Example

Switch(True(),

[PatientAge] >=  '0 years'  && [PatientAge] <= '17 years' &&
[PatientAge] >= '0 month' && [PatientAge] <= ' '204 months' &&

[PatientAge] >= '0 days'  && [PatientAge] <= '1200 days' , "Between 0 and 17 Years"

// Add other condition

)

Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56

[Karolina411]

Thank you; I plan to use Swith/True more often but I need to move them to the groupings in the other column ; I may just have to waste time to use groupings.