how to Count same values one time

[gireesh1216]

Hi Team,

I have a data like below:

SEAL	TAMS	SOX	RISK
1216	T529	Yes
1217	T529	High
1218	T529	Critical
1219	T530	Critical
1220	T531	Pending
1221	T532	Yes
1222	T533	Critical
1223	T533	High
1224	T533	Pending
1225	T533	Medium

Above table "SEAL" data is  Unique Values and "TAMS" data is Repeated Values.

My requirement is:

In the above table same TAMS (T529,T533) have multiple SOX values.So i want to calculate tams count based on priority.

SOX priority:

SOX

Yes

Critical

High

Medium

Pending

1)If SOX='Yes' then calculate TAMS count only these TAMS(T529,T532)

2)If SOX='Critical' then calculate TAMS count only these TAMS(T530,T533) .Here T529 ,T533 Id's SOX have Critical But Same Id We are counting in If SOX='Yes'.So no need to calculate again.

Expected Results:

SOX	TAMS COUNT
Yes	2
Critical	2
High	0
Medium	0
Pending	1

[sunny_talwar]

May be this

=Sum(Aggr(

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Yes'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Yes'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Critical'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Critical'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'High'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'High'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Medium'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Medium'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Pending'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Pending'}>} TAMS))))))

, TAMS, SOX))

[Clever_Anjos]

Please use a Straight Table

Dimension: =Aggr(FirstSortedValue(SOX,Match(SOX,'Yes','Critical','High','Medium','Pending')),TAMS)

Expression : Count(Distinct TAMS)