Forum Discussion
Relationships & Visuals
Hi
In the report I'm creating I have the amount of new leavers and starters for each month.
When I select a particular month on a bar chart for example, I'd like for a table on the report to show the names of the specific employees who have left and started that month. (the names,etc are located within the Employee Master table).
I have the following relationships below, currently only those that are leavers are appearing (This is likely due to the relationship between the Datekey and MonthLeft).
Can you see anyway I can configure these relationships so that both the names of leavers and starters appear when a user selects a particular month?
Any help is much appreicated.
Thanks
Relationships
Hi Spencer,
Based on your description, I have some misunderstanding of your table structure(I store the name to the ‘leavers’ and ‘starters’).
Since you store the count value of records into the ‘leavers’ and ‘starters’, you can use CONCATENATEX function to achieve your requirement, below is a sample:Create a simple table with deptcode, monthjoin, monthleave, name
Use dax table formula to create two table DeptST,DeptLE
DeptST = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthJoined",[MonthJoined],"Starter",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthJoined]=EARLIER(Sheet1[MonthJoined])))))
DeptLE = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthLeft",[MonthLeft],"Leaver",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthLeft]=EARLIER(Sheet1[MonthLeft])))))
Write measure to get the specify name which in deptST and deptLE.Detail of Starter = var joindate= MAX([MonthJoined]) return
CONCATENATEX(FILTER(Sheet1,Sheet1[DeptCode]=VALUES(DeptST[DeptCode])&& Sheet1[MonthJoined]=joindate),[Name]&",")
Detail of Leaver = var leavedate= MAX([MonthLeft]) return
CONCATENATEX(FILTER(Sheet1,Sheet1[DeptCode]=VALUES(DeptLE[DeptCode])&& Sheet1[MonthLeft]=leavedate),[Name]&",")
Add calculate columns to store and display them.
Regards,
Xiaoxin Sheng
7 Replies
- I would create 1 fact table with leavers AND joiners. Make sure to have:
- 1 column with the date
- 1 column to indicate if it is a leaver or a joiner (optionally by adding a dimension table)
- 1 column with the name of the employee (optionally by adding a dimension table)
That should imo be enough to show the amount of leavers/stayers within any given month. Maybe add a time slicer? Hi Spencer,
>>Can you see anyway I can configure these relationships so that both the names of leavers and starters appear when a user selects a particular month?
You can use a slicer to filter data.(Make sure you have the relationship from ‘DateTable’ to ‘DeptST’ and ‘DeptLE’)
Add a Slicer visual and two table visuals.(I have disabled the relationship from ‘DateTable’ to ‘Employee Master’ to remove the effect from ‘Employee Master’ table)
Result:
Regards,
Xiaoxin Sheng
Hi Anonymous
Thankyou very much for your response. The only thing your process dosen't give is the names of each employee in either the DeptLE or DeptST table. The Full Names are located in the Employee Master table. Is there anyway to have those appear when slicing?
Thanks
