Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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 value...
  • MattAllington's avatar
    10 years ago

    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.