Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!