List customers who have not bought a specific Item

[BobKoenen]

Hi You all,

I have a customer table with certain Items in it but no amounts. I am trying to generate a list of all customers who have NOT bought a certain Item. 

My data looks like this:

Customer	Item
A	Bike
A	Car
B	Bike
B	Bike
C	Car
C	Train
D	Airplaine

based on this data I would like a list of all customers who have not bought a Car. I am thinking in the of creating an If statement with true or False but I cannot quite work it out. 

Customers with no Car

B

D

Hope you can help me. 

[Icey · Accepted solution]

Hi @BobKoenen ,

Please check:

1. Create a Item Slicer table.

Item Slicer = VALUES('customer table'[Item])

2. Create measures.

Measure = 
VAR SelectedItem =
    SELECTEDVALUE ( 'Item Slicer'[Item] )
VAR Count_ =
    CALCULATE (
        COUNTROWS ( 'customer table' ),
        'customer table'[Item] = SelectedItem
    )
RETURN
    IF ( Count_ >= 1, 1, 0 )

Title = "Customers with no " & SELECTEDVALUE('Item Slicer'[Item])

3. Create a table visual.

Then, you will get this:

BTW, .pbix file attached.

Best Regards,

Icey

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

[BobKoenen]

This is awsome!.

Thankx alot!

[amitchandak]

@BobKoenen ,Try

new Table = except(all(Table[customer]),filter(Table[Customer],Table[Item] ="Car"))

new Measure =
var _m1 =except(all(Table[customer]),filter(Table[Customer],Table[Item] ="Car"))
return
calculate([Measure], filter(Table,Table[customer] in _m1))