Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
I hope I was clear on my explanation.
Any help will be highly appreciated
Solved! Go to Solution.
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.
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.
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |