Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi - need help creating a measurement.
Here is a sample of my data:
Item ID | Course Title | User ID | Last Name | First Name | Completion Status | Completion Date |
000001 | Course A | 12345 | Doe | Allison | Complete | 4/21/2023 |
000001 | Course A | 23456 | Doe | John | Complete | 4/21/2023 |
000001 | Course A | 34567 | Doe | Mark | Complete | 9/26/2022 |
000002 | Course B | 34567 | Doe | Mark | Complete | 9/26/2022 |
000002 | Course B | 34567 | Doe | Mark | Complete | 9/26/2022 |
000003 | Course C | 34567 | Doe | Mark | Complete | 9/26/2022 |
000004 | Course D | 34567 | Doe | Mark | Complete | 9/26/2022 |
000001 | Course A | 45678 | Doe | Beth | Complete | 9/26/2022 |
000001 | Course A | 56789 | Doe | Erika | Complete | 9/28/2022 |
000002 | Course B | 45678 | Doe | Beth | Complete | 10/3/2022 |
000003 | Course C | 45678 | Doe | Beth | Complete | 10/3/2022 |
000004 | Course D | 45678 | Doe | Beth | Complete | 10/3/2022 |
What I would like to do is have a count for each user ID IF they have a completion for EACH course. So only count Mark Doe if he has completed Course A, B, C, and D.
try something like this
Count Users Completed All =
VAR coursecount = DISTINCTCOUNT(Table[Course], ALL())
RETURN COUNTROWS(FILTER(VALUES(Table[User ID]),
CALCULATE(DISTINCTCOUNT(Table[Course])) = coursecount
))
@kl8818 I hope this helps you.
Hi - thank you for your response. It is still not filtering correctly with that formula. It is counting every line not filtering if the user has completed Course A, B, C, D, and E. I have some users who have only completed Course A and B for example and I do not want to count them in this measurement.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
31 | |
27 | |
24 | |
22 |