Forum Discussion

Sebastian's avatar
Sebastian
Icon for Advocate III rankAdvocate III
10 years ago

function like "not in"

Hi all,   did there still exist a function like "not in"?   I try to find values which are maybe in the one table but not in another one.   could someone help?   Thanks.
Need Help
Sebastian's avatar
Sebastian
Icon for Advocate III rankAdvocate III
10 years ago

Hey. Thanks for your answer.

 

The except function doesn't work.

 

My problem:

 

I have a table (table 1)  which consist of all customer information and a second table (table 2) which consist of sales information.

I try to find all customer who are in table 1 but not in table 2.

greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

As a query:

EVALUATE(
    CALCULATETABLE(
        DimCustomer
    )
    ,EXCEPT(
        VALUES( DimCustomer[CustomerKey] )
        ,VALUES( FactSale[CustomerKey] )
    )
)

As a measure you can use in a visualization:

Customers without Sales =
COUNTROWS(
    CALCULATETABLE(
        DimCustomer
        ,EXCEPT(
            VALUES( DimCustomer[CustomerKey] )
            ,VALUES( FactSale[CustomerKey] )
        )
    )
)

You could use anything in a CALCULATE() with that EXCEPT() as a filter, but I think COUNTROWS() of the resulting DimCustomer is appropriate, because you could use a customer hierarchy and see how many customers in various groups have no purchases. This will also work with filters on your date dimension, so you could filter to a specific date and see customers without sales on that date.