Relationships & Visuals

[Spencer]

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

[v-shex-msft]

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

[Spencer]

Hi @v-shex-msft

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

[v-shex-msft]

Hi Spencer,

>>Is there anyway to have those appear when slicing?

You could drag the name column to the table visuals.

Relationship:

 

Table visual structure:

 

Result:

 

Regards,

Xiaoxin Sheng

[Spencer]

Hi Xiaoxin Sheng,

Thanks again for the response and apoligies for my slow reply. I wasn't able to create the relationships between the Employee Master and Dept tables as per your suggestion earlier.

I have since been able to reduce the number of tables in my model by creating measures that calculate the leavers and starters directly from the Employee Master table using the following functions:

Leavers = CALCULATE(COUNTROWS('Employee Master'),FILTER('Employee Master',([termination_date] <= LASTDATE(DateTable[DateKey]) && [termination_date]>=FIRSTDATE(DateTable[DateKey]))))

Starters = CALCULATE(COUNTROWS('Employee Master'),FILTER('Employee Master',([commence_date] <= LASTDATE(DateTable[DateKey]) && [commence_date]>=FIRSTDATE(DateTable[DateKey]))))

Unfortunately this still won't give me the names of the particular employees when I filter on the visual (see screenshot). How would you solve this under my revised set up? (I have tried activating a relationship between DateTable and Employee Master but haven't been able to achieve my desired result)

Huge thanks,

Spencer

RelationshipsIntended Visual

[v-shex-msft · Accepted solution]

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

[Spencer]

Got it now, thanks for all you help!

[SanderBeukers]

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?