Hello Power BI experts,
I am hoping for your assistance with preparing data for use in the custom Chord Diagram in Power BI. For those that are familiar, the diagram typically includes a 'From' column, a 'To' column, and a Gross Amount.
In my particular case, I am wanting to track a user's movement around my website, and have data that looks like the below table:
| User Name | Sitepage Visit | Time |
| Joey7 | HomePage | 8:00pm |
| Joey7 | ProductsPage | 8:03pm |
| Greg9 | HomePage | 7:44pm |
| Greg9 | ShoppingCartPage | 7:50pm |
| Greg9 | ProductsPage | 7:45pm |
| Joey7 | ShoppingCartPage | 8:04pm |
| Joey7 | ComplaintsPage | 8:10pm |
| Anna889 | HomePage | 6:15pm |
| Anna889 | ProductsPage | 7:00pm |
| Anna889 | ComplaintsPage | 7:22pm |
| Gallen32 | HomePage | 8:00pm |
What I would like to do is create a new table within Power BI, that converts the visits into From, To and sum of each for use in the Chord diagram. Eg. How many users visited the ComplaintsPage after they visited the ShoppingCartPage (determined by which Time came first.
If possible, I'd like for this new table to be calculated using a measure so that the chord diagram dynamically updates according to slicers/filters etc. I don't need information on users that visited only one page, or those that went to the same page twice.
In reality my users are in their thousands, as are the types of pages and overall number of rows are millions.
How would I go about processing the data so they are in the correct format for a Chord Diagram?
Any help you could provide would be very much appreciated.
