Getting not matched result of two tables column

[AwaisAnwar]

Hi, I am trying to calculate the number of customer who buys milk only. I have gone through different posts and this solution i got. But when i apply this error is "error single value was expected where as table of multiple values was suppliued"

Customerbuyingmilkthisweek =

 VAR Customerlistmilk = CALCULATETABLE(VALUES('Distinctcustomer'[LocationReference]),'Weekly Data'[Weeknum] = MAX('Weekly Data'[Weeknum]),'Weekly Data'[Product Catagory1_3] = "Milk")

 VAR Customerlistother = CALCULATETABLE(VALUES('Distinctcustomer'[LocationReference]),'Weekly Data'[Weeknum] = MAX('Weekly Data'[Weeknum]),'Weekly Data'[Product Catagory1_3] in {"Bread","Groceries","Creams"})

        return

    COUNTROWS(FILTER(Customerlistmilk,Customerlistmilk  Not in (Customerlistother)))

[jdbuchanan71]

I think the problem with your original was in the NOT IN section.  You used () instead of {} and the NOT was a bit off.

Customerbuyingmilkthisweek =
VAR Customerlistmilk =
    CALCULATETABLE (
        VALUES ( 'Distinctcustomer'[LocationReference] ),
        'Weekly Data'[Weeknum] = MAX ( 'Weekly Data'[Weeknum] ),
        'Weekly Data'[Product Catagory1_3] = "Milk"
    )
VAR Customerlistother =
    CALCULATETABLE (
        VALUES ( 'Distinctcustomer'[LocationReference] ),
        'Weekly Data'[Weeknum] = MAX ( 'Weekly Data'[Weeknum] ),
        'Weekly Data'[Product Catagory1_3] IN { "Bread", "Groceries", "Creams" }
    )
RETURN
    COUNTROWS (
        FILTER ( Customerlistmilk, NOT ( Customerlistmilk ) IN { Customerlistother } )
    )

[AwaisAnwar]

Still unable to understand why countrown is resturning multiple values when this return scalar. However except works really good! Thanks

[jdbuchanan71 · Accepted solution]

Try it using EXCEPT

Customerbuyingmilkthisweek =
VAR Customerlistmilk =
    CALCULATETABLE (
        VALUES ( 'Distinctcustomer'[LocationReference] ),
        'Weekly Data'[Weeknum] = MAX ( 'Weekly Data'[Weeknum] ),
        'Weekly Data'[Product Catagory1_3] = "Milk"
    )
VAR Customerlistother =
    CALCULATETABLE (
        VALUES ( 'Distinctcustomer'[LocationReference] ),
        'Weekly Data'[Weeknum] = MAX ( 'Weekly Data'[Weeknum] ),
        'Weekly Data'[Product Catagory1_3] IN { "Bread", "Groceries", "Creams" }
    )
VAR CustomerMilkOnly =
    EXCEPT ( Customerlistmilk, Customerlistother )
RETURN
    COUNTROWS ( CustomerMilkOnly )