Count moving data

[Frost]

I have a dataset that looks like this:

datetime	Number1	Number2	Number3	Number4	Number5	Number6
09:12:56	46
09:18:37	0
09:20:47	18	385
11:00:03	17	18	46	114	401
11:00:14	18	114	0	0	0
11:15:41	0	0	0	0	0	0
11:15:42	1	17	18	46	113	114
11:15:47	17	18	113	114	0
11:15:49	18	114	0	0
11:15:50	0	0

The dataset captures different states in a set of parameters to reflect what values are active at a given time, but at the same time moves and sorts the data in numerical order, until the parameter is cleared as '0'. As an example, at 09:20:47 value 18 and 385 is captured in "Number1" and "Number2" and stays active until 11:00:03. At this time, we capture 17, 46, 114 and 401 which are sorted in numerical order with 17 as the lowest in "Number1". Note that 18 is moved to "Number2" and 385 has been cleared and dissapears, since it's not active any more.

Now, I want to analyze this and do two things:

1. Count number of times each value has been active during a time frame. In example, 17 and 18 are active twice, 1 and 113 once.
2. Count the duration each value has been active. In example, 18 has been active from 09:20:47 to 11:15:41 first time and 11:15:42 to 11:15:50 the second time

Any chance of accomplishing this in Power BI?

[v-yuezhe-msft]

@Frost,

I am not clear about that how you calculate the number of activity for each value. In your first example, do you mean that from 09:12:56  to 11:15:50, 17 and 18 active twice?

Regards,
Lydia

[Frost]

18 starts to be active at 09:20:47. At 11:00:03 it moves to "Number2" column, since we get a lower number in "Column1". 18 is still active. 11:00:14 it moves back to "Number1" since 17 has been cleared and dissapeared. 11:15:41 18 is cleared with '0'. 18 ahs therefore been active once during 09:20:47 to 11:15:41.

The second time 18 gets active again is at 11:15:42 in "Number3". Since lower numbers are cleared each step, it moves to "Number1" eventually at 11:15:49 and finally cleared at 11:15:50.

So, during 09:20:47 to 11:15:50, 18 has been active twice, but have been moving around in different columns due to lower values getting active until finally cleared.

Hope this helps...

[v-yuezhe-msft]

@Frost,

I don't think that we can count the occurence for these values in Power BI Desktop, as the rules to calculate the number are dynamic and different for different values.

Regards,
Lydia