Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

 

 

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 TypeDate of Purchase
1A10-Jan-21
1A11-Jan-21
1B12-Jan-21
2A5-Jan-21
2B6-Jan-21
3B7-Jan-21
3A8-Jan-21
4B9-Feb-21
4A10-Feb-21
5A15-Jan-21
6B15-Jan-21

Table 2: I need table in this format

CountCustomer ID Count
Customer with First Product A than B 
Customer First Product B than A 
Customer Only Product A 
Customer Only Product B
4 REPLIES 4
lbendlin
Super User
Super User

You need to review your requirement. It is ambiguous.

Anonymous
Not applicable

@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 ... 

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. 

080603.jpg

 

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

lbendlin
Super User
Super User

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 

 

lbendlin_0-1627165524399.png

and

lbendlin_1-1627165704752.png

 

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 ?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Kudoed Authors