Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dpicinin
Frequent Visitor

Customers that DO NOT buy specific categories

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.

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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

 

smpa01_0-1638308002830.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@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

 

smpa01_0-1638308002830.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
OwenAuger
Super User
Super User

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:

OwenAuger_0-1638306913098.png

 

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:

OwenAuger_1-1638307166981.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.