Forum Discussion

Sebastian's avatar
Sebastian
Advocate 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

5 Replies

    • Sebastian's avatar
      Sebastian
      Advocate 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
        Resident 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.