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 want a count of customers that have bought a bike or a scooter, but never bought a car. If a customer had bought all three items, they should be excluded, but in the following DAX, it will not filter out a customer who bought all three.
 
Any suggestions for how to resolve this?
 
CALCULATE(DISTINCTCOUNT('Customer Detail'[Customer ID]),
NOT ('Product Detail'[Product Category] IN {"CAR"}),
'Product Info'[Product Category] IN {"BIKE", "Scooter"},
CROSSFILTER('Customer Detail'[Customer ID],'Purchase FACT'[Customer ID], Both))
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.

6 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    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
    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 months ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    8 months ago

    To simplify the DAX code, take the Customer from the Sales table and for a single value you do not need IN

     

    CALCULATE(
                 DISTINCTCOUNT(Sales[Customer ID]),
                                 'Product Detail'[Product Category] <> "CAR",
                             'Product Info'[Product Category] IN {"BIKE", "Scooter"}

    )

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI