Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count customers with just two products. Filter specific combinations

I will try to explain my challenge.

 

I have a table "All Customers" with the following columns (among others):

NAME

PRODUCT


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:

NAME (Unique)

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. 2MP.jpg

 

 

 

 

 

 

 

 

 

 

 

I hope I was clear on my explanation.

 

Any help will be highly appreciated

1 ACCEPTED SOLUTION
MalS
Resolver III
Resolver III

I am sure there is a smart way to do this with DAX expressions, but there is also a trick you can use with calculated columns...

 

Edit Queries, then in "All Customers" go to Add Column > Custom Column and enter a formula like this:

 

 

 

Product Check = if [Product] = "P1" then "1" else if [Product] = "P2" then "10" else "100"

Now group by name, and create a new column by summing the Product Check column. You should see a list of names, and every name on that list where Product Check = 11 owns P1 and P2 and nothing else. 

View solution in original post

2 REPLIES 2
MalS
Resolver III
Resolver III

I am sure there is a smart way to do this with DAX expressions, but there is also a trick you can use with calculated columns...

 

Edit Queries, then in "All Customers" go to Add Column > Custom Column and enter a formula like this:

 

 

 

Product Check = if [Product] = "P1" then "1" else if [Product] = "P2" then "10" else "100"

Now group by name, and create a new column by summing the Product Check column. You should see a list of names, and every name on that list where Product Check = 11 owns P1 and P2 and nothing else. 

Anonymous
Not applicable

Thank you for your response it worked. I just want to notice it is very important to remember it is case sensitive and the conditional if needs parenthesis. 

Product Check = if ([PRODUCT] = "P1") then "1" else if ([PRODUCT] = "P2") then "10" else "100" 

 

Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.