Forum Discussion

aiton_grant's avatar
aiton_grant
New Member
8 months ago
Solved

Negating the IN Operator

Hello! I have an issue combining the NOT operator and IN operator. Imagine a table of purchases with customer ids and product ids, and two dimension tables of product details and customer details. I...
Need Help
  • Praful_Potphode's avatar
    Praful_Potphode
    8 months ago

    Hi aiton_grant 

    Try below:

    Customers measure = 
CALCULATE(
    DISTINCTCOUNT('Purchase FACT'[Customer ID]),
    FILTER(
        VALUES('Purchase FACT'[Customer ID]),
        VAR HasBikeOrScooter = 
            CALCULATE(
                COUNTROWS('Purchase FACT'),
                'Product Detail'[Product Category] IN {"BIKE", "Scooter"}
            ) > 0
        VAR HasCar = 
            CALCULATE(
                COUNTROWS('Purchase FACT'),
                'Product Detail'[Product Category] = "CAR"
            ) > 0
        RETURN
            HasBikeOrScooter && NOT(HasCar)
    )
)

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

  • Selva-Salimi's avatar
    Selva-Salimi
    8 months ago

    Hi aiton_grant ,

     

    I think you want to count the customers that never had bought any car. So you can write a measure as follows:

     

    Measure = var buy_car=SUMMARIZE(FILTER(Purchase_Fact,Purchase_Fact[Product]="Car"),Purchase_Fact[CustomerID])
    return
    CALCULATE(DISTINCTCOUNT(Purchase_Fact[CustomerID]), FILTER(Purchase_Fact,not(Purchase_Fact[CustomerID] IN buy_car) && Purchase_Fact[Product] in {"Scooter", "Bike"}))
     
    If you just have 3 products (Bike, Scooter, Car) then you also can omit the last part in the measure (&& Purchase_Fact[Product] in {"Scooter", "Bike"}))
     
    If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.
Selva-Salimi's avatar
Selva-Salimi
Solution Sage
8 months ago

Hi aiton_grant ,

 

I think you want to count the customers that never had bought any car. So you can write a measure as follows:

 

Measure = var buy_car=SUMMARIZE(FILTER(Purchase_Fact,Purchase_Fact[Product]="Car"),Purchase_Fact[CustomerID])
return
CALCULATE(DISTINCTCOUNT(Purchase_Fact[CustomerID]), FILTER(Purchase_Fact,not(Purchase_Fact[CustomerID] IN buy_car) && Purchase_Fact[Product] in {"Scooter", "Bike"}))
 
If you just have 3 products (Bike, Scooter, Car) then you also can omit the last part in the measure (&& Purchase_Fact[Product] in {"Scooter", "Bike"}))
 
If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.
  • aiton_grant's avatar
    aiton_grant
    New Member
    8 months ago

    This was helpful, and worked with a very small dataset, but led to lack of resource timeouts when I tried it for the whole dataset.