Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
aolan
Frequent Visitor

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

 

Screenshot_1.png

 

Thanks!

 

 

1 ACCEPTED SOLUTION

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors