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 ch...
  • 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.

     

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.