Hi,
I have data that looks like this:
Person | Department | Date | Time | Visits |
X | A | 1.1.2020 | 10.00 | 1 |
X | A | 1.1.2020 | 11.00 | 1 |
X | B | 1.1.2020 | 12.00 | 1 |
Y | A | 1.1.2020 | 10.30 | 1 |
Z | A | 1.1.2020 | 12.00 | 1 |
X | A | 2.1.2020 | 10.00 | 1 |
I want to create a measure with a count of the number of visits, but if a person have two visits the same day in the same department, it should only count as one visit. So the count for would be 5 visits, because person X have two visits in department A on the same date. Anybody know what the expression should be for this?
I also want to make table with count of distinct persons per department. I use this expression: Sum(Aggr(Count (distinct Visit), Person_id, Department)) It shows the correct numbers per department, but the total shows the sum of the departments. I want the total to show the number of distinct persons which is 3 (xyz) not 4. How to fix this?
Department | Unique persons |
Totals: | 4 |
B | 1 |
A | 3 |
