function like "not in"

[Sebastian]

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.

[Anonymous]

Hi Sebastian,

   you can try the EXCEPT function ( https://msdn.microsoft.com/en-us/library/mt243784.aspx ), but watch out because the two tables must have the same dimension.

Have a good coding

[Sebastian]

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.

[Greg_Deckler]

You might want to solve this problem in "M" versus DAX. You can control the way "M" joins things.

http://blog.crossjoin.co.uk/2014/06/02/join-conditions-in-power-query-part-1/

[greggyb]

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]

---

@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?