How to filter records depending upon condition on different row values

[Anonymous]

Hello All

I have a table which store device id and its daily status. I want to filter first date where device was in worst condition for five consecutive dates. Please find below sample data.

I want investigate only whose device ID which are in worst condition in five consecutive dates. How to achieve this in power query or DEX.Please consider that I do have billions of records in table

Let me know if I am not clear. regards Vivek

MAC	DATE	STATUS
ABCEERDDF	2018-12-01	GOOD
ABCEERDDF	2018-12-02	GOOD
ABCEERDDF	2018-12-03	GOOD
ABCEERDDF	2018-12-04	GOOD
ABCEERDDF	2018-12-05	WORST
ABCEERDDF	2018-12-06	GOOD
ABCEERDDF	2018-12-07	WORST
ABCEERDDF	2018-12-08	WORST
ABCEERDDF	2018-12-09	WORST
ABCEERDDF	2018-12-10	WORST
ABCEERDDF	2018-12-11	WORST
ABCEERDDF	2018-12-12	GOOD
ABCEERDDF	2018-12-13	WORST
ABCEERDDF	2018-12-14	WORST
ABCEERDDF	2018-12-15	WORST
YDSHHSKJL	2018-12-01	WORST
YDSHHSKJL	2018-12-02	GOOD
YDSHHSKJL	2018-12-03	WORST
YDSHHSKJL	2018-12-04	WORST
YDSHHSKJL	2018-12-05	WORST
YDSHHSKJL	2018-12-06	WORST
YDSHHSKJL	2018-12-07	WORST
YDSHHSKJL	2018-12-08	GOOD
YDSHHSKJL	2018-12-09	WORST
YDSHHSKJL	2018-12-10	WORST
YDSHHSKJL	2018-12-11	WORST
YDSHHSKJL	2018-12-12	WORST
YDSHHSKJL	2018-12-13	WORST
YDSHHSKJL	2018-12-14	GOOD
YDSHHSKJL	2018-12-15	GOOD
ABBCA1234	2018-12-01	GOOD
ABBCA1234	2018-12-02	GOOD
ABBCA1234	2018-12-03	GOOD
ABBCA1234	2018-12-04	GOOD
ABBCA1234	2018-12-05	GOOD
ABBCA1234	2018-12-06	GOOD
ABBCA1234	2018-12-07	GOOD
ABBCA1234	2018-12-08	GOOD
ABBCA1234	2018-12-09	GOOD
ABBCA1234	2018-12-10	GOOD
ABBCA1234	2018-12-11	GOOD
ABBCA1234	2018-12-12	GOOD
ABBCA1234	2018-12-13	GOOD
ABBCA1234	2018-12-14	GOOD
ABBCA1234	2018-12-15	GOOD
ZDHWLHWL	2018-12-01	GOOD
ZDHWLHWL	2018-12-02	GOOD
ZDHWLHWL	2018-12-03	GOOD
ZDHWLHWL	2018-12-04	GOOD
ZDHWLHWL	2018-12-05	GOOD
ZDHWLHWL	2018-12-06	GOOD
ZDHWLHWL	2018-12-07	GOOD
ZDHWLHWL	2018-12-08	GOOD
ZDHWLHWL	2018-12-09	GOOD
ZDHWLHWL	2018-12-10	GOOD
ZDHWLHWL	2018-12-11	GOOD
ZDHWLHWL	2018-12-12	GOOD
ZDHWLHWL	2018-12-13	WORST
ZDHWLHWL	2018-12-14	GOOD
ZDHWLHWL	2018-12-15	GOOD

[Greg_Deckler]

Not sure how it will scale, but you could add an Index and then filter out only the "WORST" in Power Query then add this column:

Column = COUNTROWS(FILTER(ALL('Table12'),[DATE]>=EARLIER([DATE]) && [DATE]<=EARLIER([DATE])+4 && [MAC]=EARLIER([MAC])))

Then create a table visual with MAC and filter it to Column = 5.

See attached, Table12 Page 7.

[RushikeshWagham]

Hi,

I want to show value on the basis of filter using calculated measure.

[Anonymous]

Thanks Greg, it is very helpful.

There are lot of other operation I am doing at power query level.

Is it possible to put this calculation at power query level?

Thanks for your help 

Gratitude 

[Greg_Deckler]

Oh, I'm sure there is a way but I'm not that best person to help you with that, but I know someone who would be, @ImkeF

[ImkeF · Accepted solution]

If your data is sorted already (like the sample data you've provided), this method should provide a result fairly fast:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdTdCoJAFATgd/E6yF1/sst0LalAyEAqev/XyMs5NqcJvBE+luPMcV+v7NB2fX9L6ZhtsjxsQ9zGPDTLy2kcU/berESUopCilKICMY+36f5NannITh/SaLKXJOSaBE1ksqHQh5Sa8GwfaRqG6XK+rtfAIXRaI/i0hvBpDflj2loTvgqGNPKD+CYgcTbBEJ2t2QSH6HAD/cuMqPjGtd0hxKL8cR+AcLYWhHMfgHDuAxB6Uuc6ALGTgq6AEXslzAZwITP1bgIQMlPevhE002ca5uvyuOUjoHMioGMioFMikEPS3hHQ2hHQ1hHQ0gHwzhGoJHnjCPj/jkJFSfp+fwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MAC = _t, DATE = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MAC", type text}, {"DATE", type date}, {"STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"MAC", "STATUS"}, {{"Count", each Table.RowCount(_), type number}, {"All", each List.First(_[DATE]), type table}}, GroupKind.Local),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([STATUS] = "WORST")),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [Count] >= 5)
in
    #"Filtered Rows"

see this article for more details, if interested: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

[Anonymous]

@ImkeF Thank you, it worked 🙂

@Greg_Deckler Thank you 🙂 

[Greg_Deckler]

@ImkeF is always the answer when it comes to Power Query!