Power BI measure/calculation count when x > y difficulties. Aggregate per hour

Question

I'm trying to calculate a how often a specific condition appears in a table. But I'm not getting it to work as intended due to how the data appears in the table, at least for the method I'm trying.

This is the base measure that it all depends on. This one works as intended:

Sold Units = 
CALCULATE(
    SUM('Table1'[Unit]),
        table2[A] = "filter1",
        table3[B] = "filter2"
    )

Now I want to calculate in Table1 how many hours have a "Sold units" above 500

My issue is that I'm not sure how to make sure it sums per hour, instead of the total

A simple calculation with this doesn't seem to work.

 COUNT(Table1[Hour]), 
    FILTER(Table1, _x>=500))

My initial thought was to use the group by function and transform the data. But since the columns I filter by are from other tables and the connection to the tables are by a unique ID this won't help.

Not sure how to proceed here

Each row has unique ID that is used to filter out the correct things that we want to sum.

One hour can have multiple rows

Update:

Data looks something like this:

UniqueID	date	hour	ID2	units sold
1001	2024-03-12	13	Bob	5
1002	2024-03-12	13	Bob	3
1003	2024-03-12	13	Bob	2
1004	2024-03-12	13	Lisa	4
1005	2024-03-12	14	Bob	3
1006	2024-03-12	14	Lisa	4
1007	2024-03-12	14	Adam	6
1008	2024-03-12	15	Bob	2
1009	2024-03-12	15	Bob	3

Hour 13 Bob sold 5+3+2, which is >=5, so that should be 1 hour counted.

Hour 14 Bob sold 3, should not be counted

Hour 15 Bob Sold 2+3 = 5, should be counted.

This means that I want my measure to return 2 for Bob (when ID2 listed in a table etc)

I need the Unique ID to be able to filter out specific products

Answer 1

You can use SUMMARIZE to group by hour and ID2, CALCULATE to sum units sold, and then filter these groups based on the threshold :

Hours Above Threshold = 
VAR Threshold = 5
VAR SoldUnitsByHourAndID2 = 
    SUMMARIZE(
        Table1,
        Table1[date],
        Table1[hour],
        Table1[ID2],
        "TotalUnits", CALCULATE(SUM(Table1[units sold]))
    )
VAR FilteredHours = 
    FILTER(
        SoldUnitsByHourAndID2,
        [TotalUnits] >= Threshold
    )
RETURN
    COUNTROWS(FilteredHours)