if there is a value listed within a predetermined set use alternate value

[dataj]

Hi there,

i have a group of families for a program and I am trying to assingn items based on the age of the person. however there cannot be suplicate Adult values. basically if adult 1 already was assigned within the family then it need to assign Adult 2. each family is represented by thier atendee number and every famnily hs 6 lines associated with them.

for example 6166 has six line. they are all one family made up of one adult, 3 school age children, a preschooler, and a toddler. 

however  6167 is a fmaily of 3 two adults and an infant. the first adult should be listed as  Adult 1 but since there is already an Adult associated with that attendee number i would like the second adult to be Adult 2.

here is the formula I used for the item column:

if [Contact Age] <1 then "Infant" else if [Contact Age] >=1 and [Contact Age] <2 then "Toddler" else if [Contact Age] >=2 and [Contact Age] <5 then "Pre-School" else if [Contact Age]>=5 and [Contact Age] <12 then "School-Age" else if [Contact Age] >=12 and [Contact Age] <18 then "Adolecent" else if[Contact Age] >=18 then "Adult 1" else "null"

Attendee Number	Contact Age	Item
AN-00006166	37.2	Adult 1
AN-00006166	10.5	School-Age
AN-00006166	8.9	School-Age
AN-00006166	5.1	School-Age
AN-00006166	3.3	Pre-School
AN-00006166	1.7	Toddler
AN-00006167	25.2	Adult 1
AN-00006167	27.3	Adult 1
AN-00006167	0.4	Infant
AN-00006167	null
AN-00006167	null
AN-00006167	null

[Zubair_Muhammad · Accepted solution]

@dataj 

Could you use DAX calculated column?

Column =
SWITCH (
    TRUE (),
    ISBLANK ( [Contact Age] ), BLANK (),
    [Contact Age] < 1, "Infant",
    [Contact Age] >= 1
        && [Contact Age] < 2, "Toddler",
    [Contact Age] >= 2
        && [Contact Age] < 5, "Pre-School",
    [Contact Age] >= 5
        && [Contact Age] < 12, "School-Age",
    [Contact Age] >= 12
        && [Contact Age] < 18, "Adolecent",
    [Contact Age] >= 18, "Adult "
        & RANKX (
            FILTER (
                Table1,
                [Attendee Number] = EARLIER ( [Attendee Number] )
                    && [Contact Age] >= 18
            ),
            [Contact Age],
            ,
            ASC,
            DENSE
        ),
    "null"
)

[dataj]

@Zubair_Muhammad Thanks That seems to have worked!

[dataj]

Thanks! I tried to use this but is ee there is a table. what is in that table? is it the attendee numbers?