Negating the IN Operator

[aiton_grant]

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

[Praful_Potphode · Accepted solution]

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 · Accepted solution]

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]

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.

[FBergamaschi]

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

[Praful_Potphode · Accepted solution]

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

[aiton_grant]

This was the best solution! Thank you!

[Ashish_Mathur]

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.