Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.