Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I`m trying to get the initial marketing channel for a customer. I have three tables which simplified are:
Customer
customerID
initialMarketingChannel (this is where I want the marketing channel to show)
Transactions
date
customerID
transactionID
GAtransactionID (there's a chance that this is 0 if no marketing data is available for this order)
MarketingData
date
GAtransactionID
MarketingChannel
All tables have many to many relationships.
My goal is to find the initial marketing channel for each customer (if available).
Solved! Go to Solution.
Hi @aukev ,
In your scenario, we can use the following DAX query to create a calculated table:
Result = ADDCOLUMNS ( VALUES ( Transactions[customerID] ), "iniID", CALCULATE ( MIN ( Transactions[GATransactionID] ), TOPN ( 1, FILTER ( Transactions, Transactions[customerID] = EARLIER ( [customerID] ) && Transactions[GATransactionID] <> 0 ), Transactions[date], ASC ) ) )
Then we can create a calculated column:
initialMarketingChannel = VAR temp = LOOKUPVALUE ( MarketingData[MarketingChannel], MarketingData[GATransactionID], Result[iniID] ) RETURN IF ( temp = BLANK (), "None", temp )
The result will like below:
Best Regards,
Teige
Hi @aukev ,
Could you please share some sample data and expected result to us for analysis?
Best Regards,
Teige
Sure, thanks!
This is a sample of the MarketingData
date | GATransactionID | MarketingChannel |
Thursday, May 2, 2019 | 1397214386 | Paid Search |
Thursday, May 2, 2019 | 1397214390 | |
Saturday, March 16, 2019 | 1397201370 | Paid Search |
Wednesday, January 16, 2019 | 1397184884 | Organic |
Wednesday, January 16, 2019 | 1397184778 | Direct |
Wednesday, January 16, 2019 | 1397184822 | Paid Search |
Wednesday, January 16, 2019 | 1397184832 | Organic |
Wednesday, January 16, 2019 | 1397184790 | Paid Search |
Wednesday, January 16, 2019 | 1397184986 | Paid Search |
Wednesday, January 16, 2019 | 1397185038 | Paid Search |
Wednesday, January 16, 2019 | 1397185050 | Social |
Tuesday, January 15, 2019 | 1397184556 | Paid Search |
Tuesday, January 15, 2019 | 1397184530 | Social |
Tuesday, January 15, 2019 | 1397184520 | Organic |
Tuesday, January 15, 2019 | 1397184768 | Direct |
Tuesday, January 15, 2019 | 1397184766 | |
Tuesday, January 15, 2019 | 1397184704 | Referral |
Transactions:
date | customerID | transactionID | GATransactionID |
01-02-2019 | 1 | 1 | 1397214386 |
01-03-2019 | 2 | 2 | 1397214390 |
01-04-2019 | 3 | 3 | 1397201370 |
01-05-2019 | 4 | 4 | 1397184884 |
01-06-2019 | 1 | 5 | 0 |
01-07-2019 | 2 | 6 | 1397184778 |
01-08-2019 | 2 | 7 | 1397184822 |
01-09-2019 | 4 | 8 | 1397184832 |
01-10-2019 | 6 | 9 | 0 |
01-11-2019 | 7 | 10 | 0 |
01-12-2019 | 8 | 11 | 1397184790 |
01-13-2019 | 2 | 12 | 1397184986 |
01-14-2019 | 9 | 13 | 1397185038 |
01-15-2019 | 1 | 14 | 1397185050 |
01-16-2019 | 7 | 15 | 0 |
01-17-2019 | 8 | 16 | 1397184530 |
01-18-2019 | 9 | 17 | 1397184520 |
01-19-2019 | 6 | 18 | 1397184768 |
01-20-2019 | 1 | 19 | 1397184766 |
01-21-2019 | 11 | 20 | 1397184704 |
01-22-2019 | 1 | 21 | 1397184756 |
Expected Result:
customerID | initialMarketingChannel |
1 | Paid Search |
2 | |
3 | Paid Search |
4 | Organic |
5 | None |
6 | Direct |
7 | None |
8 | Paid Search |
9 | Paid Search |
10 | None |
11 | Referral |
Hi @TeigeGao , I`m still struggling with this. Any idea how best to solve this? Thanks!
Hi @aukev ,
In your scenario, we can use the following DAX query to create a calculated table:
Result = ADDCOLUMNS ( VALUES ( Transactions[customerID] ), "iniID", CALCULATE ( MIN ( Transactions[GATransactionID] ), TOPN ( 1, FILTER ( Transactions, Transactions[customerID] = EARLIER ( [customerID] ) && Transactions[GATransactionID] <> 0 ), Transactions[date], ASC ) ) )
Then we can create a calculated column:
initialMarketingChannel = VAR temp = LOOKUPVALUE ( MarketingData[MarketingChannel], MarketingData[GATransactionID], Result[iniID] ) RETURN IF ( temp = BLANK (), "None", temp )
The result will like below:
Best Regards,
Teige
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |