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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
bamcdonough
New Member

Distinct Count IDs, COUNTROWS with Multiple Filters

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 ID2022 Incompletes
123456720
891011120
1658565520
2426119920
3193674320
3961228720
4728783120
5496337520
6263891920

Dataset Sample:
User - User IDTraining - Training TitleTranscript - Transcript StatusTranscript - Transcript Registration Date
1234567Insider Trading 2022 (GCG_1H22)Completed6/26/2022
1234567Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/26/2022
1234567Global Information Handling 2022 (GCG_1H22)Registered / Past Due6/26/2022
8910111Insider Trading 2022 (GCG_1H22)Completed6/25/2022
8910111Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
8910111Global Information Handling 2022 (GCG_1H22)Registered / Past Due6/25/2022
16585655Insider Trading 2022 (GCG_1H22)Registered / Past Due6/25/2022
16585655Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
16585655Global Information Handling 2022 (GCG_1H22)Registered / Past Due6/25/2022
24261199Insider Trading 2022 (GCG_1H22)Completed6/25/2022
24261199Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
24261199Global Information Handling 2022 (GCG_1H22)Completed6/25/2022
31936743Insider Trading 2022 (GCG_1H22)Registered / Past Due6/25/2022
31936743Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)In Progress / Past Due6/25/2022
31936743Global Information Handling 2022 (GCG_1H22)Completed6/25/2022
39612287Insider Trading 2022 (GCG_1H22)Registered / Past Due6/25/2022
39612287Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
39612287Global Information Handling 2022 (GCG_1H22)Completed6/25/2022
47287831Insider Trading 2022 (GCG_1H22)Registered / Past Due6/24/2022
47287831Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
47287831Global Information Handling 2022 (GCG_1H22)Registered / Past Due6/24/2022
54963375Insider Trading 2022 (GCG_1H22)Registered / Past Due6/24/2022
54963375Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
54963375Global Information Handling 2022 (GCG_1H22)Registered / Past Due6/24/2022
62638919Insider Trading 2022 (GCG_1H22)Registered / Past Due6/24/2022
62638919Economic Sanctions, Antiboycott and Export Control Laws 2022 (GCG_1H22)Registered / Past Due6/25/2022
62638919Global Information Handling 2022 (GCG_1H22)Completed6/24/2022
 
This is how I would like it calculated:
User - User ID2022 Incompletes
12345672
89101112
165856553
242611991
319367432
396122872
472878313
549633753
626389192

 

Thanks in advance for your help!

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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)"}))

vjaywmsft_0-1669966535134.png

 

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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)"}))

vjaywmsft_0-1669966535134.png

 

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.