Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |