New measure conditioned partially to other columns

[xors]

Hi, 

I'd like to create a couple of columns conditioned to the sum of the non-zero parts of another column, not sure if that's even possible. Let me clarify it with an example:

 Area    Time    Stop-time     Sum    Signal

3.2        10:46         0                0          0

3.5        10:47         0                0          0

2.6        10:48         0                0          0

6.7        10:49         0                0          0

0           10:50      10:50            0          0

0           10:51      10:51         00:01      0

0           10:52      10:52         00:02      1

0           10:53      10:53         00:03      0

0           10:54      10:54         00:04      0

1.2        10:55         0                0          0

1.5        10:56         0                0          0

5.6        10:57         0                0          0

2.7        10:58         0                0          0

0           10:59      10:59            0          0

0           11:00      11:00        00:01       0

0           11:01      11:01        00:02       1

0           11:02      11:02        00:03       0

My data would be similar to the first 3 columns of table above, and my goal is to create the latter 2. On the Sum column the idea is to sum the time of the Stop-time column by blocks (limited by the 0's above and below), and on the Signal one the idea is to triger a boolean if the sum is over a certain value (2 minutes in the example above).

As mentioned before I'm not sure that's even possible, have been looking around the forum without success, so any hint would be highly appreciated!

[v-danhe-msft]

Hi @xors,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[v-danhe-msft]

Hi @xors,

Based on my test, you could refer to below formula:

Create below columns:

Column = var a=Table1[Time]-1/1440
 var b= CALCULATE(SUM(Table1[Stop-time]),FILTER('Table1','Table1'[Time]=a)) 
return IF('Table1'[Stop-time]<>BLANK()&&b=BLANK(),0,IF('Table1'[Stop-time]=BLANK()&&b<>BLANK(),BLANK(),[Stop-time]-b))

Column 2 = IF([Column]=BLANK(),BLANK(),CALCULATE(SUM(Table1[Column]),FILTER('Table1','Table1'[Time]<=EARLIER(Table1[Time])&&'Table1'[Column]=EARLIER(Table1[Column]))))

Then calculate the [sum] column and [Signal] column:

sum = IF([Column 2]<=
MAXX('Table1',IF([Column 2]=IF([Column]=BLANK(),BLANK(),CALCULATE(SUM(Table1[Column]),FILTER('Table1','Table1'[Time]>=EARLIER(Table1[Time])&&'Table1'[Column]=EARLIER(Table1[Column])))),[Column 2])),
[Column 2],
[Column 2]-MAXX('Table1',IF([Column 2]=IF([Column]=BLANK(),BLANK(),CALCULATE(SUM(Table1[Column]),FILTER('Table1','Table1'[Time]>=EARLIER(Table1[Time])&&'Table1'[Column]=EARLIER(Table1[Column])))),[Column 2])))+0

Signal = IF(MINUTE('Table1'[sum])=2,1,0)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[xors]

Thank you so much @v-danhe-msft for such an extense reply!

There's just one little detail to fix I'm struggling with; In your first column you set variable "a" as the result of "[Time] - 1/1440" (minus 1 minute), but in reality my data doesn't have a time step set (each row has a 1-2s step, but is completely variable), so I guess I'd have to change that "1/1440" to point to the previous row right?

Column 2 also complains about a lack of memory in the system (we're talking about thousands/millions of rows), but I guess I can fix that creating a Measure instead of a column as I've seen in other posts, please confirm if I'm right.

Thanks again for your help!

[v-danhe-msft]

Hi @xors,

For you first problem, your guess is right to change that "1/1440" to point to the previous row. And for your second problem, change the calculated column to measure could do improve the performence but I am afraid it is difficult to change the calculated column to measure, you could try to fix it.

Regards,

Daniel He