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 Community,
Can you please help me writing this query in Power BI DAX (Column).
I have table, where I want to find the customer converstions from first product to their last product. i.e converstion from Product A to B and B to A. Also, There is no customers in data that have purchased two products on same day. Just to avoid ambguity, I will exclude the customers that purchased on same day.
Note - I want to exclude the products that are purchased on First day of year.
I would really appreciate if you could help me with it.
Many Thanks, Chana
Solved! Go to Solution.
Try this code for a calculated column:
Product Conversion =
VAR _Table =
CALCULATETABLE (
'Table',
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
)
)
VAR DistVal =
CALCULATE (
COUNT ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
)
)
RETURN
IF (
DistVal = 1,
'Table'[Product] & "2" & 'Table'[Product],
CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )
)
I've added a new row for ID 1 in 2021 so you can see the progression in case you have more than 2 operations in a year
As an alternative you could just list the progression based on the number of Products by year using:
Product Conversion =
VAR _Table =
CALCULATETABLE (
'Table',
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
)
)
RETURN
CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )
To get:
Proud to be a Super User!
Paul on Linkedin.
please post sample data (not an image) and a depiction of the expected outcome. Thanks!
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown, Please see below: I need result in column like Product Conversion TEST.
Note: I want excludes data on First day of year.
Thank you in advance,
Asad
Date | ID | Product | TEST COL | Product Conversion TEST |
1/1/2020 0:00 | 6 | A | A Only | A2A |
1/1/2020 0:00 | 7 | B | B Only | B2B |
2/12/2020 0:00 | 1 | A | A Only | A2A |
2/13/2020 0:00 | 6 | A | A Only | A2A |
3/12/2020 0:00 | 1 | A | A Only | A2A |
5/13/2020 0:00 | 5 | B | B Only | B2B |
12/5/2020 0:00 | 5 | B | B Only | B2B |
1/1/2021 0:00 | 1 | A | A & B | A2B |
1/1/2021 0:00 | 3 | A | A Only | A2A |
1/2/2021 0:00 | 2 | A | A Only | A2A |
1/3/2021 0:00 | 2 | A | A Only | A2A |
1/8/2021 0:00 | 1 | B | A & B | A2B |
4/14/2021 0:00 | 4 | B | B Only | B2B |
thanks for that. Do you want a measure or a calculated column?
Proud to be a Super User!
Paul on Linkedin.
Calculate column please. Thanks!
Sure! Can you please explain the logic behind your Test Col?
and please clarify the criteria. For example for ID = 1, you have AAAB but you your progression states:
AA
AA
AA
AB
Does that mean the criteria is current date vs. previous?
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
Test col reperesents if customers has only Product A or B or both based on calendar year.
To know the conversion from Product A to B or B to A , I am looking to First and Last date of product purchases.
Thank you,
Chana
So is the calculation by year or progressive irrespective of the year?
Proud to be a Super User!
Paul on Linkedin.
Calculation by Year. Thank you
Try this code for a calculated column:
Product Conversion =
VAR _Table =
CALCULATETABLE (
'Table',
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
)
)
VAR DistVal =
CALCULATE (
COUNT ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
)
)
RETURN
IF (
DistVal = 1,
'Table'[Product] & "2" & 'Table'[Product],
CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )
)
I've added a new row for ID 1 in 2021 so you can see the progression in case you have more than 2 operations in a year
As an alternative you could just list the progression based on the number of Products by year using:
Product Conversion =
VAR _Table =
CALCULATETABLE (
'Table',
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
)
)
RETURN
CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )
To get:
Proud to be a Super User!
Paul on Linkedin.
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.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |