Question :

For a state,there are four assets.Total no of outliers has been calculated for each asset.If the total no of outliers are above certain threshold,the field would be highlighted as Red,green and amber accordingly.

And the next step of requirement is if any one of the asset is Red,the country dimension field should be highlighted as Red which indicates that an asset in this particular state is in critical condition.

Whatever Done

I have done the below calculated field to achieve the first set of Question

``IF [Total Outliers] > 50 THEN "RED" ELSE IF [Total Outliers] > 45 THEN "AMBER" ELSE "GREEN" END END ``

and got the result like this (Below image) BUt I dont want the asset code to be shown in the report.If i remove the field Asset Code from the shelf,all the value changes to red color because its getting aggregated for State (Total outlier count gets sum up and exceeds 50 according to my calculated field)

i want in such a way

For eg : Maryland has four Asset ->  one of them is "RED" and other three are "GREEN"

So Now IF any one of the Asset indicates RED then whole Maryland Should be RED IF not AMBER , IF not GREEN

So i want some output like this attached below

I tried so much even like below code

``IF ( ATTR([State])="Florida" AND ((ATTR([Asset Code]) = "LM 3400" AND [Outlier Analysis_2]= "RED") OR (ATTR([Asset Code]) = "PG 8167" AND [Outlier Analysis_2]= "RED")) ) OR ( ATTR([State])="Kansas" AND ((ATTR([Asset Code]) = "FS 3025" AND [Outlier Analysis_2]= "RED") OR (ATTR([Asset Code]) = "FS 8245" AND [Outlier Analysis_2]= "RED") OR (ATTR([Asset Code]) = "MS 1945" AND [Outlier Analysis_2]= "RED") OR (ATTR([Asset Code]) = "PG 6013" AND [Outlier Analysis_2]= "RED")) ) then "RED_1" END ``

but all i get is "RED" for all states whenever i remove ASSETCODE..Where am i going wrong? Is it Possible?

Thank You.