How to use a column value as a variable for a calculated column?

Question

I have the Grades table:

Grade
88
91
97
92
84

and the PassCount table:

Value
80
85
90
95

I would like to add a calculated column to PassCount, called "Count", which would be the count of how many rows from the Grades table have a Grade which exceeds the value specified in PassCount's Value column.

For example, the first row of PassCount has a Value of 80, so the Count should count how many rows have a Grade higher than 80. All 5 are, so the Count should be 5 for the first row.

The expected result would be a PassCount table looking like this:

Value	Count
80	5
85	4
90	3
95	1

The issue is that I can not find the syntax to achieve this. If I try:

Count = Table.RowCount(Table.SelectRows(Grades, each [Grade] > [PassValue]))

Then PassValue can't be found.

If I try:

Count = Table.RowCount(Table.SelectRows(Grades, each Grades[Grade] > PassCount[PassValue]))

Then a cyclic reference is encountered.

I've also tried making measures and intermediate queries, but run into similar problems. I can't figure out how to use a table of values as variable values for a query/calculation.

Answer 1

Table.RowCount( Table.SelectRows(Grades, (x)=>  x[Grade] > [Value]))