sample query result:
ID level [...]
--------------------------------
00a level 1, [...]
00a level 1, [...]
00b level 3, [...]
00b level 3, [...]
00d level 2, [...]
00e level 1, [...]
00f level 2, [...]
00e level 1, [...]
it should display: (PLEASE refer to the attached screenshot)
level 1: 2 level 2: 2 level 3: 1
So I'm trying to count the number of unique ID for each level.
My attempt:
Group them by level, then do a CountDistinct on the ID:
For level1 [=CountDistinct(IFF(Fields.level = "level 1", Fields.ID, 0)) - 1]
For level2 [=CountDistinct(IFF(Fields.level = "level 2", Fields.ID, 0)) - 1]
-1 is for all the 'false' count
However this didnt work, it will always return 0.
Any advice?