Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Please help me with some DAX.
Here's a sample of the 'Registrations' data:
People | Course | Completion |
Person 1 | E1 | 100% |
Person 1 | E2 | 0% |
Person 1 | M1 | 100% |
Person 1 | M2 | 10% |
Person 1 | M3 | 20% |
Person 2 | E1 | 100% |
Person 2 | E2 | 100% |
Person 2 | M1 | 90% |
Person 2 | M2 | 100% |
Person 2 | M3 | 100% |
Person 3 | E1 | 100% |
Person 3 | E2 | 100% |
Person 3 | M1 | 10% |
Person 3 | M2 | 10% |
Person 3 | M3 | 10% |
And then a dimension table:
Course | Subject |
E1 | English |
E2 | English |
M1 | Maths |
M2 | Maths |
M3 | Maths |
The fact table has the same Courses for every person and a completion % for each. I need to create measures that count the number of people that have:
1. Started one or more Courses
2. Completed 80%-100% in one or more Courses
3. Completed 80%-100% in ALL Courses <<<< This is the one I'm stuck on
I have measures that solve for 1 and 2:
Note that in the report, I want to slice by subject: English or Maths or all.
So, the results for 'Completed 80%-100% in ALL Courses' would be:
For English, 2 (Person 2 and Person 3),
For Maths, 1 (Person 2 only).
The solution is probably quite simple, but I'm not getting anywhere with my attempts. Help my sanity, please!
Please try the below code:
#Learners =
VAR _Table =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'Data', 'Data'[People], Subject[Subject] ),
ALLSELECTED ()
),
"@AVGCOMP",
CALCULATE (
AVERAGE ( Data[Completion] ),
ALLEXCEPT ( Data, Data[People], Subject[Subject] )
)
)
VAR _Filtered =
FILTER ( _Table, [@AVGCOMP] >= 0.8 )
RETURN
COUNTROWS ( _Filtered )
Hi and thanks for your help. I'm really glad you posted - its interesting to see how you've tackled this.
I've tried that measure and tested it; it kind of works but not all the time. I think it hinges upon where you've used average, whereas I'd need it when all completions are 80% or higher (and not just the average). So someone with 100%, 100%, 79% should not by counted. Would you be able to adapt the DAX? I've tried but without success. I think it would be along the lines of: when a 'count of rows' is equal to a 'count of the rows with completion >= 80%'.
I worked out a solution, but it's by deduction. I'd still be keen to hear if anyone can come up with a way to count them directly.
To count how many have achieved 80-100% in all modules, I started with all Learners than deducted any that had a score below 80%:
User | Count |
---|---|
53 | |
22 | |
18 | |
18 | |
13 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |