Forum Discussion
Countrows with filters
Simple enough,
Hello jay13
this is your measure
let me know if it works
thank you for the reply. This seemed to work at first but if I add additional rows it is still only counting one?
Example: It should count 3 since ID's 3,4 and 5 have code D only.
| ID | code |
| 1 | M |
| 1 | D |
| 1 | L |
| 2 | D |
| 2 | M |
| 3 | D |
| 4 | D |
| 5 | D |
You're right. I changed the previous post with the updated formula. Let me know
Thanks Gabry ! Are you able to explain how this is working? I am understanding pieces of it but when we get to the last filter where its <> "D" then =0 I am getting a little lost. I really appriciate the help on this one. Thanks again!
Yeah, sure, I can try. You can use the DAX query view to help debug the code.
This part of the code:
FILTER (
VALUES ( Tabella[ID] ),
CALCULATE ( COUNTROWS ( FILTER ( Tabella, Tabella[code] <> "D" ) ) ) = 0
)filters out all the IDs that have a code different from "D". It essentially says to keep only the rows where the count is 0.
Then, you just count the IDs in this filtered table with:
DISTINCTCOUNT ( Tabella[ID] )
and you'll get the number of IDs.
😉
Simple enough,