Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Dear Community Forum,
I would like to run a formula that assigns a last touch attribtion logic of marketing activity to my orders.
I have:
What i would like is a calcualted column either in DAX or maybe done in PowerQuery that:
Below is an illistration of what I am trying to achieve.
I've been trying to solve this problem for 3 days. The closest I got was using a neat range lookup i found from Pragmatic Studios:
Range Lookup in Power BI in the Power Query Editor - YouTube
This approach however just left my PowerQuery editor spinning and I think there is a more elegant (simple?) solution.
Thank you,
Stephen
MARKETING
Client | Date of Marketing Activity | Marketing Activity |
123 | 31/12/2022 | 50% off letter |
123 | 03/01/2023 | New Year inspiration letter |
124 | 31/12/2022 | 50% off letter |
125 | 06/01/2023 | New Year inspiration letter |
125 | 07/01/2023 | New Year inspiration letter |
126 | 01/01/2016 | Welcome to 2016 |
ORDERS
Transaction Date | Client | Revenue |
01/01/2023 | 123 | 5 |
02/01/2023 | 124 | 5 |
03/01/2023 | 124 | 5 |
04/01/2023 | 123 | 5 |
05/01/2023 | 125 | 5 |
05/01/2023 | 126 | 5 |
Solved! Go to Solution.
Try this calculated column in the Orders table. There is no relationship between these tables in this example.
Marketing Activity =
VAR vTransactionDate = Orders[Transaction Date]
VAR vClient = Orders[Client]
VAR vMarketingTable =
FILTER (
Marketing,
Marketing[Client] = vClient
&& Marketing[Date of Marketing Activity] < vTransactionDate
&& Marketing[Date of Marketing Activity] >= vTransactionDate - 90
)
VAR vMaxDateMarketingActivity =
MAXX ( vMarketingTable, Marketing[Date of Marketing Activity] )
VAR vResult =
MAXX (
FILTER (
vMarketingTable,
Marketing[Date of Marketing Activity] = vMaxDateMarketingActivity
),
Marketing[Marketing Activity]
)
RETURN
IF ( ISBLANK ( vResult ), "NA", vResult )
Proud to be a Super User!
That is genius.
What an elegant solution.
Applied it to my model and it works beautifully at scale.
I owe you a beer.
Excellent, glad to hear that works!
Proud to be a Super User!
Try this calculated column in the Orders table. There is no relationship between these tables in this example.
Marketing Activity =
VAR vTransactionDate = Orders[Transaction Date]
VAR vClient = Orders[Client]
VAR vMarketingTable =
FILTER (
Marketing,
Marketing[Client] = vClient
&& Marketing[Date of Marketing Activity] < vTransactionDate
&& Marketing[Date of Marketing Activity] >= vTransactionDate - 90
)
VAR vMaxDateMarketingActivity =
MAXX ( vMarketingTable, Marketing[Date of Marketing Activity] )
VAR vResult =
MAXX (
FILTER (
vMarketingTable,
Marketing[Date of Marketing Activity] = vMaxDateMarketingActivity
),
Marketing[Marketing Activity]
)
RETURN
IF ( ISBLANK ( vResult ), "NA", vResult )
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |