Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
You need to review your requirement. It is ambiguous.
@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.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.