dax table: list of customers who did not buy a particular product line

[melvincv]

Hi,

I'm still learning table functions, so please help me...

I have the following columns in my data table:

Invoice No.

Customer Name

Product Line - A, B, C, D, E

Revenue

I need a DAX table formula to get a column with the list of Customers who did not buy a particular product line (B)

I tried this

ZeroCustomers = SUMMARIZECOLUMNS('US Sales'[CustomerName],'US Sales'[Productline],FILTER('US Sales','US Sales'[Productline]<>"B"))

But it gives me the customers who bought B also...

Please help me at the earliest.

[Eric_Zhang · Accepted solution]

---

@melvincv wrote:

Hi,

I'm still learning table functions, so please help me...

I have the following columns in my data table:

Invoice No.

Customer Name

Product Line - A, B, C, D, E

Revenue

I need a DAX table formula to get a column with the list of Customers who did not buy a particular product line (B)

I tried this

ZeroCustomers = SUMMARIZECOLUMNS('US Sales'[CustomerName],'US Sales'[Productline],FILTER('US Sales','US Sales'[Productline]<>"B"))

But it gives me the customers who bought B also...

Please help me at the earliest.

---

@melvincv

You can try to create a calculated table as

Product = VALUES(Table1[Product Line])

and a measure as

Not Purchase the selected Product =
IF (
    ISFILTERED ( 'Product'[Product Line] )
        && HASONEVALUE ( 'Product'[Product Line] )
        && COUNTAX (
            FILTER (
                Table1,
                Table1[Product Line] = LASTNONBLANK ( 'Product'[Product Line], "" )
            ),
            Table1[Customer Name]
        )
            >= 1,
    FALSE (),
    TRUE ()
)

See more details in the attached pbix.

[bsas]

Hi,

You can use function IF [e.g IF(and(Product Line="B",revenue<>0 "or you can use >0"),Customer Name,"")

I do not know whether you have two categories in 1 column.

[melvincv]

How do I exclude the values of the Customer Name column?

I have

Table1 - CustomerName, Product Line

Table2 - CustomerName, Product Line

I need Table3 with Table1[CustomerName] - Table2[CustomerName]

[bsas]

What infor should be represented in these 2 tables (1 and 2)?