Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
User - User ID | 2022 Incompletes |
1234567 | 20 |
8910111 | 20 |
16585655 | 20 |
24261199 | 20 |
31936743 | 20 |
39612287 | 20 |
47287831 | 20 |
54963375 | 20 |
62638919 | 20 |
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 |
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!
Solved! Go to 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)"}))
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)"}))
@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
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |