Count value with conditions in a period of time

[Anonymous]

Hello everyone,

I have a table with 3 columns : 

- column 1 : Value

- Column 2 : employee.

- column 3 : Date and time 

I would like to count how many time the values in column 1 are above X during 10 minutes for each employee. 

For example, if value in column 1 are above 25 during 10 minutes (column 3) for an employee ( column 2), the formula will count 1. 

Many thanks for your help, 

[Anonymous]

create new column whih calculated column3 in minutes.

New measure= calculate(count(Table[Employee]),Filter(Table,Table[value]>X && table[New column]>10))

Thanks & regards,

Pravin Wattamwar

[Anonymous]

Hello, 

Thank you @Anonymous  for your quick response. 

Unfortunately, It doesn't work because I didn't explain well the time issue I have. 

Please see below a sample of the data : 

value	Employee	Date
25	Peter	30/12/2019 20:10
26	Peter	30/12/2019 20:11
27	Peter	30/12/2019 20:12
28	Peter	30/12/2019 20:13
28	Peter	30/12/2019 20:14
29	Peter	30/12/2019 20:15
30	Peter	30/12/2019 20:16
31	Peter	30/12/2019 20:17
32	Peter	30/12/2019 20:18
33	Peter	30/12/2019 20:19
34	Peter	30/12/2019 20:20
32	Peter	30/12/2019 20:21
33	Peter	30/12/2019 20:22
34	Peter	30/12/2019 20:23

I  would like a formula which counts how many times values have been above 25 for at least 10 minutes. In this case, between 30/12/2019 20:10 and 30/12/2019 20:23 (13 minutes) values are above 25 for Peter and the formula should count 1. 

Do you have any idea ? 

Many thanks for your help, 

Ilyas, 

[V-lianl-msft]

Hi @Anonymous ,

According to my understanding, your problem type is circular iteration, and DAX can't solve this kind of problem temporarily. You can use Python script or R script to solve this problem.

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

[Anonymous]

Try to create index column and get difference in minutes by using datediff.
Then apply the above which i have given.

Get reference from below post.
https://community.powerbi.com/t5/Desktop/get-value-from-Previous-row/td-p/441336


Thanks & regards,
Pravin