Window of three minutes if multiple columns match up

[A_K]

Hello,

Hope you are well. I have a column called device id, where the same id can appears multiple times in the column. I have another column called product id, where the same id can also appear multiple times and I have a name column with 5 distinct names that can also appear multiple times within the column. Finally, I have a date and time column where there can be multiple of the same date and time rows. The thing that I want to do is filter to only include the rows where the product id is the same, the name is the same, and the date and time column is within three minutes of each other. Then I would like to count the distinct device ids within the filtered down rows. I have created a calculated column which does this task, but the runtime is very long since I have lots of data. I will include my dax for that calculated column below. Is there a way to optimize this or do a different methods so that it will not take so long to process? Thanks

Dax Code: 

DistinctDeviceCountFilter = 

var currentProduct = table[productId]

var currentName = table[name]

var currentTime = table[time]
return
Calculate(DISTINCTCOUNT(table[deviceId]), All(table), table[productId] = currentProduct && table[name] = currentName && ABS(table[time] -currentTime)<= TIME(0,3,0))

[Anonymous]

Hi  @A_K ,

You can try the following dax:

DistinctDeviceCountFilter =
VAR currentTime = 'Table'[time]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[deviceId] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[productId], 'Table'[name] ),
            ABS ( 'Table'[time] - currentTime )
                <= TIME ( 0, 3, 0 )
        )
    )

Refer to :

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[A_K]

Thanks so much for your time in answering my question, however I am still having to wait like an hour for the calculated column to finish running. I am dealing with 2 million lines of data. Could there be another approcah this?