cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## DAX converting product categories into columns by customer

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.

2 ACCEPTED SOLUTIONS
Super User

you can select the date column and pivot column in PQ

then rename the column name as what you want

Proud to be a Super User!

New Member

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?

3 REPLIES 3
Community Support

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.

Super User

you can select the date column and pivot column in PQ

then rename the column name as what you want

Proud to be a Super User!

New Member

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors