Count of Employees Based On Dynamic Age

[ubaid2001]

I have Employees data in Power BI to calculate the count of employees by age group, I have created measure to calculate the age dynamically based on selected date against each employee ID

Age = DATEDIFF(MAX('fact_employee_history'[DOB]), MAX(dim_calendar[Date]), YEAR)

and then I have calculated the age group using this dax

AgeGroup =

VAR Age = [Age]

RETURN

    SWITCH(

        TRUE(),

        Age <= 30, "18-30",

        Age >= 31 && Age <= 40, "31-40",

        Age >= 41 && Age <= 50, "41-50",

        Age >= 51 && Age <= 60, "51-60",

        Age > 60, "60 above (other)",

        BLANK()

    ).

Now I want to get the count of employees for each group instead of each Employee ID.

The final output will look like this

[Anonymous]

Hi @ubaid2001 ,

Please create a new table like:

And a new measure like:

Count = 
VAR _table = SUMMARIZE('Table','Table'[EmployeeID],'Table'[Age],"group",[AgeGroup])
VAR _cur_age_group = MAX('Age Groups'[Age Groups])
VAR _result = COUNTROWS(FILTER(_table,[group]=_cur_age_group))
RETURN
_result

Output:

The result returned by the measure is dynamically calculated, which should work.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[ubaid2001]

Hi,

I have Employee ID in table, but age and age group both are measures, can you please help me with that.

Age Measure = DATEDIFF(MAX('fact_employee_history'[DOB]), MAX(dim_calendar[Date]), YEAR)

AgeGroup Measure =

VAR Age = [Age]

RETURN

    SWITCH(

        TRUE(),

        Age <= 30, "18-30",

        Age >= 31 && Age <= 40, "31-40",

        Age >= 41 && Age <= 50, "41-50",

        Age >= 51 && Age <= 60, "51-60",

        Age > 60, "60 above (other)",

        BLANK()

    ).

Thanks

[Anonymous · Accepted solution]

Hi @ubaid2001 ,

Please try:

Count = 
VAR _table = SUMMARIZE('Table','Table'[EmployeeID],"group",[AgeGroup])
VAR _cur_age_group = MAX('Age Groups'[Age Groups])
VAR _result = COUNTROWS(FILTER(_table,[group]=_cur_age_group))
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Uzi2019]

Hi @ubaid2001 
You can simply take that column twice (age group) and set other age group to count like below. In my case sorting is my calculated column.

I hope i answered your question!

[ubaid2001]

I am not able to get the count of Age Group measure

[Uzi2019]

Hi @ubaid2001 
Please calculate the Age group in Column not measure.

[ubaid2001]

Once I am calculating it in column then it is not changing the group based on age dynamically.

Age measure is calculating the age dynamically based on selected date.

[Ahmedx]

You'll create a calculated column based on your measure. and then just create a new measure using DISTINCTCOUNT

[ubaid2001]

can you please explain your solution in detail.

Thanks