Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter + Anti Filter for a page

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 

 

AccountProduct
A1
B2
C2
D3
D4
D2
B3
C1

 

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!

General Comment
Need Help
Tutorial Requests
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.