Hi Community and PowerBi Masters,
I am currently working on an analysis for which I try to understand the consumer behavior in detail.
The corresponding data that I have is (name of table: All_Platforms):
The ones marked in bold are the ones, which seem to matter for this case.
My goal: understand which consumer has changed between platforms.
An example: Username Oscaaarrr with Phone number 1925526 has purchased something on Facebook on January first and on Amazon ( 9th February, different user name: YO_Oscar) and a month later on Google (9th March, different user name: Oscar32).
Now I want to display that visually in a sankey chart for which I need two variables: Source and Destination. This would allow me to show the flow of users between the platforms.
My question: how do I create new columns that match the phone numbers across platforms and state their platform used for the current transaction and the platform used of the previous transaction?
I hope that is clear. Let me know if you need any more informatoin to solve my issue!
Highly appreciate your help.
All the best and many thanks!!
sorry for being unclear: I wanted to match them with their phone numbers.
Assuming that they kept their numbers constant across platforms.
that's a bold assumption 🙂
Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) .
Hello @lbendlin ,
was the data that I provided in the table form of any help?
I would really appreciate help here, dont manage to find a starting point.
Thanks a lot!
Thanks a lot!
Seeing the events where thez purchased from the same platform multiple times in a row would also be nice! Thanks for asking.
I unfortunately can not open the report because I can not download the latest version of PBI currently. Would you be able to share it in another format? That would be great!
Thanks a lot!
Here's the result:
and here is the DAX :
Came from = var i = 'Table'[Index] var p = CALCULATE(max('Table'[Platform]),ALLEXCEPT('Table','Table'[Phone Number]),'Table'[Index]=i-1) return if('Table'[Platform]=p,BLANK(),p)
That looks very promising. When I fit that into my table however, it does not show my anything for the p variable. It just returns blank values.
As a measure it does not work because it does not recognize the index column.
What am I doing wrong?
Here the data
|Order Number||Order Time||User Name||BGR_ALL.Product Name||BGR_ALL.Category||Value||Platform||Phone Number|
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
At the monthly call, connect with other leaders and find out how community makes your experience even better.
Click here to read more about the May 2022 updates!