Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unique total for specific days & 7,14,21 days later...

PBI File

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).

 

Relevant Tables: 

Fact Table[ContactID,Date] * to 1 Dimension Contact: [ContactID]

Event Table with no relations. [Event Name, Event Date] 

Date Table

 

Expected Result:

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.

Capture.PNG

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

 

TY 🙂
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.

6 REPLIES 6
lbendlin
Super User
Super User

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.

Anonymous
Not applicable

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?

Anonymous
Not applicable

@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.

Anonymous
Not applicable

@lbendlin Sorry to keep bothering, but have you managed to find anything that might help?

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.