Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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" )
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" )
Thanks! I tried to use this but is ee there is a table. what is in that table? is it the attendee numbers?
User | Count |
---|---|
123 | |
111 | |
99 | |
60 | |
60 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |