CountRows with ALL & FILTER

[Anonymous]

Hi community,

I am having difficulty trying to create a COUNTROWS measure that has both ALL & FILTER. This is my data structure,

Activity	New/Continuation	Role
A	New	Teacher
A	New	Teacher
B	New	Student
B	Continuation	Student
C	New	Student

I would like to COUNT only "New" rows, and also ignore the Roles when putting it in a Matrix that has Roles in Columns. Something like this,

Activity	Teacher	Student
A	2	0
B	0	1
C	0	1

This is my current measure, but it does not ignore Roles when I place it in my matrix:

Measure = CALCULATE(COUNTROWS(Activity),
FILTER(Activity,Activity[New/Continuation]="New"),
ALL(Activity[Role]))

Could you let me know what has gone wrong? Thank you.

[Anonymous · Accepted solution]

Your problem stems from the fact that you do something you should never do: putting a whole table as a filter in CALCULATE. Do not ever do it if you want to create correct formulas and stay sane. The golden rule of DAX is this: You must never filter a table if you can filter a column.

[Measure] =
CALCULATE(
    COUNTROWS( Activity ),
    KEEPFILTERS( Activity[New/Continuation] = "New" ),
    ALL( Activity[Role] )
)

[Anonymous]

Hi @Anonymous 

Thanks for pointing out my mistake Also really appreciate you explaining the approach I should take to avoid similar mistakes in the future!