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

How to count customers who buy only A or A+B?

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!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Customer:

a1.png

 

Product:

a2.png

 

Purchase:

a3.png

 

Slicer(a calculated table):

Slicer = DISTINCT('Product'[Product Name])

 

Relationship:

a4.png

 

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:

a5.pnga6.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Customer:

a1.png

 

Product:

a2.png

 

Purchase:

a3.png

 

Slicer(a calculated table):

Slicer = DISTINCT('Product'[Product Name])

 

Relationship:

a4.png

 

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:

a5.pnga6.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@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])

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.