Unexperienced in DAX

[Anonymous]

Hi,

Data

I have a dataset in which different units (unit_id's) get an update every 10 minutes. There are multiple columns under which a datetime column, a idle_time column and unit_id column. 

Necessary

I need just the rows in which the next value of idle_time is 0 (per unit).

So for example from unit 1&2:

unit_id	datetime	idle_time
1	1-5-2021 10:10	53
1	1-5-2021 10:20	63
2	1-5-2021 10:23	12
1	1-5-2021 10:30	0
2	1-5-2021 10:33	22
2	1-5-2021 10:43	0

I now just need the bold rows (the peeks per unit). All updates and al units are in the same table. How can i filter the rows out for every unit, where the next idle_time value is 0? So it's based on the date-time increasing.

Thanks!

[CNENFRNL]

Solution in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcuxDcAgDETRVZBrkHx3IUVWQey/Blh0xO1/+mMYrBpab3SiwD/4Dl02698Y9h7jbYrA9FN8nm6KjUzt0dnmAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [unit_id = _t, datetime = _t, idle_time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"unit_id", Int64.Type}, {"datetime", type datetime}, {"idle_time", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"unit_id"}, {{"ar", each let index = Table.AddIndexColumn(_, "index", 0), pos = List.Transform(List.PositionOf([idle_time], 0, Occurrence.All), each _-1) in Table.SelectRows(index, each List.Contains(pos, [index]))}}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"datetime", "idle_time"}, {"datetime", "idle_time"})
in
    #"Expanded ar"

[AlB]

Hi @Anonymous 

This can probebly best be done in PQ

If you want it in DAX, xreate a new calculated table:

NewTable = 
FILTER (
    Table1,
    VAR zeroTime_ =
        CALCULATE (
            MAX ( Table1[datetime] ),
            Table1[idle_time] = 0,
            ALLEXCEPT ( Table1, Table1[unit_id] )
        )
    VAR previous_ =
        CALCULATE (
            MAX ( Table1[datetime] ),
            Table1[datetime] < zeroTime_,
            ALLEXCEPT ( Table1, Table1[unit_id] )
        )
    RETURN
        previous_ = Table1[datetime]
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[amitchandak]

@Anonymous , create a new flag column like

new column =
var _max = minx(filter(Table, [unit_id] =earlier([unit_id]) && [datetime]> earlier([datetime])),[datetime])
return
if(minx(filter(Table, [unit_id] =earlier([unit_id]) && [datetime]= _max),[idle_time]) =0, 1, blank())

and use