Forum Discussion

JasonFurtner's avatar
JasonFurtner
Regular Visitor
3 years ago
Solved

DistinctCount overcounting when drill down by date

Hello

 

I have data that looks similar to the form below.

 

Unit SNQuestionAnswer (Y/N)Date
N001Question 1Y4/1/2023
N001Question 2Y4/1/2023
N001Question 3Y4/1/2023
N001Question 4Y4/1/2023
N001Question 5Y4/2/2023
N001Question 6Y4/2/2023
N002Question 1Y4/2/2023
N002Question 2Y4/2/2023
N002Question 3Y4/3/2023
N002Question 4Y4/4/2023
N002Question 5Y4/4/2023
N002Question 6Y4/4/2023
N003Question 1Y4/4/2023
N003Question 2Y4/4/2023
N003Question 3Y4/5/2023
N003Question 4Y4/6/2023
N003Question 5Y4/7/2023
N003Question 6Y4/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

  • Nevermind. I figured it out. I can just add a filter to only count 1 question from each questionaire. That seems to work okay.