Distinct based on several fields

[Amit_B]

Hi,

I need  to count distinct rows where aggr Sum is equal or bigger than 10.
I tried use aggr() function in the sheet measures but it doesn't work, so I created 2 flag fields:
* flagA - 1 if total sum by month & ID >= 10
* flagB - 1 if total sum by month & ID & type >= 10.
Now, my problem is to make a distinct count on several fields.

example:

Month	ID	Type	Sum	flagA	flagB
1	00001	a	5	0	0
2	00002	a	9	1	1
2	00002	a	3	1	1
2	00002	b	4	1	0
2	00002	b	5	1	0
2	00003	a	11	1	1
2	00003	b	11	1	1
3	00002	a	10	1	1
3	00003	a	9	0	0

count ID where monthly sum is equal and bigger than 10:
00002, month = 2, sum = 21
00003, month = 2, sum = 22
00002, month = 3, sum = 10
So, the measure should be the count of flagA where flagA=1 and distinct Month&ID, so 3.

count ID where monthly sum by type is equal and bigger than 10:
00002, month = 2, type = a, sum = 12
00003, month = 2, type = a, sum = 11
00003, month = 2, type = b, sum = 11
00002, month = 3, type = a, sum = 10
So, the measure should be the count of flagB where flagB=1 and distinct Month&ID&type,
so when selecting type = a, the result is 3, and when selecting type = b, the result is 1.

Love for some help.
Thanks!

[TauseefKhan · Accepted solution]

Hi @Amit_B,

For counting distinct IDs where the monthly sum is equal to or greater than 10 (flagA=1):

Count(DISTINCT {<flagA={1}>} Month & '|' & ID)

This expression will count the distinct combinations of Month and ID where flagA is equal to 1.

For counting distinct IDs where the monthly sum by type is equal to or greater than 10 (flagB=1):

Count(DISTINCT {<flagB={1}>} Month & '|' & ID & '|' & Type)

This expression will count the distinct combinations of Month, ID, and Type where flagB is equal to 1.

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[Amit_B]

I created a field where I concatenate all the relevant fields and used it in the formula. It's like you did, both ways works.
Thanks.