How to count no of records

[Anonymous]

  I have the data in the fact table as follows

YEAR	MONTH	METRICS_ID	TERMINAL_IID
2016	03	6	11
2016	03	6	12
2016	03	6	21
2016	03	6	21
2016	03	6	12
2016	03	6	12
2016	03	1	12
2016	03	1	12
2016	03	1	21
2016	03	1	21
2016	03	1	11
2016	03	1	12
2016	03	2	21
2016	03	2	11
2016	03	2	12
2016	03	2	21
2016	03	2	12
2016	03	2	12
2016	03	3	12
2016	03	3	12
2016	03	3	12
2016	03	5	21
2016	03	5	12
2016	03	5	12
2016	03	5	12
2016	03	5	21
2016	03	5	11

We have the following dimension

METRICS_ID
1
2
3
4
5
6

The expected results.

When the User Select Metrics_ID 6 from Dimension , we need to count (terminal_iid) who had done 2 or more transactions rest for all other metrics we need to count(terminal_iid)    

for example if user select metrics_id 6 and year 2016 and month 03 , then the expected results should be 2.  (Only Terminal IID 12 and 21 having 2 and more transactions)

I am new and not familiar with set analysis, is there any way to build the expression for the above case.

Please guide me

[sunny_talwar]

May be this:

=Sum(If(Aggr(Count(TERMINAL_IID), METRICS_ID, TERMINAL_IID) > 1, 1))

or

=Sum(If(Aggr(Count(TERMINAL_IID), METRICS_ID, TERMINAL_IID, YEAR, MONTH) > 1, 1))

[Anonymous]

Thanks a lot  for you reply.  I have tried the same , but still i am getting 3 for Metrics_ID for 6.  I have uploaded your QVW file , there i can see

YEAR	MONTH	METRICS_ID	TERMINAL_IID
2016	03	1	11
2016	03	1	12
2016	03	1	21
2016	03	2	11
2016	03	2	12
2016	03	2	21
2016	03	3	12
2016	03	5	11
2016	03	5	12
2016	03	5	21
2016	03	6	11
2016	03	6	12
2016	03	6	21

i can see only 3 records for metrics ID 6.  Is anything we need to do to restrict the original data?.   Because the original data i have 6 Rows for metrics ID 6

Please advice

[sunny_talwar]

I am not 100% sure I understand where are you seeing 3? My screenshot above shows 2 for  METRICS_ID = 6. Do you want to see 3?

[Anonymous]

Sorry its working fine .. Its resolved

[sunny_talwar]

Awesome. I would suggest closing the thread down in that case. Also Please consider marking real helpful answer as helpful. As marking your response doesn't really seems helpful for future users.

Thanks,

Sunny