cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drwillparry Regular Visitor
Regular Visitor

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!) Smiley Wink

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count rows less than column value

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
5 REPLIES 5
Super User
Super User

Re: Count rows less than column value

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
drwillparry Regular Visitor
Regular Visitor

Re: Count rows less than column value

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

drwillparry Regular Visitor
Regular Visitor

Re: Count rows less than column value

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

 

Will

Super User
Super User

Re: Count rows less than column value

Yep, that is what I was going to suggest. Smiley Very Happy



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
vazfelipe Frequent Visitor
Frequent Visitor

Re: Count rows less than column value

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