Data filtering in expression

[victor_jacquens]

Hello,

I have an app where I keep track of the stock of some products, and a few of them have a limit on their stock.

I want to create a dimension that only keeps data for these limited products, but I have a problem when writing the expression. I want to keep the 'Class 1' products with category '2' or '3' and the 'Class 5' products with category '3' since they are the only one with a stock limit.

Here's my expression:

=if( [Product Class] = 'Class 1' and [Product Category] = ('2','3'), 'Class 1 Cat.2&3',
if( [Product Class] = 'Class 5' and [Product Category] = '3', 'Class 5 Cat.3'))

The filtering on Class 5 is working fine but with Class 1, I can't seem to filter on 2 different categories.

What should I modify in my expression?

[GaryGiles · Accepted solution]

There are a couple of ways to approach this.  One is to use the Match() function, like this:

=if( [Product Class] = 'Class 1' and Match([Product Category],'2','3')>0, 'Class 1 Cat.2&3',
if( [Product Class] = 'Class 5' and [Product Category] = '3', 'Class 5 Cat.3'))

Basically, the Match() will return the position of the [Product Category] value in the list provided (in this case '2,'3') and returns 0 if it is not found.

[victor_jacquens · Accepted solution]

Thank you,

I just found another solution, which is:

=if( ([Product Class] = 'Class 1' and [Product Category] = '2') or ([Product Class] = 'Class 1' and [Product Category] = '3'), 'Class 1 Cat.2&3',
if( [Product Class] = 'Class 5' and [Product Category] = '3', 'Class 5 Cat.3'))

[GaryGiles]

Like I said, there are multiple approaches.  The Match() function approach has the advantage of being easier to read/write/change, especially if you had many [Product Class] values that you wanted to include.