Customers that DO NOT buy specific categories

[dpicinin]

Hello,

It seems easy, but I am having a hard time figuring it out =/

I have some categories of products (Salmon, Mussels, Shrimp...). Some clients buy products from more than one category.

But I am interested in knowing the clients' names that DO NOT buy, for example, Shrimp (they buy only Salmon and Mussels).
I would like that info when selecting Shrimp on a slicer.

Thanks in advance.

[smpa01 · Accepted solution]

@dpicinin  does this help?

Measure =
VAR _1 =
    CALCULATE (
        MAXX (
            FILTER ( 'Fact', 'Fact'[CAT] IN ALLSELECTED ( Slicer[CAT] ) ),
            'Fact'[Cust]
        ),
        ALLEXCEPT ( 'Fact', 'Fact'[Cust] )
    )
VAR _2 =
    CALCULATE ( MAX ( 'Fact'[CAT] ), FILTER ( 'Fact', NOT 'Fact'[Cust] IN { _1 } ) )
RETURN
    _2

[OwenAuger]

Hi @dpicinin 

I would recommend you set up your data model with Customer and Product dimensions.

Then you can write a measure that returns a specific value if the fact table (I'll call it Sales) is empty, and create a visual grouped by customer that filters on that measure.

I have attached an example:

Model diagram:

This is the Sales Empty measure, that returns 1 if the Sales table is empty, otherwise zero:

Sales Empty = 
INT ( ISEMPTY ( Sales ) )

Report page, with the rightmost table filtered to [Sales Empty] = 1:

The Sales Empty measure is simply checking whether the Sales table is empty, so if multiple Product Category selections are made, it will return 1 for Customers that didn't purchase any of the selected Categories.

Hopefully this is useful!

Regards,

Owen

[dpicinin]

I tried to reproduce your approach @OwenAuger , but for some reason it didn't work on my model.
Then I tried @smpa01 suggestion and it worked fine. Appreciate your time guys.