Count number of unique values from multiple tables

[frankhofmans]

hi PBI experts,

i have the following question:

i want to create a sick leave report and i have the following 2 tables (with > 100.000 rows):

Table 1: all employee's

EMPLOYEE ID	START CONTRACT	END CONTRACT
111	1-1-2020	1-8-2020
112	1-7-2020
113	1-9-2020	1-3-2021
114	1-10-2020
115	1-10-2020	1-9-2021
116	1-11-2020	01-12-2020
117	1-12-2020
118	1-4-2021
119	1-5-2021	1-10-2021
120	1-5-2021

When END CONTRACT is blank, an employee is still employed.

Table 2: all sickness cases

EMPLOYEE ID	START CASE	END CASE
111	1-3-2020	1-4-2020
114	1-2-2021	1-3-2021
117	1-1-2021	1-2-2021
120	1-9-2021	1-10-2021
114	1-5-2021	1-6-2021
114	1-9-2021	1-10-2021
117	1-4-2021	1-5-2021

I want to have the following result:

YEAR SELECTION: 2021

# of employee's with 0 sickness cases: 5 (empl. ID 112, 113, 115, 118, 119, they all have at least 1 working day in the selected period en no sickness cases in the selected period)

# of employee's with 1 sickness case: 1 (empl. ID 120)

# of employee's with 2 or more sickness cases: 2 (empl. ID 114 and 117)

If a deepdive to for example Q1 2021:

# of employee's with 0 sickness cases: 3 (empl. ID 118 and 119 were no active employee's in Q1)

# of employee's with 1 sickness case: 2 (empl. 114 and 117)

# of employee's with 2 or more sickness cases: 0 (empl. 114 and 117 only had 1 case in Q1)

Many thanks in advance!

Regards,

Frank

[speedramps]

Hi again frankhofmans 

This is a community forum. Volunteers like myself are keen to help with “how to” questions in return for kudos, whereas this it more akin to an entire system spec with mushrooming requirements. 😀😀😉

To be honest I derive a lot more pleasure volunteering to show Power BI novices how to do things rather than doing it for them. 🤔🤔🤔

You have got a lot of answer here. Please consider accepting the solution and raise 1 new ticket per additional question. You will get a better response and each helper gets the kudos reward they deserve. It is a win win. Thank you. 😉😉😉

Volunteers like myself cant spend hours in our lunch break solving a single ticket with multiple questions. 😮

Here is some more helpful info with  2 measures and a screen shot.  😁

Note the [EmployeeValid] on the left shows row values but no total value because it uses the SELECTEDVALUE command which only returns a value when the context for the column name has been filtered down to one distinct value.

Whereas the [EmployeesValid] on the right shows row values and total value because it uses SUMX to iterate [EmployeeValid] for every selected table row.

EmployeeValid =

// get min max dates for the selected date range

VAR calendarmin = MIN(Calendar[Date])

VAR calendarmax = MAX(Calendar[Date])

// get start end dates for employee

VAR employeestart = SELECTEDVALUE(Employees[START CONTRACT])

VAR employeeend = SELECTEDVALUE(Employees[END CONTRACT])

// return 1 if the employe was active, otherwise return nothing.

RETURN

IF(employeestart <= calendarmax   && employeeend >= calendarmin,1,BLANK())

EmployeesValid =

//  iterate the [EmployeeValid] for each selected row in Employee table

SUMX(Employees,[EmployeeValid])

[speedramps]

Hi again Frank

I am an unpaid Power BI volunter who helps during lunch breaks.

I cant write a full solution, you need to do that, but I will help.

Create a calender table with date and quarter.
Dont auto add or manauallly add relationships

Add a slicer with quarter with single slection turned on

Create a 1:M relationship from Emploee(Employree ID) to Sickness(Employree ID) 

Create these 2 measures, so when you select a quarter you get EmployeeValid and SicknessValid count.

You will then need to weave these measures tinto your other measures to build your report.

Sorry, I need to eat my lunch and get back to work.

Please do accept my solutions and raise more tickets for each other issues because I am spending a lot of time helping you. It is niggling when you spend hours helping and get no kudos. Thanks 😀

EmployeeValid =

// get min max dates form slected date range

VAR calendarmin = MIN(Calendar[Date])

VAR calendarmax = MAX(Calendar[Date])

// get start end dates for employee

VAR employeestart = SELECTEDVALUE(Employees[START CONTRACT])

VAR employeeend = SELECTEDVALUE(Employees[END CONTRACT])

RETURN

IF(employeestart <= calendarmax && employeeend >= calendarmin,1,BLANK())

SicknessValid =

// get min max dates form slected date range

VAR calendarmin = MIN(Calendar[Date])

VAR calendarmax = MAX(Calendar[Date])

// get start end dates for sickness

VAR sicknessstart = SELECTEDVALUE(Sickness[START CASE])

VAR sicknessend = SELECTEDVALUE(Sickness[END CASE])

RETURN

IF(sicknessstart <= calendarmax && sicknessend >= calendarmin,1,BLANK())

[frankhofmans]

hi Speedramps,

Thanks for your reply, but i still don't know how to use your solution. Does anybody have another solution?

Regards,

Frank

[speedramps]

Try using these measures ....

Employees =
// # of employees
COUNTROWS(Employees)

Sick0 =

// # of employee's with 0 sickness cases

VAR employeesickdays = SUMMARIZE(sickness,sickness[EMPLOYEE ID],"sickdays",COUNTROWS(sickness))

RETURN

[Employees] - COUNTROWS(employeesickdays)

Sick1 =

// # of employee's with 1 sickness case: 1 (empl. ID 120)

DISTINCTCOUNT(sickness[EMPLOYEE ID])

Sick2 =

// # of employee's with 2 or more sickness cases

VAR employeesickdays = SUMMARIZE(sickness,sickness[EMPLOYEE ID],"sickdays",COUNTROWS(sickness))

VAR employeewith2sickdays = FILTER(employeesickdays, [sickdays] >= 2)

RETURN

COUNTROWS(employeewith2sickdays)

Please click thumbs up and accept as solution button.

If you need to add more complex logic to your problem then please accept this solution and create a new ticket.

You will get a better response and each soilcer gets the kudos they deserve.
I've spent a lot of time on this ticket answering multiple questions so hope you will accept this solution for kudos. Thanks 😀

[frankhofmans]

Hi Speedramps,

Thanks for your solutions. The formula does not take the date filter into account. I want to create an overview per periode (per year of per quarter) and the possibility to deepdive (from year to quarter to month). So when i select 2021 Q1, i only want to show the number of employees that had at least 1 one "contract" day in 2021 Q1, the number of employees (with at least one contract day) that had 1 sickness case in 2021 Q1 (based on the startdate of the sickness case), the number of employees (with at least one contract day) that had 2 or more sickness cases in Q1 and the number of employees that had 0 sickness case (total number of employees - number of employees with 1 case - number of employees with 2 or more cases).

Do you have a solution for that?

Many thanks,

Regards,

Frank