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 the current row value. Note that the scores can appear multiple times.

 

An example of what I am trying to achieve is below...

 

 

Thanks!

 

 

  • 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 )
    )

3 Replies

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

    Hi,

    I am not sure how your datamodel looks like, but could you please try something like below wether it suits your requirement?

     

    Expected count result measure: =
VAR _a = [PRE_TotalNumeracy measure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TableName'[ColumnName] ),
        [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 )
    )