I have the following condition as shown below. Sorry that I can't share the actual package.
Pic#1 - there is a RuleNo dragged from Dimension is applied. Data in Rule1 is considered Trigger if the square is red color(as shown in the calculated field). other color is not a trigger ( Grey - no observation, Orange - no data, Green - normal data) Here I can get a correct representation for Trigger.
My problem is when I drag out the RuleNo, the color for Trigger has change as hilited in Pic#2 below.
The calculated field I used is as below:
ELSEIF [STAT]="no data" THEN "no data"
ELSEIF [STAT]="no observation" THEN "no observation"
ELSE "normal data"
** the [STAT] is referring to another calculated field but it's kind of straight forward setting.
My question is how would the color now change to Green instead of Red. I think without the RuleNo at the Column, the calculated field won't work. How can I effectively have only the square(s) from Rule1 show up in Pic#2(similar to that of Pic#1) as this is the data I am interested in. I do not want to use filter in this case as the workbook is also join to another table hence will jeopardise the other set of data.
I was thinking that perhaps it is better for me to just use a single source table and it's easier for me to control. I query the data by setting the condition for Rule1 only and then create a calculated field to find the to check for null/not null and set the trigger accordingly. This way can solve the problem I faced.