Matching values between 2 tables when using a date slicer

[James_Galis1]

Hello All
Hoping for some assistance on the following

I have 2 tables.

1, Employee 

2. Visits

The employee table is simply a list of all employees and the visit table contains all client visits attended by the employee's

What im trying to acheive is the following

When using the date slicer im hoping to show a list of employees from the employee table that have not had a visit within that date range.

Eg , I have for example an employee count of 200 on the employee table and if choose a date range  (24/4/23 - 30/4/23) from the visit table , i am hoping to see a list of employees that have visits in that date range and a list of employees that do not

The 2 tables have a relationship from a client ID.

Would appreciate the assistance

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.

[James_Galis1]

Apologies Ashish

Here is a small sample size of data from 2 tables

 Visit table.

Client ID	Visit Start Date	Visit Visit ID	Employee ID
AC000802186	2023-04-24	1516014	AC001971161
AC003647289	2023-04-24	1609546	AC003697811
AC000865524	2023-04-24	1591665	AC003388602
AC000061390	2023-04-24	1609253	AC000008651
AC001607353	2023-04-24	1442254	AC001432971
AC001570054	2023-04-24	1582205	AC000002261
AC001019508	2023-04-24	1541569	AC001400185
AC000804586	2023-04-24	1540108	AC000002251
AC002512224	2023-04-24	1612147	AC003649687
AC000747854	2023-04-24	1611079	AC000013024
AC001379982	2023-04-24	1610968	AC000002293
AC000808603	2023-04-24	1442003	AC001971080
AC000000398	2023-04-24	1520678	AC002773931
AC001088333	2023-04-24	1443823	AC003697811
AC000000222	2023-04-24	1441325	AC000002279
AC000840309	2023-04-24	1578925	AC001432971
AC003092621	2023-04-24	1449251	AC001936818
AC000998309	2023-04-24	1508647	AC000012964
AC002294527	2023-04-24	1447635	AC000143289

Employee table

Employee ID	Has Visit	Job Title
AC003697811	Yes	Community Support Workers
AC003649687	Yes	Community Support Workers
AC003388602	Yes	Community Support Workers
AC002773931	Yes	Community Support Workers
AC001971080	Yes	Community Support Workers
AC001971161	Yes	Community Support Workers
AC001936818	Yes	Community Support Worker
AC001432971	Yes	Community Support Workers
AC001400185	Yes	Community Support Workers
AC000143289	Yes	Community Support Workers
AC000012964	Yes	Community Support Workers
AC000013024	Yes	Community Support Workers
AC000008651	Yes	Community Support Workers
AC000002293	Yes	Community Support Workers
AC000002251	Yes	Community Support Workers
AC000002239	No	Community Support Workers
AC000002279	Yes	Community Support Workers
AC000002261	Yes	Community Support Workers
AC000002243	No	Community Support Workers
AC000002231	No	Community Support Workers

Note: The " Has Visit" column is entered manually and example of what i hope to acheive when slicing from the visit table.

Obviously this would change when selecting the date range and depending on which empoyee has visit for the selected date range

Please let me know if more information is required

Thanks in advanced

[Ashish_Mathur]

Hi,

Your data does not seem reepesentative.  You have taken only 1 date - please take more dates as well.  Also, within the date range selected, what is an employee visited on a certain date and not on another.  In such a scenario, since in the result, the employee ID should appear only once, what should the result be - Yes or No.  As requested, please share a logical/resresentative example and show the expected result on that revised sample.  

[James_Galis1]

Hi

My  solution and im not sure if it can be acheived would be to dynamically show on the employee table if they had and didnt have visits when the date is sliced.

It would not matter if they had 1 visit in a 1,2 ,3 or x number of days. The result would expect to show " has visit".

Conversely, the result if they do not have a visit in a 1,2,3 or x number of days should show    "no visit".

My employee count is approx 160 and depending on how big or small the date slicer is i could expect for a 1 day range, 120 employees scheduled and 40 not scheduled. So , i want to be able to show which employees  had visits and which ones didnt in this date range.

Examples attached restricted to a select few  employees along with a select few visit on different dates. 

https://docs.google.com/spreadsheets/d/1aaWM9OeGhi7sPeoucvZ3UakpLM5_R-ugcFyJWwmij2E/edit?usp=sharing 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[James_Galis1]

Perfect

Exactly what i was looking for

[Ashish_Mathur]

You are welcome.

[James_Galis1]

Hi

Apologies for the length of this message 

My  solution and im not sure if it can be acheived would be to dynamically show on the employee table if they had and didnt have visits when the date is sliced.

It would not matter if they had 1 visit in a 1,2 ,3 or x number of days. The result would expect to show " has visit".

Conversely, the result if they do not have a visit in a 1,2,3 or x number of days should show    "no visit".

My employee count is approx 160 and depending on how big or small the date slicer is i could expect for a 1 day range, 120 employees scheduled and 40 not scheduled. So , i want to be able to show which employees  had visits and which ones didnt in this date range.

Examples below restricted to a select few  employees along with a select few visit on different dates. Below is an example of 25th ,26th and combination of visit dates 24,25 and 26

Visit for 25th.

AC000286989 2023-04-25 1450477 AC001400185
AC001284596 2023-04-25 1495467 AC001400185
AC001284596 2023-04-25 1619215 AC001936818
AC000857537 2023-04-25 1616233 AC001975471
AC001843806 2023-04-25 1611861 AC001432971
AC000001589 2023-04-25 1450091 AC001936818
AC002345385 2023-04-25 1451871 AC000002279
AC001109817 2023-04-25 1508387 AC001432971
AC000921649 2023-04-25 1578816 AC000012964
AC002058327 2023-04-25 1451580 AC001936818
AC000001617 2023-04-25 1450193 AC000002203
AC000001617 2023-04-25 1450196 AC000002203
AC000001345 2023-04-25 1463220 AC000002121
AC003056327 2023-04-25 1452584 AC000002231
AC000535555 2023-04-25 1451296 AC001400185
AC000001593 2023-04-25 1450221 AC000002231
AC000000092 2023-04-25 1451327 AC001936818
AC001678159 2023-04-25 1456236 AC000859223
AC001992729 2023-04-25 1451557 AC001936818

Employee table result for 25th

Employee ID Role Has Visit
AC003697811 Support Workers No
AC003649687 Support Workers No
AC003388602 Support Workers No
AC002773931 Support Workers No
AC001971080 Support Workers No
AC001971161 Support Workers No
AC001936818 Support Workers Yes
AC001432971 Support Workers Yes
AC001400185 Support Workers Yes
AC000143289 Support Workers No
AC000012964 Support Workers Yes
AC000013024 Support Workers No
AC000008651 Support Workers No
AC000002293 Support Workers No
AC000002251 Support Workers No
AC000002239 Support Workers No
AC000002279 Support Workers Yes
AC000002261 Support Workers No
AC000002243 Support Workers No
AC000002231 Support Workers Yes

Visit for 26th

Client ID Visit Date Visit ID Employee ID
AC002251827 2023-04-26 1451748 AC001971161
AC002335126 2023-04-26 1523569 AC000002231
AC000043944 2023-04-26 1450324 AC001400185
AC002909733 2023-04-26 1569064 AC002385691
AC001658318 2023-04-26 1451664 AC002385691
AC000001459 2023-04-26 1611854 AC001432971
AC000622688 2023-04-26 1450683 AC000012964
AC000006312 2023-04-26 1630998 AC003697811
AC001840094 2023-04-26 1590019 AC001975471
AC001603554 2023-04-26 1451409 AC001971161
AC000000229 2023-04-26 1450104 AC000012964
AC001114094 2023-04-26 1611328 AC002385691
AC001227935 2023-04-26 1451188 AC001975471
AC003208808 2023-04-26 1616874 AC001400185
AC000001839 2023-04-26 1542616 AC000002203
AC002261574 2023-04-26 1451847 AC001936818
AC000286989 2023-04-26 1450475 AC001400185
AC000692502 2023-04-26 1573875 AC001971161
AC001284596 2023-04-26 1619236 AC001936818

Employee table results for 26th

Employee ID Role Has Visit
AC003697811  Support Workers Yes
AC003649687  Support Workers No
AC003388602  Support Workers No
AC002773931  Support Workers No
AC001971080  Support Workers No
AC001971161  Support Workers Yes
AC001936818  Support Workers Yes
AC001432971  Support Workers Yes
AC001400185  Support Workers Yes
AC000143289  Support Workers No
AC000012964  Support Workers Yes
AC000013024  Support Workers No
AC000008651  Support Workers No
AC000002293  Support Workers No
AC000002251  Support Workers No
AC000002239  Support Workers No
AC000002279  Support Workers No
AC000002261  Support Workers No
AC000002243  Support Workers No
AC000002231  Support Workers Yes

Visits for 24,25 and 26

Client ID Visit Date Visit ID Employee ID
AC000802186 2023-04-24 1516014 AC001971161
AC003647289 2023-04-24 1609546 AC003697811
AC000865524 2023-04-24 1591665 AC003388602
AC000061390 2023-04-24 1609253 AC000008651
AC001607353 2023-04-24 1442254 AC001432971
AC001570054 2023-04-24 1582205 AC000002261
AC001019508 2023-04-24 1541569 AC001400185
AC000804586 2023-04-24 1540108 AC000002251
AC002512224 2023-04-24 1612147 AC003649687
AC000747854 2023-04-24 1611079 AC000013024
AC001379982 2023-04-24 1610968 AC000002293
AC000808603 2023-04-24 1442003 AC001971080
AC000000398 2023-04-24 1520678 AC002773931
AC001088333 2023-04-24 1443823 AC003697811
AC000000222 2023-04-24 1441325 AC000002279
AC000840309 2023-04-24 1578925 AC001432971
AC003092621 2023-04-24 1449251 AC001936818
AC000998309 2023-04-24 1508647 AC000012964
AC002294527 2023-04-24 1447635 AC000143289
AC000286989 2023-04-25 1450477 AC001400185
AC001284596 2023-04-25 1495467 AC001400185
AC001284596 2023-04-25 1619215 AC001936818
AC000857537 2023-04-25 1616233 AC001975471
AC001843806 2023-04-25 1611861 AC001432971
AC000001589 2023-04-25 1450091 AC001936818
AC002345385 2023-04-25 1451871 AC000002279
AC001109817 2023-04-25 1508387 AC001432971
AC000921649 2023-04-25 1578816 AC000012964
AC002058327 2023-04-25 1451580 AC001936818
AC000001617 2023-04-25 1450193 AC000002203
AC000001617 2023-04-25 1450196 AC000002203
AC000001345 2023-04-25 1463220 AC000002121
AC003056327 2023-04-25 1452584 AC000002231
AC000535555 2023-04-25 1451296 AC001400185
AC000001593 2023-04-25 1450221 AC000002231
AC000000092 2023-04-25 1451327 AC001936818
AC001678159 2023-04-25 1456236 AC000859223
AC001992729 2023-04-25 1451557 AC001936818
AC002251827 2023-04-26 1451748 AC001971161
AC002335126 2023-04-26 1523569 AC000002231
AC000043944 2023-04-26 1450324AC001400185

AC002909733 2023-04-26 1569064 AC002385691
AC001658318 2023-04-26 1451664 AC002385691
AC000001459 2023-04-26 1611854 AC001432971
AC000622688 2023-04-26 1450683 AC000012964
AC000006312 2023-04-26 1630998 AC003697811
AC001840094 2023-04-26 1590019 AC001975471
AC001603554 2023-04-26 1451409 AC001971161
AC000000229 2023-04-26 1450104 AC000012964
AC001114094 2023-04-26 1611328 AC002385691
AC001227935 2023-04-26 1451188 AC001975471
AC003208808 2023-04-26 1616874 AC001400185
AC000001839 2023-04-26 1542616 AC000002203
AC002261574 2023-04-26 1451847 AC001936818
AC000286989 2023-04-26 1450475 AC001400185
AC000692502 2023-04-26 1573875 AC001971161
AC001284596 2023-04-26 1619236 AC001936818

Employee table results for 24,25 and 26

Employee ID     Role                      Has visit
AC003697811  Support Workers  Yes
AC003649687  Support Workers  Yes
AC003388602  Support Workers  Yes
AC002773931  Support Workers  Yes
AC001971080  Support Workers Yes
AC001971161  Support Workers  Yes
AC001936818  Support Workers  Yes
AC001432971  Support Workers  Yes
AC001400185  Support Workers  Yes
AC000143289  Support Workers  Yes
AC000012964  Support Workers  Yes
AC000013024  Support Workers  Yes
AC000008651  Support Workers  Yes
AC000002293  Support Workers  Yes
AC000002251  Support Workers  Yes
AC000002239  Support Workers  No
AC000002279  Support Workers  Yes
AC000002261  Support Workers  Yes
AC000002243  Support Workers  No
AC000002231  Support Workers  Yes