Exclude Slicer when selected - but the column is not used in the table

[Anonymous]

Hi 

I have a requirement, In which I have to use customer name as a slicer which should exclude in the table.

so for eg if i select a customer then the respective product id & product description & Net sales should be excluded.

I have implemented a DAX like this,

Exclude_selection =
IF(
SELECTEDVALUE(DimCustomer[CustomerName]) IN ALLSELECTED(Customer[CustomerName]) && COUNTROWS(ALLSELECTED(Customer[CustomerName])) <> COUNTROWS(ALL(Customer[CustomerName])),1,0)

and have created a disconnected distinct customer name table and used that as a slicer.

But as customer name is not present in the table. it is not getting filtered out.

Relationship for the report is 

relationship:

Dimcustomer has customer ID -> Fact sales Customer ID

Dimproduct Product ID -> Fact Sales Product ID

Can Anyone provide with a solution?. 

Thanks & Regards

[v-henryk-mstf]

Hi @Anonymous ,

You can create a filter for the customer table (with no relationship between it and the main table) from a virtual table. Then create the following formula.

Slicer_ = VALUES(DimCustomer[Customer])

slicerCustomer =
VAR slicer_c =
    SELECTEDVALUE ( Slicer_[Customer] )
RETURN
    ( IF ( slicer_c = MAX ( FactSales[Customer] ), 1, 0 ) )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Hi @v-henryk-mstf,

Thanks for your reply, however When I add customer name in the table the result is achieved but my based upon my requirement Customer name is not present in the table.  

if we try to filter then then the expected result is not achieved 

for eg,

if i try to filter Mark , then it should exclude MArk and produce product Id & Net Sales Excluding mark 

then the result should be ,

ProductID  NetSales

1                 10

2                  20

3                   60

but the result produced is