Multiple Slicers + Filter Customer who haven't bought selected item (in DAX)

[Asantos2020]

Hello!

I have the following problem I can't get my head around and ended up here to ask for your help:

Slicer 1: Year (from a Calendar Table)

Slicer 2: Month (from a Calendar Table)

Slicer 3: Customer Category (From Sales Table, whose Date Column is related to Calendar Date)

Slicer 4: Product (separate table, summarized from Sales Table)

When the user selects Year, Month, Customer Category and the Product, the table below them should list the customers who have not bought that product within that period of time. 

Currently, the table lists the customers who haven't bought the product, but only the ones we sold something to and didn't include the selected item. Instead, it should list all of the customers who have not bought that item in the selected period of time.

Here is the DAX formula used for now:

Customers who didn't buy = IF(
HASONEVALUE(Product[Object]);
CALCULATE(
DISTINCTCOUNT(Sales Table[CustomerID]);
FILTER(
Sales Table;
NOT(CONTAINS(Sales Table;Sales Table[Object];VALUES(Products[Object])))
)
);
DISTINCTCOUNT(Sales Table[CustomerID])
)

Any help is greatly appreciated!

[v-frfei-msft]

Hi @Asantos2020 ,

To create another measure and make the visual filtered by it as the picture below.

Measure 2 = 
VAR a =
    VALUES ( 'Product'[Product] )
RETURN
    IF ( MAX ( Sales[Product] ) IN a, 1, BLANK () )

[Asantos2020]

Hello again @v-frfei-msft !

Sorry if this is stretching your "good faith", but there is one more request:

The list of customers who didn't buy that item should consist of all the customers in the system, instead of only the ones who bought something that year/month.

How to include that - I wonder.

PS: I'm definitely going after a course on DAX, Power BI, etc in the near future, so I can help more than ask for it.

[v-frfei-msft · Accepted solution]

Hi @Asantos2020 ,

Sorry for missing the key point. We can achieve that by two ways.

1. To create a calculated table and work on that by a measure.

new sales = Sales

new filer = 
VAR a =
    VALUES ( 'Product'[Product] )
RETURN
    IF ( MAX ( 'new sales'[Product] ) IN a, BLANK(),1 )

Alternatively, we can create a measure and display the product by a card.

Measure 3 = 
VAR a =
    VALUES ( 'Product'[Product] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( Sales[Product] ),
            FILTER ( ALL ( Sales ), ( Sales[Product] IN a ) = FALSE () )
        ),
        Sales[Product],
        " "
    )

For more details, please check the pbix as attached.

[v-frfei-msft]

Hi @Asantos2020 ,

To create a measure as below.

Measure = 
VAR proj =
    VALUES ( 'Product'[Product] )
VAR infilter =
    CALCULATE (
        DISTINCTCOUNT ( Sales[CustomerID] ),
        FILTER ( Sales, Sales[Product] IN proj )
    )
VAR alno =
    CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ), ALL ( Sales ) )
RETURN
    IF ( ISFILTERED ( 'Product'[Product] ), alno - infilter, BLANK () )

[Asantos2020]

Hello @v-frfei-msft !

Thanks for taking the time to help.

With the intent to confirm, I see below a list of 05 customers, but this includes a customer who did buy P1 in April. Shouldn't we see a list of the ones who bought other items, but the one filtered? 

Again, thank you in advance!