Count rows less than column value

[Anonymous]

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

[MattAllington · Accepted solution]

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. 

[Anonymous]

Hi Matt,

Thanks for that, I will try it out!

Do you know of any accessible tutorials on how to learn more about calculations and measures, CALCULATE and filter contexts?

Will

[Anonymous]

Ha ha! Just noticed your book link at the bottom. I'll check it out! 🙂

Will

[MattAllington]

Yep, that is what I was going to suggest. 

[vazfelipe]

Hi,

Please, I created a measure MEDIAN (Dax 2013 version, there's no Median function). I would like to COUNT how many itens in the AVG column that are Greater than the MEDIAN measure. Any idea?

My example is here: https://1drv.ms/f/s!As8YC7wZr8RKist1_Ql70SsOKar7yA