How do I create a NOT filter?

[jmkvalsund]

Fairly simplified I have two tables, Customers (1 column, CustomerID) and CustomerProducts (2 columns, CustomerID and ProductID). These are related by CustomerID, so that selecting a product on a slicer on ProductID shows the customers having that product.
But how can I invert it, so that selecting a ProductID shows the customers who does not have that product?

Regards,

John Martin

[Jihwan_Kim · Accepted solution]

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
    )

[Mikelytics · Accepted solution]

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.

[Mikelytics · Accepted solution]

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.

[Mikelytics · Accepted solution]

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.

[jmkvalsund]

Thanks, that solved the problem. But then I try to step up a bit:
I replace the product-table with a deal-table, consisting of customerID, contractID, productID and amount.
The relationsship between customers and deals are one-to-many on customerID, a customer can have any number of deals.
Instead of slicing on the whole producttable, I now create a slicer on products from the deal-table.  How do I make the slicer work so it shows only customers not having the selected product? I set up two table-visuals, one containg customers-table, the other deals-table. When I show all the deals, and adding your measure to the customers-visual, I get all the customers that does not have a deal (and hence none of the products), but as soon as I try to slice on products, it doesn't show any customers at all. Do you have any suggestion for this challenge?

Regards,

John Martin

[Mikelytics]

Hi @jmkvalsund,

Thank you for your feedback! 🙂

To make sure that I do not lose track and give feedback on wrong assumptions could you please share a picture of the current data model (only the relevant tables incl. relations), the measure you now use as well as the visual you want to create?

Best regards

Michael

[jmkvalsund · Accepted solution]

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

[Jihwan_Kim · Accepted solution]

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
    )

[jmkvalsund]

Thanks a lot, this solved my case 🙂

[Mikelytics]

Hi John,

Lets say you put the the customer column into a matrix and the product column in a slicer then this measure should do it when you also put it into the matrix.

 Customers not having the product = IF(COUNTROWS('Customer Table') > 0 , BLANK(), 1) 

I have the assumption that the product table filters the customer table. (filter direction). IF thats not the case please try something like this. 

Customers not having the product = 
CALCULATE( 
   IF(COUNTROWS('Customer Table') > 0 , BLANK(), 1),
   CROSSFILTER(ProductTable[CustomerColumn], ProductTable[CustomerColumn], BOTH) 
) 

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.

[Mikelytics · Accepted solution]

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.