I will try to explain my challenge.
I have a table "All Customers" with the following columns (among others):
I created another Table "Total Products by Customer". All I did was group by "NAME" and the new column "NUMBER OF PRODUCTS" is just a count, so now I know how many products each customer have:
NUMBER OF PRODUCTS
These two tables are related by NAME on a Both Cross Filter direction
I want to calculate a measure to know how many customers just have the combination of Product1(P1) and Product2(P2). I have tried many things this is the latest:
Number of Customers with 2 Major Products = CALCULATE(DISTINCTCOUNT('All Customers'[NAME]),AND(AND(AND('All Customers'[PRODUCT]<>"P3",'All Customers'[PRODUCT]<>"P4"),'All Customers'[PRODUCT]<>"P5"),'All Customers'[PRODUCT]<>"P6"))
I also added a slicer to filter just those with two products. The result I am getting includes or counts customers that have P1 and P2, and any combination of either P1 or P2 with other product.
I hope I was clear on my explanation.
Any help will be highly appreciated