Find Customers who did NOT puchase X product(s)

[bkwohls]

As a FNG to PBI:

I have used the DAX formula below successfully to determine customers who have NOT purchased a specific product. What I NEED to do is correct this DAX to allow for the selection of multiple products. When multiple values are selected in my slicer I get an error

For Context

'Sales 4y' is my Sales Table

'Part DNS' is my list of Parts that I want to use as my DID NOT SELL list for use in a slicer

DID NOT SELL =

CALCULATE ( [Total Sales], FILTER ( 'Sales 4y', NOT ( CONTAINS ( 'Sales 4y', 'Sales 4y'[PartNum], VALUES ( 'Part DNS'[PartNum] ) ) ) )

The end visual is a Simple Table that shows the Total Sales for the Customers who did not purchase the selected items. Customer Name is in the Sales 4Y table. 

[bkwohls]

Brilliant - Many thanks!

[Anonymous]

Hi @bkwohls ,

Could you please mark the helpful post as Answered if the problem has been resolved? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[AlB]

Hi @bkwohls 

I don't follow completely but based on your current measure, try this:

DID NOT SELL =
VAR slicerVals_ =
    VALUES ( 'Part DNS'[PartNum] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( 'Sales 4y', NOT 'Sales 4y'[PartNum] IN slicerVals_ )
    )

 You might need an ALL('Sales 4y') as based for the filter or something else depending on how/where you use the measure

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[bkwohls]

This works to display the sales for a customer that are not in the Did Not Sell list, However I want the [Total Sales] for the customers that never sold the part.

[AlB · Accepted solution]

@bkwohls 

DID NOT SELL =
VAR slicerVals_ =
    VALUES ( 'Part DNS'[PartNum] )
VAR customerList_ =
    EXCEPT (
        DISTINCT ( 'Sales 4y'[CustomerID] ),
        CALCULATETABLE (
            DISTINCT ( 'Sales 4y'[CustomerID] ),
            FILTER ( 'Sales 4y', 'Sales 4y'[PartNum] IN slicerVals_ )
        )
    )
RETURN
    CALCULATE ( [Total Sales], customerList_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@bkwohls , Not very clear. But you use this meausre with customer and product if will give product not purcahsed by customer. You can use slicer filter too

not sold measure =

if(isblank( [Total Sales]),1,blaknk())

[bkwohls]

In 'Sales 4Y' Table I have fields Partnum,Customer, Sales $,Date. In 'Part DNS' table it is just a list of part numbers with no relation to Sales table. I want to use 'Part DNS' table as a slicer for user to select the part(s) to find customer who did not sell them. Is that more clear? How can I make more clear?