Forum Discussion
Count rows less than column value
Hi there,
I am trying to do something which should be simple, but I am getting nowhere (as per usual!) ;)
I have a column of durations in months (integers from 0 to 120, with many repeated values) and want to count how many are less than or equal to each of the values in the 'Duration' column. Ideally, I also want to then be able to filter visuals by other columns in the table.
What is the best way to go about this? I have tried something like this in the table, which I realise is wrong:
=CALCULATE( COUNTROWS( Table ), FILTER( Table, Table[Duration] <= Table[Duration] ))
I have also tried a linked DISTINCT durations table, with a similar calculated column in that table:
=CALCULATE( COUNTROWS( Table ), FILTER( Table, Table[Duration] <= [Duration] ))
...but this does not appear to work either, and won't allow me to filter on other columns in the original table.
Any ideas?
Will
You are close. Assuming you are filtering on your duration column in a visual, this should give you what you want.
=CALCULATE( COUNTROWS( Table ), FILTER( ALL(Table), Table[Duration] <= max(Table[Duration] )))
The addition of the ALL function removes all filters in your visualisation
the addition of the MAX function (or any aggregator for that matter) will detect the current filter context and pass that to your calculate formula.
Hence the filter function first removes all filters and then reapplies a filter on the Table[Duration] column to be less than or equal to he maximum value in the current filter context.
5 Replies
You are close. Assuming you are filtering on your duration column in a visual, this should give you what you want.
=CALCULATE( COUNTROWS( Table ), FILTER( ALL(Table), Table[Duration] <= max(Table[Duration] )))
The addition of the ALL function removes all filters in your visualisation
the addition of the MAX function (or any aggregator for that matter) will detect the current filter context and pass that to your calculate formula.
Hence the filter function first removes all filters and then reapplies a filter on the Table[Duration] column to be less than or equal to he maximum value in the current filter context.