Return time of first non-zero value on column

[ausphil]

Hello all,

I have a table with columns Time, Index, Shift and Value like so:

Time	Index	Shift	Value
7:00	1	Day	0
8:00	2	Day	32
9:00	3	Day	34
10:00	4	Day	68
11:00	5	Day	72
12:00	6	Day	0
13:00	7	Day	35
14:00	8	Day	40
15:00	9	Day	50
16:00	10	Day	60
17:00	11	Day	10
18:00	12	Day	0
19:00	13	Night	0
20:00	14	Night	0
21:00	15	Night	0
22:00	16	Night	70
23:00	17	Night	60
0:00	18	Night	50
1:00	19	Night	70
2:00	20	Night	0
3:00	21	Night	80
4:00	22	Night	0
5:00	23	Night	0
6:00	24	Night	0

I need to write some measures to return the red highlighted values like so:

1. The time of the first non-zero value where shift = day (8:00)

2. The time of the first continuous zero value through to the end of the shift where shift = day (18:00)

3. The time of the first non-zero value where shift = night (22:00)

4. The time of the first continuous zero value through to the end of the shift where shift = night (4:00)

Appreciate the help!

Thank you.

[Zubair_Muhammad]

@ausphil 

For first and third requirment, you could try this MEASURE's pattern

Measure 1 =
MINX (
    TOPN ( 1, FILTER ( Table1, [Shift] = "Day" && [Value] <> 0 ), [Index], ASC ),
    [Time]
)

[ausphil]

---

@Zubair_Muhammad wrote:

@ausphil 

For first and third requirment, you could try this MEASURE's pattern

Measure 1 =
MINX (
    TOPN ( 1, FILTER ( Table1, [Shift] = "Day" && [Value] <> 0 ), [Index], ASC ),
    [Time]
)

---

Thank you @Zubair_Muhammad this one works perfectly!

[Zubair_Muhammad]

@ausphil 

Just change day to night or vice versa for similar 2nd and 3rd MEASURE

[Zubair_Muhammad · Accepted solution]

Hi @ausphil 

Try this for your 4th requirement i,e. The time of the first continuous zero value through to the end of the shift where shift = night (4:00).

You can use same logic for 2nd requirement

Measure4 =
VAR LastNonZeroNight =
    MAXX (
        TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] <> 0 ), [Index], DESC ),
        [Index]
    )
RETURN
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [Shift] = "Night"
                    && [Value] = 0
                    && [Index] > LastNonZeroNight
            ),
            [Index], ASC
        ),
        [Time]
    )

[ausphil]

---

@Zubair_Muhammad wrote:

Hi @ausphil 

Try this for your 4th requirement i,e. The time of the first continuous zero value through to the end of the shift where shift = night (4:00).

You can use same logic for 2nd requirement

Measure4 =
VAR LastNonZeroNight =
    MAXX (
        TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] <> 0 ), [Index], DESC ),
        [Index]
    )
RETURN
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [Shift] = "Night"
                    && [Value] = 0
                    && [Index] > LastNonZeroNight
            ),
            [Index], ASC
        ),
        [Time]
    )

---

Thank you @Zubair_Muhammad this seems to work for the 4th requirement, but for the 1st requirement it is returning 7:00 AM. Any thoughts?