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.
Hi All,
I'm new to Power BI. For my 1st project I am trying to determine if an order came from a marketing campaign.
I have a list of campaigns, the customers involved and the start and end date they ran from.
What I am trying to figure out is for every order is the customer in a campaign and have ordered between the start and end date the campaign ran for.
Hope this makes sense.
I have set up 3 spread sheets:
Campaign Audience
Campaign ID
Customer ID
Campaigns
Campaign ID
Campaign Name
Start Date
End Date
Orders
Customer ID
Order Date
Product
Thanks in advance.
Michael
Solved! Go to Solution.
You can either create a measure as below
1.You can try to create a measure as below. Then you may get the respected result. Measure = MAXX ( FILTER ( campaigns, MAX ( orders[order date] ) >= campaigns[start date] && MAX ( orders[order date] ) <= campaigns[end date] ), campaigns[campaign id ] )
Or create an auxiliary table to extend the campaign row(start date and end date) to rows of each day. And then create a one to one relationship between the created table and order table.
Table = FILTER ( CROSSJOIN ( 'calendar date', campaigns ), 'calendar date'[Date] >= campaigns[start date] && 'calendar date'[Date] <= campaigns[end date] )
You can either create a measure as below
1.You can try to create a measure as below. Then you may get the respected result. Measure = MAXX ( FILTER ( campaigns, MAX ( orders[order date] ) >= campaigns[start date] && MAX ( orders[order date] ) <= campaigns[end date] ), campaigns[campaign id ] )
Or create an auxiliary table to extend the campaign row(start date and end date) to rows of each day. And then create a one to one relationship between the created table and order table.
Table = FILTER ( CROSSJOIN ( 'calendar date', campaigns ), 'calendar date'[Date] >= campaigns[start date] && 'calendar date'[Date] <= campaigns[end date] )
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 |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |