Hi, I need to find the unique total for all of my events [Total People on Event date] and then if those specific people appeared 7 days later from the day of the event do a total of that and If an event has multiple dates, do a total for all those dates and look at the last date for the 7 days later analysis.
The goal of this task is to see how many people that initially attended the event were present weeks later and also compare them to the total for that specific day in a bar chart, line chart (or whatever).
Fact Table[ContactID,Date] * to 1 Dimension Contact: [ContactID]
Event Table with no relations. [Event Name, Event Date]
The blue bar on the left is the total for that event/campaign and orange is the total for that day. Then week 1 will be the 7 days later total and then the blue bar is how many people that attended campaign 1 our present then the orange is the total for that specific day.
From my limited understanding of PBI how I want this to work is by having a slicer with event names and when a specific event is chosen it shows the data in a chart similar to the one above. Any recommendations are welcome
Any help on this is greatly appreciated. I've somewhat achieved this result by making custom columns for each day and then a measure for totals, but this is definitely not the way to do it as I would end up with 100s of columns and it doesn't even do totals properly.
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.
Hi, I've already posted the sample data. It's the "PBI File" hyperlinked at the very top of the page. I've done my best to describe the expected result in the post under 'expected result" if you need further explaniation, let me know and I'll do my best.
Apologies for missing the link - will have a look at the file.
Edit = You have multiple events overlapping. How do you decide which event the contact attended on that day?
@lbendlin Completely missed this edit. Those overlaps occurred when I was changing the dates for the sample data but in general, include all dates even if they overlap. So If a contact attended two events on the same day include both events.
No worries. Actaully I've managed to solve half of the problem and the measure used is in this PBI file: https://drive.google.com/file/d/1tMYZYQGsoyNE-4OOekc8Sxv5XNRZ-Sh7/view?usp=sharing
I now just need to see the total unique for the 7D (7 days later) column.
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!
Click here to read more about the May 2022 updates!