Distinct Count IDs, COUNTROWS with Multiple Filters

[bamcdonough]

Hi Everyone,

I'm trying to analyze training data and I want to create a calculation that shows how many incompete trainings each employee has this year (2022).  

I "Grouped By" one report (Training Watchlist) by emplyoee ID, then in the second column I want to see the number of incomplete trainings per employee, which are identified in the file as "In Progress / Past Due" and "Registered / Past Due".  There have been three trainings this year, so the rance should be 0-3.  I'm trying to pull training data from a second report (Training_All_In_Data).  I have the two reports linked by employee ID.

The formula I'm using is the following:

2022 Incompletes = CALCULATE(COUNTROWS(Training_All_In_Data),FILTER(Training_All_In_Data,Training_All_In_Data[Transcript - Transcript Status] = "In Progress / Past Due" || Training_All_In_Data[Transcript - Transcript Status] = "Registered / Past Due"),filter(Training_All_In_Data,Training_All_In_Data[Training - Training Title] = "Insider Trading 2022 (GCG_1H22)" || Training_All_In_Data[Training - Training Title] = "Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)" || Training_All_In_Data[Training - Training Title] = "Global Information Handling 2022 (GCG_1H22)"))

This is how it is currently displaying incorrectly and showing the total incompletes for the population per row:

User - User ID	2022 Incompletes
1234567	20
8910111	20
16585655	20
24261199	20
31936743	20
39612287	20
47287831	20
54963375	20
62638919	20

Dataset Sample:

User - User ID	Training - Training Title	Transcript - Transcript Status	Transcript - Transcript Registration Date
1234567	Insider Trading 2022 (GCG_1H22)	Completed	6/26/2022
1234567	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/26/2022
1234567	Global Information Handling 2022 (GCG_1H22)	Registered / Past Due	6/26/2022
8910111	Insider Trading 2022 (GCG_1H22)	Completed	6/25/2022
8910111	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
8910111	Global Information Handling 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
16585655	Insider Trading 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
16585655	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
16585655	Global Information Handling 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
24261199	Insider Trading 2022 (GCG_1H22)	Completed	6/25/2022
24261199	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
24261199	Global Information Handling 2022 (GCG_1H22)	Completed	6/25/2022
31936743	Insider Trading 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
31936743	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	In Progress / Past Due	6/25/2022
31936743	Global Information Handling 2022 (GCG_1H22)	Completed	6/25/2022
39612287	Insider Trading 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
39612287	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
39612287	Global Information Handling 2022 (GCG_1H22)	Completed	6/25/2022
47287831	Insider Trading 2022 (GCG_1H22)	Registered / Past Due	6/24/2022
47287831	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
47287831	Global Information Handling 2022 (GCG_1H22)	Registered / Past Due	6/24/2022
54963375	Insider Trading 2022 (GCG_1H22)	Registered / Past Due	6/24/2022
54963375	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
54963375	Global Information Handling 2022 (GCG_1H22)	Registered / Past Due	6/24/2022
62638919	Insider Trading 2022 (GCG_1H22)	Registered / Past Due	6/24/2022
62638919	Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)	Registered / Past Due	6/25/2022
62638919	Global Information Handling 2022 (GCG_1H22)	Completed	6/24/2022

This is how I would like it calculated:

User - User ID	2022 Incompletes
1234567	2
8910111	2
16585655	3
24261199	1
31936743	2
39612287	2
47287831	3
54963375	3
62638919	2

Thanks in advance for your help!

[v-jayw-msft · Accepted solution]

Hi @bamcdonough ,

Please check the formula.

measure = CALCULATE(COUNT(yourtable[User - User ID]),FILTER(yourtable,yourtable[Transcript - Transcript Status] in {"In Progress / Past Due","Registered / Past Due"}&&yourtable[Training - Training Title] in {"Insider Trading 2022 (GCG_1H22)","Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)","Global Information Handling 2022 (GCG_1H22)"}))

[amitchandak]

@bamcdonough , Try like

2022 Incompletes =
CALCULATE (
COUNTROWS ( Training_All_In_Data ),
FILTER (
Training_All_In_Data,
Training_All_In_Data[Transcript - Transcript Status] in { "In Progress / Past Due", "Registered / Past Due"}
&& Training_All_In_Data[Training - Training Title] in { "Insider Trading 2022 (GCG_1H22)", "Economic Sanctions, Antiboycott and Export Control" , "Global Information Handling 2022 (GCG_1H22)" }
)
)

But the measure seems fine. hope you are creating measures