Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Count aggr distinct dimension conditionally

I'd like to create an expression in a table that counts the number of Policies by Region that have a rating of 'Red' for Policy Health.

A Policy is classified as 'Red' if its Policy Health is >25. The only dimension in the table is Region.

In the same table, we calculate the Policy Health using the following calc:

sum({1*<Disabled = {'No'},[Run Date] = {'\$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count] * [SIM Score])
/
sum({1*<Disabled = {'No'},[Run Date] = {'\$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count])

The above expression works fine. The below is what I've tried to count distinct 'Red' Policies per Region:

count(distinct if((sum({1*<Disabled = {'No'},[Run Date] = {'\$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count] * [SIM Score])
/
sum({1*<Disabled = {'No'},[Run Date] = {'\$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count]))>25,Policy))

but no luck. I have also tried some aggr() functions but again can't crack this.

Any ideas?

1 Solution

Accepted Solutions
MVP

Sum(Aggr(

If((Sum({1*<Disabled = {'No'}, [Run Date] = {'\$(=Max([Run Date]))'}, [Firewall Type] = {'Border'}>} [Connection Count] * [SIM Score])/Sum({1*<Disabled = {'No'}, [Run Date] = {'\$(=Max([Run Date]))'}, [Firewall Type] = {'Border'}>} [Connection Count])) > 25

, 1, 0), Policy, Region))

2 Replies
MVP

Sum(Aggr(

If((Sum({1*<Disabled = {'No'}, [Run Date] = {'\$(=Max([Run Date]))'}, [Firewall Type] = {'Border'}>} [Connection Count] * [SIM Score])/Sum({1*<Disabled = {'No'}, [Run Date] = {'\$(=Max([Run Date]))'}, [Firewall Type] = {'Border'}>} [Connection Count])) > 25

, 1, 0), Policy, Region))

Anonymous
Not applicable
Author

Thanks! That works perfectly.

Community Browser