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 r...
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.

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.