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?