topic Re: Count rows that are less than the current row's value in DAX Commands and Tips

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

aolan — Mon, 10 Oct 2022 04:11:41 GMT

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!

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

Jihwan_Kim — Mon, 10 Oct 2022 04:15:54 GMT

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 )

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

aolan — Mon, 10 Oct 2022 04:40:20 GMT

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

Name	Question Number	Mark	Bit
ABC	Question 1	Correct	1
ABC	Question 2	Correct	1
ABC	Question 3	Incorrect	0
ABC	Question 4	Correct	1

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.

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

Jihwan_Kim — Mon, 10 Oct 2022 04:56:36 GMT

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