Secondary filter to exclude values from measures

[therealdees]

Hi,

I have a table for customer behavior analysis that the user can filter clothing brands and see data only for those who have purchased such brand, e.g NIKE.

What I want to do is add an additional filter for exclusion: if the user has selected NIKE in the primary filter and ADIDAS in the secondary filter, it should show only data for customers that have bought NIKE but haven't bought ADIDAS.

I've found some posts about the subject but I couldn't make it work. The closest I got was using alternate states to the exclusion filter and pointing out the state in the set analysis.

What is the best way to achieve this?

[Clement15 · Accepted solution]

For the second input variable you just have to choose another variable here vTest2.

In the values the &'| ' after the Concat allows you to add an empty value allowing you to not have a filter

[therealdees · Accepted solution]

Hi, @Clement15 

Thanks for the reply!

I'm trying to understand your solution, but I'm a bit confused, could you explain me some more?

1. Do I have to work with variable inputs in both cases? Or could I use a filter in the first case (or maybe in both)? I ask this, because the user might want to filter more than a brand (e.g customers that have bought Nike or Lacoste) and exclude another (possibly more than one brand as well, e.g: that haven't bought Adidas and CK)

2. If I'm understanding it right, you're multiplying the P() by the E() in the expression, if that's right, why?

3. What did you add to the variable? Is it a blank/null value and the values are then fulfilled with the Concat(Distinct Brand, '|')&'|'? If so, is this the complete expression in the dynamic values or is there more that is not being show in the picture?

[therealdees · Accepted solution]

You gave me an insight using concat() and I managed to achieve what I wanted with a different approach. GetFieldSelections() will also concatenate the values and I can use it inside the set analysis

For those who it might help, I used alternate states and getfieldselections:

Filter 1 = product_brand (inherited state, no need for changes)

Filter 2 (exclusion) = I created an alias field (product_brand_exc) and added a different state (e.g BrandExclusion) [maybe it's not necessary and you could use the same product_brand as it's using a different state]

Then in the measure expression you could use GetFieldSelections() to retrieve the selected values in Filter 2:

Count(Distinct {<key_customer = E({BrandExclusion<product_brand = {$(=chr(39)&GetFieldSelections(product_brand_exc,''',''',10,'BrandExclusion')&chr(39))}>} key_customer)>} sale_id)

Note that for this to work, you must use chr(39) (single quote) outside the GetFieldSelections and not use double quotes in the {} set analysis, plus single quotes before and after the delimiter (as single quote would actually comment the code, you must use the escape method to identify the single quote as a string instead of a "function": ''','''

@Clement15  thanks for your help