Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to show a table with active employees based on the year. Someone provided me with a userelationship measure to get the terminated employees to show.
e.g.
I was wondering if it was possible to do the same but with the employees start and end date so that the table only shows employees who were active during that year?
Thanks
Hi @DynamicsHS ,
Is there any relationship between the table Date and cdm_worker? If yes, could you please provide the relationship info(Relationship field,Cardinality, Cross filter direction, active or inactive etc.)? Assume that you have the data model as below screenshot, you can create a measure as below to get the number of active employees.
Terminated within =
VAR _seldate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( cdm_worker[Worker ID] ),
FILTER (
'cdm_worker',
'cdm_worker'[cdm_worker (2).Start Date] <= _seldate
&& OR (
'cdm_worker'[cdm_worker (2).End Date] >= _seldate,
ISBLANK ( 'cdm_worker'[cdm_worker (2).End Date] )
)
)
)
In addition, you can refer the following links to get it:
How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX
Create inactive relationships
Create measure
Create visuals
Count Amount of Active Employees by period
Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell
Best Regards
Hi @v-yiruan-msft & @tamerj1 ,
Currently I have multiple relationships running from the cdmworker table to the date table. More specifically, a relationship for the start and end date of the employees:
I am currently using this bar chart:
Which is pulling 3 dax measures:
Count of Active Employee - female =
VAR endOfPeriod = MAX ( 'Date'[Date] )
VAR startOfPeriod = MIN( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( cdm_worker ),
FILTER (
ALL(cdm_worker),
( cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentstartdate] <= endOfPeriod
&& cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentenddate] >= startOfPeriod && cdm_worker[cdm_worker (2).cdm_gendername] = "female")
)
)
Count of Active Employee - unspecified =
VAR endOfPeriod = MAX ( 'Date'[Date] )
VAR startOfPeriod = MIN( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( cdm_worker ),
FILTER (
ALL(cdm_worker),
( cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentstartdate] <= endOfPeriod
&& cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentenddate] >= startOfPeriod && cdm_worker[cdm_worker (2).cdm_gendername] = "unspecified")
)
)
Count of Active Employee - male =
VAR endOfPeriod = MAX ( 'Date'[Date] )
VAR startOfPeriod = MIN( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( cdm_worker ),
FILTER (
ALL(cdm_worker),
( cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentstartdate] <= endOfPeriod
&& cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentenddate] >= startOfPeriod && cdm_worker[cdm_worker (2).cdm_gendername] = "male")
)
)
I have a table below this bar chart which has the employee names. However, it only provides employees who have been hired within those date ranges. For example we hired one employee in July, so when I click July on the bar chart it only shows me that one employees name in the table below rather than all 106 employees that were active - with the employee who was currently hired.
Regards,
Henry
Hi @DynamicsHS
In this case you need to do the oppsite. You need to remove the filter from the date table and then iterate ove the table to manually count the number of employees that are active in the selected 'Date'[Date] in the filter context. Something like:
=
CALCULATE (
SUMX (
Payroll,
IF (
Payroll[Start Date] <= MAX ( 'Date'[Date] )
&& Payroll[End Date] >= MAX ( 'Date'[Date] ),
1
)
),
REMOVEFILTERS ( 'Date' )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |