Help creating a column that determines whether or not a row is displayed after counter reset

[PomJack]

Hi Power BI Community,

I've been struggling with this problem for a while and have not found a work around for it yet. I am working with the table below and I would only like to display certain rows in the visuals. I only want to display the rows after the most recent error count "reset". I haven't found a way to create the display column yet or filter the visual to ignore the rows before the error count "reset". 

Die Hits	Time Stamp	Index	Error Count	Display? - Create this column
2057	1/8/2025	547	4	y
787	1/7/2025	543	3	y
353	1/6/2025	523	2	y
351	1/6/2025	521	1	y
55963	11/16/2024	127	8	n
55870	11/14/2024	123	7	n
55848	11/14/2024	122	6	n
55846	11/14/2024	121	5	n
55841	11/14/2024	120	4	n
53690	11/10/2024	91	3	n
53442	11/7/2024	60	2	n
53094	11/6/2024	55	1	n

The table is just a snippet of the full table. It is possible for there to be multiple error count "resets" and I only want the most recent reset displayed. 

Thanks for your help!

[lbendlin]

So you want all the rows after the latest date that has an Error Count of 1 (including that row) ?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDJDcQgDAB74R0J34ZaUPpvYzkdaflY4BnLR2uJQD09CXPJBKT9qTISkt6nJS8L+ge5R56QlSe0gDQStCH+w5mYULXarMWMU5DxodGrbKE4bEE+YdT4EaTcAvVoIdgtjBk0BLwFiN2VrZ4Z4AgVY31lEVrcDzaIAyhDlYVjRdV1gvcH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Die Hits" = _t, #"Time Stamp" = _t, Index = _t, #"Error Count" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time Stamp", type date}, {"Error Count", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Time Stamp] >= List.Max(Table.SelectRows(#"Changed Type", each [Error Count]=1)[Time Stamp]))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[PomJack]

Yes, that is one way of framing the problem.

[lbendlin]

I added the sample code.

[PomJack]

The table in this question is already in table view in Power BI. The Error Count column is a new calculated column in the table view. Is there a way to do the same thing as above, but in table view instead of power query? I should have included these details in the original post.

[Anonymous · Accepted solution]

Thanks for the reply from lbendlin.

Hi @PomJack,

As lbendlin said, we can create a measure and here are the steps to do so:

1. Create a measure:

Display = 
VAR _date=CALCULATE(MAX('Table'[Time Stamp]),FILTER(ALL('Table'),'Table'[Error Count]=1))
RETURN
IF(MAX('Table'[Time Stamp])>=_date,"y","n")

2. Filter the data with the measure value of y:

3. Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[lbendlin]

The logic is the same.  Create a measure to find the newest Date that has an error count of 1, then filter to show that data and any newer dates.