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
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.

  • Shamatix's avatar
    Shamatix
    Post Partisan
    8 years ago
    greggyb wrote:

    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.

     

    What if its two different tables, with different columns, but has a relationship on one of the columns?

    Lets say TableA has column, A1, B1,C1,D1,E1,F1 and TableB has column A2,B2,C2


    Normally I would do Select COUNT(*) from table A where A1 not in (select A2 from TableB) but with your measure it says "Each table argument of 'EXCEPT' must have the same number of columns?