Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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))
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))
Thanks! That works perfectly.