Forum Discussion

LWOLF's avatar
LWOLF
Regular Visitor
4 years ago
Solved

SELECTED VALUES IN TABLE

Hi all,

 

Could you please advice how to fix this issue?

I need to exclude customers that bought at least one of the selected products. My current solution works if you select only one product, however I need to make it work for multiple selection. thanks 🙂

My main table:

Customer IDProduct

1a
1b
1c
1d
2c
2d
2f
2j
3j
3a
4v
4c
4t
5a
5d
6f
6r
6d

 

slicer disconnected = VALUES(main[Product])
Customer concat = SUMMARIZE('main',main[Customer ID])

 

calculated column in the 'customer concat' table:

 

Product_Concat = 
 CONCATENATEX(
     FILTER(
         'main',
         'main'[Customer ID] = 'Customer concat'[Customer ID]),'main'[Product],
         " ; ",'main'[Product],
         ASC)

 

Measure :

 

Products_exclusion = 
VAR _customer = SELECTEDVALUE('main'[Customer ID])
VAR _custmomer_products = SUMMARIZE(FILTER('main','main'[Customer ID]=_customer),main[Product])
VAR _selected_products = ALLSELECTED('slicer disconnected'[Product])
RETURN
IF(
    ISFILTERED(
        'slicer disconnected'[Product]),
IF(     
    _selected_products  IN _custmomer_products,
    0,
    1
    ),
    1)

 

This is my main visual that needs to be filtered correctly based on the slicer selection.

data model: 

 

 

  • LWOLF's avatar
    LWOLF
    4 years ago

    thanks amitchandak . I actually managed to solve it after many hours of thinking! 😄 

     

    Products_exclusion = 
VAR _table = CALCULATETABLE(SUMMARIZE('main','main'[Customer ID]), FILTER(ALL('main'),'main'[Product] IN FILTERS('slicer disconnected'[Product])))
RETURN
IF(
    ISFILTERED(
        'slicer disconnected'[Product]),
IF(     
    MAX('Customer concat'[Customer ID]) IN _table,
    0,
    1
    ),
    1)

     

3 Replies

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

    LWOLF , Make sure independent table for the product is used in slicer, I changed the name to product

     

    try measure

     

    Customer concat =
    var _cust = except(all(main[Customer ID]) , SUMMARIZE(filter('main',main[Product] in VALUES(Product[Product])), main[Customer ID]))
    return
    countrows(filter(main, main[Customer ID] in _cust ))

     

    plot this with customer

    • LWOLF's avatar
      LWOLF
      Regular Visitor
      4 years ago

      thanks amitchandak . I actually managed to solve it after many hours of thinking! 😄 

       

      Products_exclusion = 
VAR _table = CALCULATETABLE(SUMMARIZE('main','main'[Customer ID]), FILTER(ALL('main'),'main'[Product] IN FILTERS('slicer disconnected'[Product])))
RETURN
IF(
    ISFILTERED(
        'slicer disconnected'[Product]),
IF(     
    MAX('Customer concat'[Customer ID]) IN _table,
    0,
    1
    ),
    1)