Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dataj
Regular Visitor

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

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 NumberContact AgeItem
AN-0000616637.2Adult 1
AN-0000616610.5School-Age
AN-000061668.9School-Age
AN-000061665.1School-Age
AN-000061663.3Pre-School
AN-000061661.7Toddler
AN-0000616725.2Adult 1
AN-0000616727.3Adult 1
AN-000061670.4Infant
AN-00006167null 
AN-00006167null 
AN-00006167null 
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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"
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@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"
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad Thanks That seems to have worked!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.