The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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!
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?
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.
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!
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
122 | |
89 | |
75 | |
55 | |
45 |
User | Count |
---|---|
134 | |
120 | |
76 | |
65 | |
64 |