Forum Discussion
How to filter records depending upon condition on different row values
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-power-query/
Community ChampionNot 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.
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
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-power-query/