Relationships & Visuals

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