Dynamically Count Distinct Individuals in Age Groups

[user16940356]

Hello and thanks in advance for your help. I am looking for a way to be able to dynamically obtain the count of distinct individuals by age groups (<18, 18-64, 65+) based on their age at their most recent Service Date within a time period selected on a slicer. 

Ideally, the count for age categories should be a distinct count of 'ServiceTable'[ID].

I have two tables with the following information (plus a calendar table based on Service Date in ServiceTable) :

1. ServiceTable

ID	Service Date
A	1/1/2021
A	2/1/2021
A	5/1/2021
B	2/1/2021
B	3/1/2021
C	3/1/2021
C	4/1/2021
C	5/1/2021
D	2/1/2021

2. ClientTable

ID	DOB
A	4/1/2003
B	2/15/1956
C	3/15/1956
D	1/1/1981

(in this example, person A turns 18 in 2021, persons B & C turn 65 in 2021, person D turns 40 in 2021)

If a slicer were set for 3/1/21 - 5/1/21, I would expect to see the following:

AgeCat	Count
<18	0
18-64	1
65 +	2

(ID A = 18-64, IDs B&C = 65+)

For a slicer set to 1/1/21 - 3/1/21:

AgeCat	Count
<18	1	(ID A)
18-64	2	(ID C,D)
65 +	1	(ID B)

And for a slicer 1/1/21 - 5/1/21:

AgeCat	Count
<18	0
18-64	2	(ID A,D)
65 +	2	(ID B,C)

[user16940356]

Thanks, @v-kaiyue-msft ! Could you help me understand what you're doing? It looks like you're creating a new table joining ID + DOB to SD on ID = ID2; and then attributing months to the different age categories; then for each age category you're counting the number of individuals who fall into that category.

However, I'm not sure what 'Table'[Value] is supposed to reference, but it is giving me an error. I'm also wondering how this "knows" to select the most recent Service Date?

Appreciate your help!

[v-kaiyue-msft · Accepted solution]

Hi @user16940356 ,

I'm sorry I forgot to mention in my previous reply that table is the calculation table created which contains data for different age categories in order to count different clients.

CROSSJOIN (
SELECTCOLUMNS (
'ClientTable'.

"_DOB", 'ClientTable'[DOB]
),
SELECTCOLUMNS (
'ServiceTable'.

"_SD", 'ServiceTable'[SERVICE DATE]
)
)

This expression above returns the Cartesian product of all the rows of the ClientTable and ServiceTable. The columns in the resulting table are all the columns in both tables.

The purpose of adding a filter to the outer layer is to allow the two tables to be connected by ID, similar to a primary key.

The slicer holds the date from the serviceTable, and its filtering affects the result of the merge of the two tables, which in turn affects the overall count.

Here is the documentation of the function, hope it can help you:
CROSSJOIN function (DAX) - DAX | Microsoft Learn
SUMMARIZE function (DAX) - DAX | Microsoft Learn
SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[v-kaiyue-msft]

Hi @user16940356 ,

Create measure, write dax expression.

MEASURE = 
VAR _newvalue =
    SELECTEDVALUE ( 'Table'[Value] )
VAR _vtable =
    SUMMARIZE (
        SELECTCOLUMNS (
            FILTER (
                CROSSJOIN (
                    SELECTCOLUMNS (
                        'ClientTable',
                        "_ID", 'ClientTable'[ID],
                        "_DOB", 'ClientTable'[DOB]
                    ),
                    SELECTCOLUMNS (
                        'ServiceTable',
                        "_ID2", 'ServiceTable'[ID],
                        "_SD", 'ServiceTable'[Service Date]
                    )
                ),
                [_ID] = [_ID2]
            ),
            [_ID],
            "_Age",
                IF (
                    DATEDIFF ( [_DOB], [_SD], MONTH ) < 216,
                    "<18",
                    IF (
                        DATEDIFF ( [_DOB], [_SD], MONTH ) >= 216
                        && DATEDIFF ( [_DOB], [_SD], MONTH ) < 780,
                        "18-64",
                        IF ( DATEDIFF ( [_DOB], [_SD], MONTH ) >= 780, "65+" )
                    )
                )
        ),
        [_ID],
        [_Age]
    )
RETURN
    SWITCH (
        _newvalue,
        "<18", COUNTX ( FILTER ( _vtable, [_Age] = "<18" ), [_ID] ),
        "18-64", COUNTX ( FILTER ( _vtable, [_Age] = "18-64" ), [_ID] ),
        "65+", COUNTX ( FILTER ( _vtable, [_Age] = "65+" ), [_ID] )
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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