Hi!
| date1 |
field2 |
field3 |
id |
| 25/05/2023 |
AAA |
111 |
7 |
| 25/05/2023 |
AAA |
111 |
8 |
| 26/05/2023 |
BBB |
111 |
7 |
| 26/05/2023 |
AAA |
222 |
8 |
| 26/05/2023 |
BBB |
111 |
9 |
I have a pivot table with date1, field2 and field3 as columns, and I want a measure that gets the sum of distinct id's per date1. I've tried with sum(aggr(count(distinct id)),date1), but it doesn't work for the rows of field2 and field3, because they have to be included in the measure. The problem is that depending on the level of dimension, the measure should be one or another.
For example, for field2 it would be sum(aggr(count(distinct id)),date1, field2), and for field3, sum(aggr(count(distinct id)),date1, field2, field3). In the pivot table the dimensions are dynamic, the user can move the order of then, so the measure would also change.
The count(distinct id) of all data is 3, but the sum(aggr(count(distinct id)),date1) is 2+3=5.
How can I write the measure to be the same for all levels of the pivot table?
