Forum Discussion
Reshape data
Hi Anonymous,
You can take a look at below sample:
1. Source table.
RecordsTime list
2. Calculated column formula to calculate the count.
Count = COUNTAX(FILTER(ALL('SampleFile'),[StartTime]<=[Hour]&&[EndTime]>=[Hour]),[User])+0Resut table
Regards,
Xiaoxin Sheng
Resolver IIHi Anonymous,
I plugged your demo time data in along with dates to look something like this:
After loading the data I added two calculated columns to my datetime table: HourEnd and OnlineUsers. HourEnd is just HourStart + 59 minutes to give us a range of time. You could also probably do +60 minutes so long as you took off the "or = to" modifier on the OnlineUsers calculated column.
HourEnd: Take the start time and add 59 minutes
HourEnd = Table2[HourStart] + 59/(60*24)
OnlineUsers: count the rows in Table1 that fall within the hour
OnlineUsers = CALCULATE( COUNTROWS(Table1), FILTER(Table1,Table2[HourStart] >= Table1[Start Time] && Table2[HourEnd] <= Table1[End Time]) )
End result is something like this:
If you build a relationship between your tables on Start Date then you could also use slicers for any additional data on your fact table. Is this in line with what you're looking for?
Microsoft EmployeePossibly an easier solution is to add a calc column to determine the start of the hour for each record. Then just create a relationship between this calc column and HourStart, and when you put HourStart as an axis and the calc column as values set to count, you will see your count by hour.