How do I create a NOT filter?

Hi,

Please check the below picture and the attached pbix file whether it suits your requirement.

In the sample, I created disconnected table in order to create product slicer, and the below measure in order to put it in "Filters on this visual"

Flag measure: =
VAR _productselectlist =
    DISTINCT ( 'Product'[Product] )
VAR _customerlist =
    SUMMARIZE (
        FILTER ( CustomerProduct, CustomerProduct[Product] IN _productselectlist ),
        Customer[Customer]
    )
VAR _notcustomerlist =
    EXCEPT ( ALL ( Customer[Customer] ), _customerlist )
RETURN
    IF (
        COUNTROWS ( FILTER ( Customer, Customer[Customer] IN _notcustomerlist ) ) > 0,
        1
    )

Hi John,

I took some more time to verify my approach and it should definetely work.

First column are actual sales and the second column is my measure approach for the NOT filter.

As soon as I take out the first measure only the NOT measure is left and only the customers without the product come up

I even used it on a little bit more complex case with the following data model:

Here is the formula I used in the model above:

Relationship Function | CROSSFILTER | Customers not having a selected product = 
CALCULATE(
    IF(COUNTROWS(DimProduct)>0, BLANK(),1),
    CROSSFILTER(FactOnlineSales[ProductKey],DimProduct[ProductKey],BOTH)
)

I used the CROSSFILTER function to make sure that the filter context starting from Product also goes through the relationship between Fact table and Customer.

Best regards

Michael

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

AS in the solution from Jihwan you can also put the measure in the visual filter

Best regards

Michael

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

Hi Michael, 
This is a screenshot, as you see it a mix of english and norwegian (ok, you probably didn't recognize that 🙂 )

Quick glossary:
kundenr = CustomerID
SkadeforsikringsavtaleID = contractID
Produktnavn = productID
Premiebelop = amount

So the simple goal is to be able to slice on products to show customers who do NOT have the selected product(s).

Regards,

John Martin