Count aggr distinct dimension conditionally

[Anonymous]

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?

[sunny_talwar · Accepted solution]

How about this:

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]

Thanks! That works perfectly.