Forum Discussion
Count distinct IDs based on another field
Hello,
I have a large data set where the ID is not distinct, and I want to create a measure to count the IDs per Region.
The table looks like this
| ID | Region |
| 001 | APAC |
| 001 | APAC |
| 002 | APAC |
| 003 | AMER |
| 003 | AMER |
| 003 | AMER |
| 004 | EU |
| 005 | EU |
| 005 | EU |
And I want an output to be:
| Region | IDs |
| APAC | 2 |
| AMER | 1 |
| EU | 2 |
The reason for this has to do with the filter pane. When I put Region in the filter pane, I want the number of rows count to be by the number of IDs, not by the number of rows for the region in the data set. So, I want the filter to read as the output (2nd table) above, but right now it reads like this:
| Region | Count |
| APAC | 3 |
| AMER | 3 |
| EU | 3 |
Hi Anonymous ,
Please create a new column:
Count = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Region] = EARLIER('Table'[Region])))Create a table visual:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi,
To your table visual, drag Region and write this measure
Measure = distinctcount(Data[ID])
Hope this helps.
Hi Anonymous ,
Please create a new column:
Count = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Region] = EARLIER('Table'[Region])))Create a table visual:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
