Forum Discussion

user16940356's avatar
user16940356
Regular Visitor
2 years ago
Solved

Dynamically Count Distinct Individuals in Age Groups

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

IDService Date
A1/1/2021
A2/1/2021
A5/1/2021
B2/1/2021
B3/1/2021
C3/1/2021
C4/1/2021
C5/1/2021
D2/1/2021

 

 

2. ClientTable

IDDOB
A4/1/2003
B2/15/1956
C3/15/1956
D1/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:

AgeCatCount
<180
18-641
65 +

2

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

 

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

AgeCatCount 
<181(ID A)
18-642(ID C,D)
65 +1(ID B)

 

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

AgeCatCount 
<180 
18-642(ID A,D)
65 +2(ID B,C)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • user16940356's avatar
    user16940356
    Regular Visitor
    2 years ago

    Thanks, Anonymous ! 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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.