topic Re: Matching Users across Platforms in DAX Commands and Tips

Matching Users across Platforms

Anonymous — Wed, 29 Sep 2021 02:26:36 GMT

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!!

Re: Matching Users across Platforms

lbendlin — Thu, 30 Sep 2021 01:52:46 GMT

how are you planning to identify the users if you don't have anything but your eyeballs to match them?

Re: Matching Users across Platforms

Anonymous — Thu, 30 Sep 2021 02:09:18 GMT

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

Re: Matching Users across Platforms

lbendlin — Thu, 30 Sep 2021 14:50:36 GMT

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

Re: Matching Users across Platforms

Anonymous — Fri, 01 Oct 2021 03:48:17 GMT

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	Facebook	1925526
22222	02.01.2021	Luna	lenovo	Laptops	111	Amazon	1164070
33333	03.01.2021	Emil	apple	Tablets	69	Google	1618350
44444	04.01.2021	Lisa-Maria	android	Phones	53	Instagram	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	Facebook	1892353
99999	09.01.2021	YO_Oscar	apple	Tablets	168	Amazon	1925526
111110	02.03.2021	Jose	android	Phones	161	Google	1164070
122221	03.03.2021	Emil	macbook	Laptops	112	Instagram	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Saturn	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Facebook	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Amazon	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Google	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Instagram	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Instagram	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Instagram	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Instagram	1618350
122221	03.03.2021	Emil	macbook	Laptops	112	Instagram	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	Facebook	1213889
177776	08.03.2021	Rosa	android	Phones	175	Amazon	1892353
188887	09.03.2021	Oscar32	macbook	Laptops	100	Google	1925526

Re: Matching Users across Platforms

Anonymous — Tue, 05 Oct 2021 01:06:48 GMT

I still did not manage to solve that issue and would be super grateful if someone can assist! 🙂

Thanks, community!

Re: Matching Users across Platforms

Anonymous — Tue, 12 Oct 2021 01:36:45 GMT

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!

Re: Matching Users across Platforms

lbendlin — Wed, 13 Oct 2021 19:59:32 GMT

Do you want to record only true platform switches, or do you also want to see the events where they purchased from the same platform multiple times in a row (like Emil and Instagram)?

See attached for a sample implementation

Re: Matching Users across Platforms

Anonymous — Thu, 14 Oct 2021 01:39:37 GMT

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!

Re: Matching Users across Platforms

lbendlin — Mon, 18 Oct 2021 12:01:09 GMT

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)

Re: Matching Users across Platforms

Anonymous — Tue, 19 Oct 2021 07:29:11 GMT

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 !