Skip to main content
cancel
Showing results for 
Search instead 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

Reply
hoskr
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 IDProduct CategoryDate
1A01/01/24
2B01/01/24
3C01/01/24
1B02/01/24
2C02/01/24
3A02/01/24
2A03/01/24

 

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

Customer IDCategory 1Category 2Category 3
1AB 
2BCA
3CA 

 

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
ryan_mayu
Super User
Super User

@hoskr 

you can select the date column and pivot column in PQ

11.png12.PNG

 

then rename the column name as what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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 IDProduct CategoryDateRank
1A01/01/241
2B01/01/241
3C01/01/241
1B02/01/242
2C02/01/242
3A02/01/242
2A03/01/243

 

Customer IDRank 1Rank 2Rank 3
1AB 
2BCA
3CA 

 

Hope that makes sense?

 

View solution in original post

3 REPLIES 3
v-nuoc-msft
Community Support
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"

vnuocmsft_0-1715911279019.png

 

Create a table. Create a virtual table.

 

 

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

 

 

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

 

vnuocmsft_1-1715911357873.png

 

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"
    )

 

 

vnuocmsft_2-1715911539460.png

 

Select the matrix visual object.

 

vnuocmsft_4-1715911692074.png

 

Here is the result.

vnuocmsft_5-1715911711780.png

 

 

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
Super User
Super User

@hoskr 

you can select the date column and pivot column in PQ

11.png12.PNG

 

then rename the column name as what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 IDProduct CategoryDateRank
1A01/01/241
2B01/01/241
3C01/01/241
1B02/01/242
2C02/01/242
3A02/01/242
2A03/01/243

 

Customer IDRank 1Rank 2Rank 3
1AB 
2BCA
3CA 

 

Hope that makes sense?

 

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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