Forum Discussion
How to make active date table relationship with two tables?
Hi
I have two table as follow
1) Employee Master table
Employee Master table has all the employee records who hired date, status, department and employee id. It starts from 1989 to 2023, where employees joined on each year
2) Timesheet table
Timesheet table is an attendance table where it records all the employees intime daily which starts from 2021 to 2023
I calculated below measures
Total Active Employees = CALCULATE(DISTINCTCOUNT('Employee Master'[EID]), 'Employee Master'[Status] = "Active")
Cumulative Total Employees =
CALCULATE([Total Active Employees],
FILTER(ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
I have created date table by taking min and max hire date from employee table using dax.
All the three table has connected as follow
I wanted to visualize 1) department vs Cumulative total active employees 2) Department vs count of Intime (from timesheet table) as side by side bar graph or stacked bar graph with one date slicer.
Date slicer I would like to use is Intime date from timesheet table. however, its not working for department vs cumilative total employees. I tried to connect the date table with time sheet table but it's coming in active relationship
Can anyone advise how to make a uniform date slicer for the above two graphs? I am trying to solve this for a long time and not able to acheive this.
attached pbix file for your reference https://we.tl/t-IkzsaCTgdw
1 Reply
- lbendlin
Super User
This would be a slightly better data model
but in your scenario you may have to use separate calendar tables. Not sure what you are trying to achieve with the chart on the right.