Wanting to count which hours are used in a day, not repetitions in the data

Question

So I have a 30+million row chart in which I have the data segmented like this (this is a mock up of the format):

:

Where I have a variable I'm counting ('Number'), a group, a full date/time string, the date only, the hour (out of 24 hours), the total variable count ('Number') per day, and the hours active per day. What I'm trying to do is determine the number of hours a GroupID is active per day. In the hours per day column, I have the following formula:

So if we see from the table, Im getting a result of 5 hours active, not the 3 unique hour counts of the hour that I want (for 10am, 11am and 12pm). It is taking into account repetitions, and what I'd like to find is the unique number of instances of that hour, per day, per GroupID.

Any help would be appreciated, thanks!

vince_faller · Accepted Answer

You need to check that it's the first one.

With 30M+ rows this may take a bit to run, but it should get you what you want.

Col Sum( Col Cumulative Sum( 1, :Date Only, :GroupID, :Hour ) == 1, :Date Only, :GroupID )

It basically looks that the cumulative sum of the group, date, hour combo == 1.  If it isn't, returns a 0.  Then you just col sum() by the date/group