Forum Discussion
CountRows with ALL & FILTER
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:
FILTER(Activity,Activity[New/Continuation]="New"),
ALL(Activity[Role]))
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] ) )
2 Replies
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] ) )