Conditional Listbox

[Anonymous]

Hi,

I have a listbox which contains many values but there are values that I want to exclude based on a condition.

The condition itself is:

=COUNT({$<FIELD_1 = {7,8,9,14,19}>} FIELD_2) > 0 

I then have a listbox for FIELD_3. I only want values in the listbox to be visible if the above condition is true for that selection in FIELD_3. In other words for each value in the listbox, I want to only include selections in FIELD_3 that yield COUNT({$<FIELD_1 = {7,8,9,14,19}>} FIELD_2) > 0, and exclude all other values.

Does anybody know how I could achieve this? I've had a look around the forums and tried to make an expression but to no avail. I've tried the following expression as a listbox but it doesn't work at all:

IF(COUNT({$<FIELD_1= {7,8,9,14,19}>} FIELD_2) > 0, FIELD_3)

[jonathandienst]

Use this as the listbox expression:

=Aggr(If(COUNT({$<FIELD_1 = {7,8,9,14,19}>} FIELD_2) > 0, FIELD_3), FIELD_3)

[Anil_Babu_Samineni]

Aggregate functions won't work in listbox using Sum(), Count() and Avg() .. without Aggr() on field from that measure. For ex, If you have measure called Sum(Sales) where You want to show field which holds in between 2 and 20. That case, You must need Aggr() function like

If(Aggr(Sum(Sales), Field)>=2 and Aggr(Sum(Sales), Field)<=20, FieldName)

[Anonymous]

Thank you! That worked very nicely, as an aside - is there a way of keeping the values where this condition isn't satisfied but as greyed out options?

[ahmed_hassan]

You can use "background colors" for the dimensions and expressions ( click on the "+" and choose background color)

[Anonymous]

Hmmm I'm not sure that that will work as I don't have an expression, just an expression as the field for the listbox.

I guess is there a way to define the state of each value in the listbox by the previous constraint.

i.e. 

=Aggr(If(COUNT({$<FIELD_1 = {7,8,9,14,19}>} FIELD_2) > 0, FIELD_3), FIELD_3)

If this is true then the "State" should be active, if it's false then the "State" should be inactive, but it should still display in the listbox as inactive.