rspaeth2001
New Member
- Joined
- Apr 7, 2016
- Messages
- 4
I need to count clients served each day.
I want to only count a client one time even if they have multiple services in one day.
I have a formula that works in Excel
I have Column A is a date field and Column B is Client ID
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
<tbody>
</tbody>
This formula works for small data sets. (Monthly)
But, I run out of memory if try for 6 months or a year's worth of data.
I can query the server and get the dataset I want but can't get the formula to work in powerpivot.
I don't want a Total Summary field of Total Visits Measure calculation.
I would prefer to go row by row to verify results.
Can you help me convert this formula to a Calculated Column?
Thanks!
I want to only count a client one time even if they have multiple services in one day.
I have a formula that works in Excel
I have Column A is a date field and Column B is Client ID
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Date | Client ID | 1 per Day Visit(formula) |
7/1/2015 | 1 | 1 |
7/1/2015 | 1 | 0 |
7/1/2015 | 1 | 0 |
7/2/2015 | 1 | 1 |
7/2/2015 | 1 | 0 |
<tbody>
</tbody>
This formula works for small data sets. (Monthly)
But, I run out of memory if try for 6 months or a year's worth of data.
I can query the server and get the dataset I want but can't get the formula to work in powerpivot.
I don't want a Total Summary field of Total Visits Measure calculation.
I would prefer to go row by row to verify results.
Can you help me convert this formula to a Calculated Column?
Thanks!