Filter for a Dimension

[#1]

Hi All

I am trying to out a filter onto a dimension where I only want to see a subset of a field.

I want to display the categories HAIR, ACCESSORIES and WATCH from a list of categories that are longer.

I cant get this to work. I have tried a few different methods but havent gotten a method to work yet. I have tried the only function where I have written Only({<Item.MainCategory={'HAIR,ACCESSORIES,WATCH'}Item.MainCategory) and this is not working. I get that there is an error in the expression.

I am stuck with this task at the moment. I dont know what how to write to add that filter onto the dimension. I am thankful for any help.

//Fredrik

[abeyphilip]

Hi,

if(match(Item.MainCategory,'HAIR','ACCESSORIES','WATCH'), Item.MainCategory)

Regards,

Abey

[mikecrengland]

I'm sure there's a better way, but you can do

If(Item.MainCategory = 'HAIR' OR Item.MainCategory = 'ACCESSORIES' OR Item.MainCategory = 'WATCH', Item.MainCategory)

Mike

www.fortunecookiebi.com

[stevedark · Accepted solution]

Hi Fredrik,

I would always recommend doing things like this in the load script, creating a separate field for the subset fields.  The syntax would be something like:

LOAD

     Category,

      if(Category = 'HAIR' or

          Category = 'ACCESSORIES' or

          Category = 'WATCH', Category, null()) as [Subset Category],

     [... rest of load statement ...]

If you would prefer just to do it in the dimension (as a Calculated Dimension) or a List Box (as an expression) the syntax is:

=aggr(maxstring({<Category={'HAIR','ACCESSORIES', 'WATCH'}>}Category ), Category)

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

[#5]

Thank you. I didnt know you could write it with aggr and maxstring. I prefer to have it as an calculated dimension.

[#6]

Thank you for your input. I prefer to avoid writing the expression with an if clause when possible. The application is am working with is a very large and heavy application.

[#7]

Thank you for your input. I prefer to avoid writing the expression with an if clause when possible. The application is am working with is a very large and heavy application.

[stevedark]

No worries.  You mention that you have a large data set - the extra field will be slightly more efficient - but it would make your data model less tidy and could make selections a bit less obvious.  The calculated dimension should work fine though.

- Steve