topic Re: For each Staff, Count occurrences where 2 measure are TRUE for the same date in DAX Commands and Tips

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

Deevo_ — Mon, 12 Jun 2023 03:49:18 GMT

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!

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

tamerj1 — Mon, 12 Jun 2023 05:02:48 GMT

Hi Deevo_
Please try

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

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

Deevo_ — Mon, 12 Jun 2023 05:58:24 GMT

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.

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

tamerj1 — Mon, 12 Jun 2023 06:33:51 GMT

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

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

Deevo_ — Mon, 12 Jun 2023 06:47:04 GMT

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

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

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

tamerj1 — Mon, 12 Jun 2023 07:31:59 GMT

Deevo_