How do I identify customers, who never bought an Item of specific cateogry?

[Anonymous]

Hi,

we have the following table: 

Customer	ItemCategory
A	Ticket
A	Merchandise
B	Merchandise
C	Ticket

This table has more then a thousand entries. I would like to filter all the customers who never bought an Item from the Ticket category. In this case, It should return B, as this customer only bought merchandise.

Any tips on how to achieve that?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please create a measure like this.

Measure = 
VAR x =  
CALCULATE(
    COUNT(Sheet2[ItemCategory]),
    FILTER(
       ALL(Sheet2),
        Sheet2[Customer] = MAX(Sheet2[Customer]) && Sheet2[ItemCategory] = "Ticket"
    )
)
RETURN
IF(
    x = BLANK(),
    MAX(Sheet2[Customer])
)

Best regards,
Lionel Chen

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

[harshnathani]

Hi @Anonymous ,

You can also try this measure

Customer who didn't by  Ticket =
VAR allcustomers =
    VALUES ( 'Table'[Customer] )
VAR BoughtTicket =
    CALCULATETABLE (
        VALUES ( 'Table'[Customer] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ItemCategory] = "Ticket"
        )
    )
RETURN
    COUNTROWS (
        EXCEPT (
            allcustomers,
            BoughtTicket
        )
    )

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

Hi @Anonymous ,

You can try this measure.

Measure =

VAR Cond1=Countrows(VALUES('Table'[Customer]))

VAR Cond2=Countrows(CALCULATETABLE(VALUES('Table'[Customer]),Filter(ALL('Table'[ItemCategory]),'Table'[ItemCategory] IN {"Ticket"})))

RETURN

If(AND(Cond1>=1,Cond2=0),"Yes","No")

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[mahoneypat]

A measure like this should work for you

Customers who did not buy tickets =
COUNTROWS (
    FILTER (
        VALUES ( Table[Customer] ),
        ISBLANK ( CALCULATE ( COUNT ( Table[Category] ) ) )
    )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nvprasad]

Hi

Can you try below.

CustomerCount(ExceptTicket) =
CALCULATE ( COUNTROWS ( Customer ), Customer[ItemCategory] <> "Ticket" )

Regards,

Venkata Nalla