Forum Discussion

aolan's avatar
aolan
Frequent Visitor
3 years ago
Solved

Count rows that are less than the current row's value

Hi,   I have a set of values from a calculated measure that represents the scores from a test - it counts the number of "Correct" marks. I basically want to count the rows of the values less than t...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your feedback.

    Could you please try the below?

     

    Expected count result measure: =
VAR _a = [PRE_TotalNumeracy measure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TableName'[Name] ),
        FILTER ( ALL ( 'TableName'[Name] ), [PRE_TotalNumeracy measure] < _a )
    )
aolan's avatar
aolan
Frequent Visitor
3 years ago

I have a "People" table that filters 1..* the "Responses" table. The Responses table looks something like...

 

NameQuestion NumberMarkBit
ABCQuestion 1Correct1
ABCQuestion 2Correct1
ABCQuestion 3Incorrect0
ABCQuestion 4Correct1

 

And the score is an aggregate of the "Bit" column. I feel like your answer is almost there but I am just getting this error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Thank you for your feedback.

Could you please try the below?

 

Expected count result measure: =
VAR _a = [PRE_TotalNumeracy measure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TableName'[Name] ),
        FILTER ( ALL ( 'TableName'[Name] ), [PRE_TotalNumeracy measure] < _a )
    )