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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
frankhofmans
Helper IV
Helper IV

Count number of unique values from multiple tables

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 IDSTART CONTRACTEND CONTRACT
1111-1-20201-8-2020
1121-7-2020 
1131-9-20201-3-2021
1141-10-2020 
1151-10-20201-9-2021
1161-11-2020 01-12-2020
1171-12-2020 
1181-4-2021 
1191-5-20211-10-2021
1201-5-2021 

 

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

 

Table 2: all sickness cases

 

EMPLOYEE IDSTART CASEEND CASE
1111-3-20201-4-2020
1141-2-20211-3-2021
1171-1-20211-2-2021
1201-9-20211-10-2021
1141-5-20211-6-2021
1141-9-20211-10-2021
1171-4-20211-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

5 REPLIES 5
speedramps
Super User
Super User

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_1-1652531157974.png

 

 

 

 

 

speedramps
Super User
Super User

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

 

 

 

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
Super User
Super User

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 😀
 
 
 
 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.