Forum Discussion

bamcdonough's avatar
bamcdonough
New Member
3 years ago
Solved

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!

  • Anonymous's avatar
    Anonymous
    3 years ago

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

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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