Measure to count distinct ID's that meet criteria over multiple rows

[Damondo]

Hi All, 

I'm wondering if it's possible to use a measure in this scenario so I can support slicers in the report. 

I've simplified the data for the sake of the example but if this can be solved I'll translate this to my full model. I have a table containing an Student ID (which links to another table containing Students) and a result. I'd like to count the number of distinct ID's that have less than 90% PASS rate. In the table below A has ~66% PASS and B has 75% PASS. In my actual data, I'll have to deal with a couple of different types of pass results but I think my main struggle is how to count distinct ID's that meet criteria over multiple rows.

ID	Result
A	PASS
A	PASS
A	FAIL
B	PASS
B	PASS
B	PASS
B	FAIL

At present, I'm using calculated columns to work things out but this isn't compatible with a date slicer (the full table has a date for each result). On the Student table, I'm using a calculated column to count the passes then divide by all of their results. I then have a second column to check if this is below 90%. Neither of these work with slicers as I hear that calculated columns are only processed at refresh. It's a shame these can't be set to refresh with certain slicer changes!

Any help would be much appreciated.

Kind Regards

Damondo

[Zubair_Muhammad · Accepted solution]

@Damondo 

Try a pattern like this MEASURE

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[ID] ),
        (
            CALCULATE ( COUNTROWS ( Table1 ), Table1[Result] = "PASS" )
                / CALCULATE ( COUNTROWS ( Table1 ) )
        ) < 0.9
    )
)

[Damondo]

Thanks for the response but I think I may have cracked it! I had a measure for the % Pass rate for the Results table (M_PercentageAtt) so I incorporated that into a filter command. I assume that generates a value for M_PercentageAtt for each row and compares to 0.90 before adding them to the count.

M_PAtest = 
CALCULATE (
    DISTINCTCOUNT ( Students[StudentID] ),
    FILTER ( ( Students ), [M_PercentageAtt] < 0.90 )
)

Yours looks like a good option too though so I may use that for different circumstances.