count based on possible values

[apthansh]

Hi - I have table as below.

ID	Type
1	Active
1	Inactive
2	Active
2	Pending

I have to get count of ID on each type -Active, Inactive, Pending but If ID has both Active & Inactive I only want to include the count in Active group.

Based on the data, below would be the counts, I want to show it in the KPI.

Active - 2

Inactive - 0

pending - 1

Thank you much.

[apthansh]

Hi - I have table as below.

ID	Type
1	Active
1	Inactive
2	Active
2	Pending

I have to get count of ID on each type -Active, Inactive, Pending but If ID has both Active & Inactive I only want to include the count in Active group.

Based on the data, below would be the counts, I want to show it in the KPI.

Active - 2

Inactive - 0

pending - 1

Thank you much.

[tresesco]

Inactive :

 =Count(DISTINCT {<Type={'Inactive'},ID=e({<Type={'Active'}>})>}ID)

[sunny_talwar · Accepted solution]

May be try this also

Sum(Aggr(If(Count(DISTINCT TOTAL <ID> {<Type = {'Active', 'Inactive'}>} Type) = 2 and Type = 'Inactive', 0, 1), ID, Type))

With Type as your dimension. 

[apthansh]

@sunny_talwar Thank you - This gave me a total count which is correct.I want to put this in the KPI for each type.

#Active - 

#InActive - 

#Pending - 

Thank you.

[sunny_talwar · Accepted solution]

#Active - Count(DISTINCT {<Type *= {'Active'}>} ID)

#InActive - Count(DISTINCT {<Type *= {'Inactive'}, ID *= e({<Type={'Active'}>})>} ID)

#Pending - Count(DISTINCT {<Type *= {'Pending'}>} ID)

[apthansh]

you are awesome.Thank you.