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.
Hi there,
I have a problem with counting specific group of customers. I have dataset as below:
Customer
Customer ID | Customer Name |
1 | Mary |
2 | Jack |
3 | Eric |
4 | Nancy |
5 | Jenny |
Product
Product ID | Customer Name |
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
Purchase
Customer ID | Product ID |
1 | 1 |
1 | 2 |
1 | 3 |
2 | 1 |
3 | 2 |
3 | 4 |
4 | 1 |
4 | 2 |
4 | 3 |
4 | 4 |
5 | 1 |
5 | 2 |
Now I need to count how many customers buy only A or A+B. For the example above, the answer should be 2 (Jack & Jenny).
I tried many ways to do that but failed. Please help me with this, thanks!
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.
Customer:
Product:
Purchase:
Slicer(a calculated table):
Slicer = DISTINCT('Product'[Product Name])
Relationship:
You may create two measures as below.
Customers =
var t1 = DISTINCT(Slicer[Product Name])
var t =
SUMMARIZE(
Customer,
Customer[Customer Name],
"Flag",
var t2 =
CALCULATETABLE(
DISTINCT('Product'[Product Name]),
FILTER(
ALL(Purchase),
RELATED(Customer[Customer Name])=EARLIER(Customer[Customer Name])
)
)
return
IF(
COUNTROWS(t1)=COUNTROWS(t2)&&
COUNTROWS(t1)=COUNTROWS(INTERSECT(t1,t2)),
1,0
)
)
return
CONCATENATEX(
FILTER(
t,
[Flag]=1
),
[Customer Name],
","
)
Num =
var t1 = DISTINCT(Slicer[Product Name])
var t =
SUMMARIZE(
Customer,
Customer[Customer Name],
"Flag",
var t2 =
CALCULATETABLE(
DISTINCT('Product'[Product Name]),
FILTER(
ALL(Purchase),
RELATED(Customer[Customer Name])=EARLIER(Customer[Customer Name])
)
)
return
IF(
COUNTROWS(t1)=COUNTROWS(t2)&&
COUNTROWS(t1)=COUNTROWS(INTERSECT(t1,t2)),
1,0
)
)
return
COUNTROWS(
FILTER(
t,
[Flag]=1
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.
Customer:
Product:
Purchase:
Slicer(a calculated table):
Slicer = DISTINCT('Product'[Product Name])
Relationship:
You may create two measures as below.
Customers =
var t1 = DISTINCT(Slicer[Product Name])
var t =
SUMMARIZE(
Customer,
Customer[Customer Name],
"Flag",
var t2 =
CALCULATETABLE(
DISTINCT('Product'[Product Name]),
FILTER(
ALL(Purchase),
RELATED(Customer[Customer Name])=EARLIER(Customer[Customer Name])
)
)
return
IF(
COUNTROWS(t1)=COUNTROWS(t2)&&
COUNTROWS(t1)=COUNTROWS(INTERSECT(t1,t2)),
1,0
)
)
return
CONCATENATEX(
FILTER(
t,
[Flag]=1
),
[Customer Name],
","
)
Num =
var t1 = DISTINCT(Slicer[Product Name])
var t =
SUMMARIZE(
Customer,
Customer[Customer Name],
"Flag",
var t2 =
CALCULATETABLE(
DISTINCT('Product'[Product Name]),
FILTER(
ALL(Purchase),
RELATED(Customer[Customer Name])=EARLIER(Customer[Customer Name])
)
)
return
IF(
COUNTROWS(t1)=COUNTROWS(t2)&&
COUNTROWS(t1)=COUNTROWS(INTERSECT(t1,t2)),
1,0
)
)
return
COUNTROWS(
FILTER(
t,
[Flag]=1
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Create a measure like and try
Countx(filter(summarize(purchase, Customer[Customer Name], "_1", countrows(filter(Product , [Product name] = "A"))+0 , "_2", countrows(filter(Product , [Product name] = "B"))+0 ),
[_1]>0 || ([_1] >0 && [_2] >0)),[Customer Name])
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |