Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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):
-Order Number
-Order Time
-User Name
-BGR_ALL.Product Name
-BGR_ALL.Category
-Phone Number
-Platform
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!!
how are you planning to identify the users if you don't have anything but your eyeballs to match them?
Hello,
sorry for being unclear: I wanted to match them with their phone numbers.
Assuming that they kept their numbers constant across platforms.
Thanks
Jannnn04
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)
Thank you!
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?
Thanks !
Here the data
Order Number | Order Time | User Name | BGR_ALL.Product Name | BGR_ALL.Category | Value | Platform | Phone Number |
11111 | 01.01.2021 | Oscaaarrr | iphone | Phones | 155 | 1925526 | |
22222 | 02.01.2021 | Luna | lenovo | Laptops | 111 | Amazon | 1164070 |
33333 | 03.01.2021 | Emil | apple | Tablets | 69 | 1618350 | |
44444 | 04.01.2021 | Lisa-Maria | android | Phones | 53 | 1702213 | |
55555 | 05.01.2021 | Lisa | macbook | Laptops | 90 | Ebay | 1571808 |
66666 | 06.01.2021 | Jan | samsung | Tablets | 102 | Alibaba | 1701882 |
77777 | 07.01.2021 | markus | iphone | Phones | 48 | Saturn | 1213889 |
88888 | 08.01.2021 | Rosa | lenovo | Laptops | 16 | 1892353 | |
99999 | 09.01.2021 | YO_Oscar | apple | Tablets | 168 | Amazon | 1925526 |
111110 | 02.03.2021 | Jose | android | Phones | 161 | 1164070 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | Saturn | 1618350 |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | Amazon | 1618350 |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
122221 | 03.03.2021 | Emil | macbook | Laptops | 112 | 1618350 | |
133332 | 04.03.2021 | Lisa-Maria | samsung | Tablets | 192 | Ebay | 1702213 |
144443 | 05.03.2021 | Lisa | iphone | Phones | 110 | Alibaba | 1571808 |
155554 | 06.03.2021 | Feb | lenovo | Laptops | 175 | Saturn | 1492420 |
166665 | 07.03.2021 | Markus | apple | Tablets | 125 | 1213889 | |
177776 | 08.03.2021 | Rosa | android | Phones | 175 | Amazon | 1892353 |
188887 | 09.03.2021 | Oscar32 | macbook | Laptops | 100 | 1925526 |
I still did not manage to solve that issue and would be super grateful if someone can assist! 🙂
Thanks, community!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
62 | |
44 | |
17 | |
12 |