# Counting non-blank rows when calculating a cumulative number

• ### Question

• Hi

I have tried the below formula and derivations of it (using counta, countax) in trying to create a cumulative number that does not count blank cells/records.

Every variation I try however still counts blank cells/records. Does anyone have any ideas?

=CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory])),s9JudgeFTC[reentry]=1)

Paul

Monday, March 9, 2015 6:38 PM

• Hi Paul,

use "&&" to add filter arguments:

=CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory]) &&  NOT(ISBLANK([entity_id])),s9JudgeFTC[reentry]=1)

Imke

• Marked as answer by Wednesday, March 11, 2015 1:25 PM
Wednesday, March 11, 2015 1:15 PM
• Hi Imke

Actually, a slight variation of your formula above works:

=CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory])),NOT(ISBLANK(s9JudgeFTC[ExitMonthCategory])),s9JudgeFTC[reentry]=1)

Thanks a lot!!

Paul

• Marked as answer by Thursday, March 12, 2015 8:13 AM
Wednesday, March 11, 2015 1:24 PM

### All replies

• Hi Paul,

Imke

Tuesday, March 10, 2015 8:35 PM
• Hi Imke

That make work in theory (and I will use it going forward for other items), but the Filter function can only take two arguments apparently and it is erroring on that.

Paul

Wednesday, March 11, 2015 1:10 PM
• Hi Paul,

use "&&" to add filter arguments:

=CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory]) &&  NOT(ISBLANK([entity_id])),s9JudgeFTC[reentry]=1)

Imke

• Marked as answer by Wednesday, March 11, 2015 1:25 PM
Wednesday, March 11, 2015 1:15 PM
• Hi Imke

Actually, a slight variation of your formula above works:

=CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory])),NOT(ISBLANK(s9JudgeFTC[ExitMonthCategory])),s9JudgeFTC[reentry]=1)

Thanks a lot!!

Paul

• Marked as answer by Thursday, March 12, 2015 8:13 AM
Wednesday, March 11, 2015 1:24 PM