DAX converting product categories into columns by customer

[hoskr]

I want to track a customer's 'journey' through transactions - I am guessing I need to calculate a new table via DAX?

Say I have a transaction table below:

Customer ID	Product Category	Date
1	A	01/01/24
2	B	01/01/24
3	C	01/01/24
1	B	02/01/24
2	C	02/01/24
3	A	02/01/24
2	A	03/01/24

I want to make a new table like so with the categories in sequence (by date) for each customer:

Customer ID	Category 1	Category 2	Category 3
1	A	B
2	B	C	A
3	C	A

Hopefully that makes sense? I've found articles turning multiple categories into a concat string in a column but I really want the above. No idea where to start! Thank you.

[ryan_mayu · Accepted solution]

@hoskr 

you can select the date column and pivot column in PQ

then rename the column name as what you want

[v-nuoc-msft]

Hi @hoskr 

@ryan_mayu Thank you very much for your prompt reply. Allow me to offer a different approach here.

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a table. Create a virtual table.

virtual table = VALUES('Table'[Date])

And for both tables, create a relationship based on dates.

Create new columns in the virtual table.

rank = RANKX('virtual table', 'virtual table'[Date],,ASC,Dense)

Category = 
    SWITCH(
        TRUE(),
        [rank] = 1, "Caregory 1",
        [rank] = 2, "Caregory 2",
        [rank] = 3, "Caregory 3"
    )

Select the matrix visual object.

Here is the result.

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@hoskr 

you can select the date column and pivot column in PQ

then rename the column name as what you want

[hoskr · Accepted solution]

Hi everyone,

These were good angles to come at this problem with.

Solution

Instead I chose to add a calculated column that ranked each customer transaction by order date (filtering for customer). This then gave me a column to use in a matrix - I went customer IDs by rows and my new calculated column as columns and count of transactions as values.

Transaction table

Customer ID	Product Category	Date	Rank
1	A	01/01/24	1
2	B	01/01/24	1
3	C	01/01/24	1
1	B	02/01/24	2
2	C	02/01/24	2
3	A	02/01/24	2
2	A	03/01/24	3

Customer ID	Rank 1	Rank 2	Rank 3
1	A	B
2	B	C	A
3	C	A

Hope that makes sense?