Checking if value exists among possible values

[#1]

Hey guys. I've got a chart I'm having trouble with. So I have a table box that displays a number of records. There's one field that has a many to one relationship to each row in the table. So any record could have multiple possible values for this field. Let's say the record is Eric, and in the field HairColor, are the possible values Brown and Gray. I want my table chart to display the record Eric if Brown is one of the possible selected values for HairColor. So I could create a calculated dimension like this:

=if(Match(HairColor,'Brown'), true(),false())

What this does, however, is create two rows for Eric, one in which the calculated dimension is true (where HairColor=Brown), and one where it is false (where HairColor=Gray). This isn't what I want. I want one row for Eric, that displays True, since Brown is one of the possible values.

Any ideas?

Thanks!

[#2]

You could either setup a calculated dimension like this:

=if(Color='Brown',Color)

Then specify to Supress when the value of the dimension Is Null.

Or you can use Set Analysis in whatever expressions are in your table to sum or count or whatever and filter where only Brown hair color is aggregated or shown.. for example:

count({$<Color={'Brown'}>} distinct Name)

[#3]

Hmm.. I don't think my original example was accurate for what I'm trying to do. Let me try a slightly different one.

Let's say I have a table like this:

load * inline

[Person, HairColor

Eric, Brown

Eric, Gray

Dad, Gray

Sister, Brown

Mom, Gray

Mom, Blonde];

Now in my chart, I want to exclude any Person that has Gray hair. So the only row that should appear would be for Sister. Eric, Dad, and Mom should not appear, since Gray is a possible value for them.

Thanks again for the help.

[swuehl]

Try a set expression with the e() function, something like

=count( {<Person = e({<HairColor ={Gray}>} ) >}  Person)

(e.g. in a chart with dimension Person).

Hope this helps,

Stefan

[Nicole-Smith]

See attached.

Straight Table:

Dimension: Person

Expression: if(aggr(concat(HairColor, ','), Person) like 'Gray', NULL(), HairColor)

[#6]

Ok so, I tried that and it seemed to be on the right track, but it doesn't actually remove the row, it just displays the count as 0 for the rows that I'd like to remove. I tried checking the Supress Zero Values checkbox, but that didn't do anything. Is there a way to make it remove the row if the count is zero?

[swuehl · Accepted solution]

In a straight table, it should do this by default settings (presentation tab in properties, suppress zero values).

Ah, this only works, if all expressions return zero for that line. So, if you have multiple expressions, use the above expression labelled Filter, then for all other expressions, write something like

= if( "Filter", YOUREXPRESSION , 0)

[#8]

Ahh so Supress Zero Values only works when all expressions for that row are zero? I didn't know that.

Very strange solution, but it appears to work.

[anguila]

Another option, if you want to check if a value is selected or possible, is to use the intersection in set analysis:

table:

Load * inline [

Values

a

b

c

d

e

];

And then, intersect current selection with the value we want to test:

count(distinct {$ * $<Values={'a'}>} Values)

Find an example attached.

Cheers,

David

[baliyan_vinay]

This solved one issue for me. 

Thank you