DAX to count distinct rows for specific condition

[_Adrian]

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:

Learners = DISTINCTCOUNT(Registrations[People])

Started one or more courses = CALCULATE([Learners], Registrations[Completion] > 0)

Completed (80-100%) one or more courses = CALCULATE([Learners], Registrations[Completion] >= 0.8)

But I would like help on 3.

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!

[jameszhang0805]

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 )

DAX to count distinct rows for specific condition.pbix 

[_Adrian]

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%'. 

[_Adrian]

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%:

Completed ALL courses = [Learners] - CALCULATE([Learners], Registrations[Completion] < 0.8)