powerpivot function like "NOT IN" of sql

Question

Let's say there is three table. DimItem, DimCustomer and FactSale. How could i find the customers who don't buy specific item? it looks like 'NOT IN' of sql.

Answer 1

Assuming a pivot table or similar tool as the reporting mechanism:

CustomersNotPurchasing:=
IF(
    ISEMPTY( FactSale )
    ,"No Purchase of selected item(s): " 
        & CONCATENATEX(
            VALUES( DimProduct[Product] )
            ,DimProduct[Product]
            ,"; "
        )
)

This measure will return a string consisting of the text, "No Purchase of selected item(s): " concatenated with a semicolon-delimited list of the selected products, only for those customers who did not purchase any of the selected products. It returns blank for those customers who did purchase any of the products, and is therefore omitted from a pivot or similar reporting tool.

Relationships are N:1 between FactSale[CustomerKey], DimCustomer[CustomerKey] and between FactSale[ProductKey], DimProduct[ProductKey].

This measure assume pivot table row / column labels from DimCustomer, and a slicer / filter selection from DimProduct. The logic will play well with any other filters on your FactSale table. E.g. if you filter to a specific month, you'll see results only for those customers who did not purchase any of the selected products in the selected month.

ISEMPTY() checks whether a table has 0 rows (True if 0, False if >0). Since this is a measure in a pivot table, FactSale is evaluated in filter context, i.e. filtered to only the customer on the pivot row and the item(s) selected in the product slicer.

Our IF() result for True is the string literal, along with a concatenation of all values selected in DimProduct[Product].

The else result for our IF() is omitted, meaning that the measure returns blank is ISEMPTY() returns false.

Demo workbook.