Filter + Anti Filter for a page

[Anonymous]

Hi All, 

Im looking to create a global filtering system that will allow me to filter certain products and NOT others. For example:

I have a list of accounts and products they have purchased. I want to be able to have two product filters; the first would filter all accounts who HAVE purchased said product (simple enough), but the second would filter that same list, but only show those who met the first criteria and do NOT have the second product filtered. here's an example with data 

Account	Product
A	1
B	2
C	2
D	3
D	4
D	2
B	3
C	1

Filter 1 : Product 1, Would only show Account A and C

Filter 2: Product 2. Since Account C does have Product 2 and Product 1, only show Account A

Thanks for any and all help!

[v-xiaotang · Accepted solution]

Hi @Anonymous 

use this measure

filter1measure = 
var _sel=SELECTEDVALUE(Table2[Product])
return IF(ISBLANK(_sel),1,IF(MIN('Table'[Product])=_sel,1,0))

filter2measure = 
var _sel=SELECTEDVALUE(Table3[Product])
var _Account=SELECTEDVALUE('Table'[Account])
var _countAccount1=CALCULATE(COUNT('Table'[Account]),FILTER(ALL('Table'),'Table'[Product]=_sel&&'Table'[Account]=_Account))
var _countAccountAll=CALCULATE(COUNT('Table'[Account]),FILTER(ALL('Table'),'Table'[Account]=_Account))
return IF(ISBLANK(_sel),1,IF(MIN('Table'[Product])=_sel&&_countAccount1=_countAccountAll,1,0))

result

 

---

if problem still persists, please let me know.  (please @ my ID, so that I won't miss the message.)

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang]

Hi @Anonymous 

Have you solved your problem? If yes, kindly accept the answer helpful as the solution. so the others can find it more quickly.

Or

if problem still persists, please let me know.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , the second slicer need to be on an independent table 

Try measure like this for second  

measure =

var _table = summarize(filter(Table, Table[Product] in selectedvalue(product2[product])), Table[Account])
return
calculate(count(Table[Product]), filter(Table, not(Table[Account] in _table)))

[Anonymous]

@amitchandak , I tried this measure and am using a separate table for filtering, but the Measure is just returning a count....Would i then filter on Measure = 0?