topic Re: Need help with writing DAX in DAX Commands and Tips

Need help with writing DAX

Anonymous — Wed, 25 Aug 2021 14:46:08 GMT

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

Re: Need help with writing DAX

PaulDBrown — Wed, 25 Aug 2021 17:37:16 GMT

please post sample data (not an image) and a depiction of the expected outcome. Thanks!

Re: Need help with writing DAX

Anonymous — Wed, 25 Aug 2021 18:03:34 GMT

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

Re: Need help with writing DAX

PaulDBrown — Wed, 25 Aug 2021 18:14:58 GMT

thanks for that. Do you want a measure or a calculated column?

Re: Need help with writing DAX

Anonymous — Wed, 25 Aug 2021 18:24:11 GMT

Calculate column please. Thanks!

Re: Need help with writing DAX

PaulDBrown — Wed, 25 Aug 2021 18:40:54 GMT

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:

Does that mean the criteria is current date vs. previous?

Re: Need help with writing DAX

Anonymous — Wed, 25 Aug 2021 18:54:06 GMT

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

Re: Need help with writing DAX

PaulDBrown — Wed, 25 Aug 2021 18:58:12 GMT

So is the calculation by year or progressive irrespective of the year?

Re: Need help with writing DAX

Anonymous — Wed, 25 Aug 2021 19:00:51 GMT

Calculation by Year. Thank you

Re: Need help with writing DAX

PaulDBrown — Thu, 26 Aug 2021 09:38:38 GMT

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: