For each Staff, Count occurrences where 2 measure are TRUE for the same date

[Deevo_]

Hi All,

Thanks in advance. I apologise if I am asking or using the incorrect terms, I have tried my best to make it clear using sample data. I am unable to upload a PBIX file due to workplace permissions.

Context:

I have a group of 3 staff in 3 different Teams who record leave days OR submit timesheets OR do both for each workday. I need to count every occurrences when a staff records leave AND submits a timesheet for the same date.

What I am trying to achieve:

I have 3 staff members and for each staff member, I need a measure to count every occurence where 2 measures are TRUE.

• Measures:
  • Leave Events= SUM([LeaveEventTotal])
  • Timesheet Hours submitted = SUM([Timesheet Hours])
• I need to factor in the groupings by the Teams if possible, I may need to use conditional formatting at some point to colour the values only and not the Team Totals. (future task)

Datasets (Simplified sample data):

Date table:

Datekey	Date
20230601	01/06/2023
20230602	02/06/2023
20230603	03/06/2023

Staff Details Table:

Logon ID	Staff Name	TeamID
AA001	Alex Apple	1
BB002	Bob Banana	2
CC003	Charlie Cucumber	3

Team Names Table:

TeamID	Team Name
1	Alpha
2	Bravo
3	Charlie

Leave Table:

DateKey	Leave Date	Logon ID	Staff Name	LeaveEventTotal
20230602	02/06/2023	BB002	Bob Banana	1
20230603	03/06/2023	CC003	Charlie Cucumber	1

Timesheets Table:

DateKey	Timesheet Date	Logon ID	Staff Name	Timesheet Hours
20230601	01/06/2023	AA001	Alex Apple	5
20230602	02/06/2023	BB002	Bob Banana	10
20230603	03/06/2023	CC003	Charlie Cucumber	(blank)

Report Display Output:

Date		01/06/2023	01/06/2023	02/06/2023	02/06/2023	03/06/2023	03/06/2023
Team Name	Staff Name	Total Leave Events	Timesheet Hours submitted	Total Leave Events	Timesheet Hours submitted	Total Leave Events	Timesheet Hours submitted
Alpha
	Alex Apple	(blank)	5	(blank)	(blank)	(blank)	(blank)
Bravo
	Bob Banana	(blank)	(blank)	1	10	(blank)	(blank)
Charlie
	Charlie Cucumber	(blank)	(blank)	(blank)	(blank)	1	(blank)

Expected Result:

• Over the 3 days period, I expect a resulting count = 1.
  • In my Report display output table, Team Bravo has staff Name "Bob Banana" who has a Leave event and Timesheet hours recorded on the same date (02/06/2023). The other 2 staff Names do not meet the criteria and should not be included in the total count.

Many thanks!

[tamerj1]

Hi @Deevo_ 
Please try

Count =
SUMX (
    CROSSJOIN ( VALUES ( 'Team Names'[Team Name] ), VALUES ( 'Date'[Date] ) ),
    INT ( [Leave Events] + [Timesheet Hours submitted] <> BLANK () )
)

[Deevo_]

Hi Tamerj1, I appreciate your time. This almost works, it seems to be counting the occurrences and then multiplying the count by 3 times. Not sure why?

Can you please explain the Cross Join and the values inside? I believe i need to add more values inside the CROSS Join for it to count correctly. I tried adding "VALUES ('Staff Details'[Staff Name])" to the cross join and it seems to count correctly.

Your advice would be great.

[tamerj1]

Hi @Deevo_ 
Yea you are right. Please refer to attached sample file with the proposed solution. I have added a calculated table that should clarify how CROSSJOIN works.

Count = 
SUMX (
    CROSSJOIN ( VALUES ( 'Staff Details'[Staff Name] ), VALUES ( 'Date'[Date] ) ),
    INT ( [Leave Events] <> BLANK ( ) && [Timesheet Hours submitted] <> BLANK () )
)

[Deevo_]

Hi Tamerj1, Thank you for providing a refined solution, this is working perfectly.

1. However, i cant see the calculated table inside the PBIX file?
2. Do I have to enable some options to see this? I am using PowerBI desktop Version: 2.112.1421.0 64-bit (January 2023).

[tamerj1]

@Deevo_