Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |