Forum Discussion
DistinctCount overcounting when drill down by date
Hello
I have data that looks similar to the form below.
| Unit SN | Question | Answer (Y/N) | Date |
| N001 | Question 1 | Y | 4/1/2023 |
| N001 | Question 2 | Y | 4/1/2023 |
| N001 | Question 3 | Y | 4/1/2023 |
| N001 | Question 4 | Y | 4/1/2023 |
| N001 | Question 5 | Y | 4/2/2023 |
| N001 | Question 6 | Y | 4/2/2023 |
| N002 | Question 1 | Y | 4/2/2023 |
| N002 | Question 2 | Y | 4/2/2023 |
| N002 | Question 3 | Y | 4/3/2023 |
| N002 | Question 4 | Y | 4/4/2023 |
| N002 | Question 5 | Y | 4/4/2023 |
| N002 | Question 6 | Y | 4/4/2023 |
| N003 | Question 1 | Y | 4/4/2023 |
| N003 | Question 2 | Y | 4/4/2023 |
| N003 | Question 3 | Y | 4/5/2023 |
| N003 | Question 4 | Y | 4/6/2023 |
| N003 | Question 5 | Y | 4/7/2023 |
| N003 | Question 6 | Y | 4/7/2023 |
I am using a simple measure:
CountSN = DISTINCTCOUNT(Table[UnitSN])
I want to show how many SN's completed the questionaire over time, and I want to be able to drill down through the date range. As I drill down, the distinct count function starts counting SN's more than once. For example, when I look at CountSN by month i get this:
But when I look at it by day I get this:
Since there are only 3 unique SN's in the table, the latter is clearly not correct. How can I filter this so that it only counts each SN one time, regardless of the date range I choose to look at?
Thanks,
Jason
Nevermind. I figured it out. I can just add a filter to only count 1 question from each questionaire. That seems to work okay.
1 Reply
- JasonFurtnerRegular Visitor
Nevermind. I figured it out. I can just add a filter to only count 1 question from each questionaire. That seems to work okay.