Calculate loyal customers

[yacine_Big]

Hi
i need help, i will try to explain my problem i hope someone can understand
i have a table with many costumers and many products
situation number one:
a costumer who buys many products in same category, let's say he buys milk from a brand and another brand too so we can say he is not loyal to a brand he buys whatever milk he find on market
situation number two:
customers who buy only one brand of milk so he is loyal to that brand he only buys that brand and if he doesn't find it on the market, he doesn't buy anything else

i want to calculate the costumer who bought only one brand and no other and calculate too the ones who buys many brand
PS: milk is just an exemple we have many products and many categories.
excuse my english 
Best Regards.

ID Name   -  category    -    products
1 jhon          milk                milk brand 1
1 jhon          milk                milk brand 2
1 mary         milk                milk brand 1
1 mary         milk                milk brand 1
1 jhon          milk                milk brand 2
1 jhon          milk                milk brand 2
1 mary         milk                milk brand 1
1 mary         milk                milk brand 1
1 jhon          milk                 milk brand 3

[Ahmedx]

also to calculate the COUNT  you can do this:

LoyalCount = SUMX ( FILTER ( VALUES('Table'[Name]), [number of products] = 1 ), 1 )
DisLoyalCount = SUMX ( FILTER ( VALUES('Table'[Name]), [number of products] > 1 ), 1 )

[Ahmedx]

I corrected my file, please download it again

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://dropmefiles.com/6QKvt

[ryan_mayu · Accepted solution]

@yacine_Big 

maybe you can create a new column

loyalty = if(CALCULATE(DISTINCTCOUNT('Table'[products]),ALLEXCEPT('Table','Table'[ID Name],'Table'[category]))=1,"Y","N")

[yacine_Big]

thank you it works like a charm, with some modifications because my table is more complicated than the one gave you but it really works thank you again .

[ryan_mayu]

you are welcome