Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Spencer
Helper II
Helper II

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

RelationshipsRelationships

1 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

Capture.PNG
 

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])))))

Capture2.PNG
 

DeptLE = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthLeft",[MonthLeft],"Leaver",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthLeft]=EARLIER(Sheet1[MonthLeft])))))

Capture3.PNG


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.

Capture4.PNGCapture5.PNG

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

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)

 Capture.PNG

Capture2.PNG

 

Capture3.PNG

 

Result:

 Capture4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

Hi Spencer,

 

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

You could drag the name column to the table visuals.

 

Relationship:

 Capture5.PNG

Capture.PNG

 

Table visual structure:

 Capture2.PNGCapture3.PNG

 

Result:

 Capture4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

RelationshipsRelationshipsIntended VisualIntended Visual

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

Capture.PNG
 

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])))))

Capture2.PNG
 

DeptLE = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthLeft",[MonthLeft],"Leaver",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthLeft]=EARLIER(Sheet1[MonthLeft])))))

Capture3.PNG


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.

Capture4.PNGCapture5.PNG

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Got it now, thanks for all you help!

SanderBeukers
Advocate I
Advocate I

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.