Forum Discussion
Count Rows per Group with Filter Responsiveness
Hi folks, I'm trying to create a measure that will respond to filters to give me the number of rows in each group (defined by the value of two columns). Example:
| ID | Classification | Days | Count |
| ID1 | Historical | 7 | 2 |
| ID1 | Historical | 14 | 2 |
| ID1 | Historical | 21 | 1 |
| ID2 | Historical | 7 | 2 |
| ID2 | Historical | 14 | 2 |
| ID3 | Current | 7 | 3 |
| ID3 | Current | 14 | 1 |
| ID3 | Current | 21 | 1 |
| ID4 | Current | 7 | 3 |
| ID5 | Current | 7 | 3 |
I'm currently using the expression Count = CALCULATE(COUNT([ID]), ALLEXCEPT(TableName, [Classification], [Days])) but this isn't responsive to filters. So, for instance, if I filter by another column and wind up with the following data, the Count column doesn't change:
| ID | Classification | Days | Count (with current formula - same as above) | VALUE I NEED |
| ID1 | Historical | 7 | 2 | 1 |
| ID1 | Historical | 14 | 2 | 1 |
| ID1 | Historical | 21 | 1 | 1 |
| ID4 | Current | 7 | 3 | 1 |
Does anyone have suggestions for how to get the column values I need?
Hi Anonymous
try a measure
Count = CALCULATE(COUNTROWS(TableName), FILTER(ALLSELECTED(TableName), TableName[Classification]=SELECTEDVALUE(TableName[Classification]) && TableName[Days]=SELECTEDVALUE(TableName[Days])))
2 Replies
Anonymous
Replace COUNT with DISTINCTCOUNT
If it helps, mark it as a solution
Kudos are nice tooHi Anonymous
try a measure
Count = CALCULATE(COUNTROWS(TableName), FILTER(ALLSELECTED(TableName), TableName[Classification]=SELECTEDVALUE(TableName[Classification]) && TableName[Days]=SELECTEDVALUE(TableName[Days])))
