Need help with DAX - Finding total number of customers with respect to purchase date of two products

[Anonymous]

Hello, I am new to DAX. Could you please help me writing following Dax measure OR column.

I have this table -1 of customers and I am looking to find total number customers who bought product A first and than B, Similiary Product B first and than A. Also want to know how many had just one product. 

Please any given advise is appreciated.

Many Thanks,

Channa

Table 1

ID	Product Type	Date of Purchase
1	A	10-Jan-21
1	A	11-Jan-21
1	B	12-Jan-21
2	A	5-Jan-21
2	B	6-Jan-21
3	B	7-Jan-21
3	A	8-Jan-21
4	B	9-Feb-21
4	A	10-Feb-21
5	A	15-Jan-21
6	B	15-Jan-21

Table 2: I need table in this format

Count	Customer ID Count
Customer with First Product A than B
Customer First Product B than A
Customer Only Product A
Customer Only Product B

[lbendlin]

You need to review your requirement. It is ambiguous.

[Anonymous]

@lbendlin  Thankyou so much for the reply..

Only calculation matter if customer is switch to product A to  B  or B to A in given period.  If customer has both products than I have to mark them customer with both products.My end goal is finding converstion rate from Product A to B and B to A.. Please advise ... 

[v-jingzhang]

Hi @Anonymous 

The "given period" is not very clear and specific. How do we distinguish the difference between customers(First A Then B / First B then A) and customers with both products? Do customers with both products always bought both products on the same date or on different dates? If on different dates, then how to tell they are not in group First A then B or First B then A?

To count customers that only bought Product A or Product B, you can use below measures.

Customer Only Product A = 
VAR __table = SUMMARIZE('Table','Table'[ID ],"ProductType",CONCATENATEX(DISTINCT('Table'[Product Type]),[Product Type],","),"ProductTypeCount",DISTINCTCOUNT('Table'[Product Type]))
RETURN
COUNTROWS(FILTER(__table,[ProductTypeCount]=1 && [ProductType]="A"))

Customer Only Product B = 
VAR __table = SUMMARIZE('Table','Table'[ID ],"ProductType",CONCATENATEX(DISTINCT('Table'[Product Type]),[Product Type],","),"ProductTypeCount",DISTINCTCOUNT('Table'[Product Type]))
RETURN
COUNTROWS(FILTER(__table,[ProductTypeCount]=1 && [ProductType]="B"))

And to show measures on multiple rows, you need to use a matrix visual and turn on Show on rows under Values. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[lbendlin]

You didn't specify if you want measures or calculated columns.  I assume the purchase dates are immutable so I created two calculated columns

First A = CALCULATE(min('Table'[Date of Purchase]),ALLEXCEPT('Table','Table'[ID]),'Table'[Product Type]="A")
First B = CALCULATE(min('Table'[Date of Purchase]),ALLEXCEPT('Table','Table'[ID]),'Table'[Product Type]="B")

This will then lead to 

and

which makes it easy to calculate your final result.  But it also opens up a new question - what should happen when the customer purchased A and B on the same date?  What if they purchase A then B then A etc ?