cancel
Showing results 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

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.

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
4 REPLIES 4
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 ...

Community Support

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.

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")``````

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 ?

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Kudoed Authors