Forum Discussion

RVK's avatar
RVK
Frequent Visitor
2 years ago
Solved

Visual filtering based on slicer selections

We are trying to solve the problem where we have the following:

  • Primary Product Selection slicer (single select)
    - Which gives the data of customers, Product and sales who bought the particular chosen Product from the Primary Product Selection slicer
  • Secondary Product Selection slicer (multi- select)
    - Captures the customers who did not buy the products chosen from the Secondary product selection slicer

    Below is the sample model

     



    Expected table visual is highlighted in green in the above screenshot.

    Thanks.

  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, RVK 

     

    You can try the following methods. The three tables should not establish a relationship.

    Measure = 
Var _table1=CALCULATETABLE(VALUES(Sales[CustomerID]),FILTER(ALL(Sales),[ProductID]=SELECTEDVALUE(Sales[ProductID])))
Var _table2=CALCULATETABLE(VALUES(Sales[CustomerID]),FILTER(ALL(Sales),[ProductID]=SELECTEDVALUE('Product'[Product ID])))
Var _IF=IF(SELECTEDVALUE(Sales[CustomerID]) in EXCEPT(_table1,_table2),0,1)
Return
IF(SELECTEDVALUE(Sales[ProductID])=BLANK()||SELECTEDVALUE('Product'[Product ID])=BLANK(),1,_IF)
    Sum sales = CALCULATE(SUM(Sales[Sales]),ALLEXCEPT(Sales,Sales[ProductID],Sales[CustomerID]))

    Result:

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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

     

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi, RVK 

     

    You can try the following methods. The three tables should not establish a relationship.

    Measure = 
Var _table1=CALCULATETABLE(VALUES(Sales[CustomerID]),FILTER(ALL(Sales),[ProductID]=SELECTEDVALUE(Sales[ProductID])))
Var _table2=CALCULATETABLE(VALUES(Sales[CustomerID]),FILTER(ALL(Sales),[ProductID]=SELECTEDVALUE('Product'[Product ID])))
Var _IF=IF(SELECTEDVALUE(Sales[CustomerID]) in EXCEPT(_table1,_table2),0,1)
Return
IF(SELECTEDVALUE(Sales[ProductID])=BLANK()||SELECTEDVALUE('Product'[Product ID])=BLANK(),1,_IF)
    Sum sales = CALCULATE(SUM(Sales[Sales]),ALLEXCEPT(Sales,Sales[ProductID],Sales[CustomerID]))

    Result:

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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

     

  • RVK's avatar
    RVK
    Frequent Visitor
    2 years ago

    Thank you v-zhangti  this worked.
    can we convert below variable to check for multiple SKU selections?

    Var _table2=CALCULATETABLE(VALUES(Sales[CustomerID]),FILTER(ALL(Sales),[ProductID]=SELECTEDVALUE('Product'[Product ID])))