Skip to main content
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

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,



Table 1

ID Product TypeDate of Purchase

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
Super User
Super User

You need to review your requirement. It is ambiguous.

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

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 






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

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