Desktop

Highlighted
Frequent Visitor
Posts: 2
Registered: ‎01-16-2019

New measure conditioned partially to other columns

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!

Community Support Team
Posts: 2,039
Registered: ‎06-24-2018

Re: New measure conditioned partially to other columns

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 2
Registered: ‎01-16-2019

Re: New measure conditioned partially to other columns

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.

Community Support Team
Posts: 2,039
Registered: ‎06-24-2018

Re: New measure conditioned partially to other columns

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Posts: 2,039
Registered: ‎06-24-2018

Hi @xors,