Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PBOBOP
Frequent Visitor

Identifying consecutive rows with criteria

Hi everyone, 

 

I would like to retrieve the latest 2 dates when an item passes a test twice in a row, i.e. there should not be any failed tests between the 2 passes. Please see my sample data.

Test dates have been sorted in descending order according to item number. (I have given the item numbers different colours to help to differentiate them): 

Item numberTest Date Results 
24869422/01/2024PassThis row is not considered because the results for test in the next row is "Fail"
24869415/01/2024Fail 
2486948/01/2024Fail 
24869418/12/2023Fail 
24869411/12/2023Fail 
2486944/12/2023Pass 
24869427/11/2023Pass 
24869420/11/2023Fail 
24869223/01/2024Fail 
24869217/01/2024Pass 
2486929/01/2024Pass 
24869219/12/2023Pass 
24869212/12/2023Fail 
24869010/08/2023Fail 
2486903/08/2023Fail 
24869027/07/2023Pass 
24869021/07/2023Pass 
24869012/07/2023Fail 
2486907/07/2023Fail 
24869030/06/2023Fail 
24868626/03/2024Fail 
24868621/03/2024Fail 
24868618/03/2024Pass 
24868614/03/2024Pass 
24868611/03/2024Fail 
2486866/03/2024Fail 
2486842/11/2023PassThis row is not considered because the results for test in the next row is "Fail"
24868430/10/2023Fail 
24868425/10/2023PassThis row is not considered because the results for test in the next row is "Fail"
24868418/10/2023Fail 
24868411/10/2023Pass 
2486844/10/2023Pass 
24868427/09/2023Pass 
24868420/09/2023Pass 

 

 

My desired outcome: 

Item numberLatest Pass DateSecond Latest Pass Date
2486944/12/202327/11/2023
2486929/01/202419/12/2024
24869027/07/202321/07/2023
24868618/03/202411/03/2024

248684

11/10/20234/10/2023

 

Is there any measure that can do this?

 

Thank you

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

No need for measures.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdO9DoUgDAXgd2E2oS3Izwvc+e7GwdHkbr5/cikOYoAzmi8th1q2zYhPIXuzGBFLbIVEP77HdZl9aZjXhj/H+XtzgsrJsii7MTNk32gfTKJlBkwNv3uLspsGV+Y4HYpyhsp5GryyTG9NymQpzdlBLUOhOD66MkMusR7um0eoruQOQ05Bjw6W3HDgNzPkskgPv4Lf7DHj5vNkdZFma1a13Lr8sOGta/HacF+t7wNU6/sA1R6qrkIGTB3vfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item number" = _t, #"Test Date" = _t, Results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item number", Int64.Type}, {"Test Date", type date}, {"Results", type text}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item number", "Results"}, {{"Rows", each _, type table},{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Results] = "Pass" and [Count] > 1),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Latest Pass", each [Rows]{0}[Test Date ],type date),
    Custom1 = Table.AddColumn(#"Added Custom", "Second Latest Pass", each [Rows]{1}[Test Date ],type date),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Item number", "Latest Pass", "Second Latest Pass"})
in
    #"Removed Other Columns"

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 Source step with your own source.

 

Obligatory credit: @ImkeF 

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1712219006147.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
v-xuxinyi-msft
Community Support
Community Support

Hi @PBOBOP 

 

Your solution is great, @Ashish_Mathur and @lbendlin. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

 

Here is my testing.

 

1. First add indexed columns grouped together in Power Query.

 

Table.Group(Source, {"Item number"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}})

 

 

2. Expand the table

vxuxinyimsft_0-1712280962274.png

 

vxuxinyimsft_1-1712280991693.png

 

I also changed the column names.

vxuxinyimsft_2-1712281013948.png

 

Here's the complete code in advanced editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdM9CoQwEAXgu6QWkpnE/Fxg6+3FwnJhO+8PRuMsuLyZTvKR8ObFLIvjVHNLbnLkmT0HPr/f2767dXoozaKv7fP912oge7o4qkwmJ1EQq+8sNgd8No+RI849lAouZGgzsM/UcCxhxrFCX6melNBDo45FreNWstQKVXzRMfuIE9fcV3reDGsWJUupwp5Fk6nmyUoo+XFgU0ODOu/NPJu7lffwY3xLwvg9XNq0+xcNT10P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item number" = _t, #"Test Date" = _t, Results = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Item number"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}}),
    #"Expanded index" = Table.ExpandTableColumn(#"Grouped Rows", "index", {"Test Date", "Results", "Index.1"}, {"index.Test Date", "index.Results", "index.Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded index",{{"index.Test Date", "Test Date"}, {"index.Results", "Results"}, {"index.Index.1", "Index.1"}})
in
    #"Renamed Columns"

 

 

Close & Apply

vxuxinyimsft_7-1712282060151.png

 

3. Create two measures as follow

 

rankDate = MAX([Test Date])
rank = IF(MAX([PassResult]) = 1, RANKX(ALLEXCEPT('Table', 'Table'[Item number]), [rankDate], , ASC, Dense), BLANK())

 

 

4. Create a calculated column as follows

 

PassResult = 
VAR _pIndex = [Index.1] - 1
VAR _nIndex = [Index.1] + 1
VAR _lr = CALCULATE(MAX([Results]), FILTER(ALLEXCEPT('Table', 'Table'[Item number]), [Index.1] = _pIndex))
VAR _rl = CALCULATE(MAX([Results]), FILTER(ALLEXCEPT('Table', 'Table'[Item number]), [Index.1] = _nIndex))
VAR _cl = [Results]
VAR _result = IF( _lr = "Pass" && _cl = "Pass" || _cl = "Pass"&& _rl = "Pass", 1, 0)
RETURN
_result

 

 

5. Create two measures as follow

 

Latest Pass Date = 
VAR _maxRank = MAXX(ALLEXCEPT('Table', 'Table'[Item number]), [rank])
VAR _maxDate = CALCULATE(MAX([Test Date]), FILTER('Table', [rank] = _maxRank))
RETURN
_maxDate

 

 

 

Second Latest Pass Date = 
VAR _maxRank = MAXX(ALLEXCEPT('Table', 'Table'[Item number]), [rank])
VAR _maxDate = CALCULATE(MAX([Test Date]), FILTER('Table', [rank] = _maxRank - 1))
RETURN
_maxDate

 

 

Result:

vxuxinyimsft_4-1712281503017.png

 

I have noticed some discrepancies between your expected results and those labeled in the data sheet. Please feel free to ask me if I have misunderstood.

vxuxinyimsft_6-1712281910258.png

 

Best Regards,
Yulia Xu

 

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

Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1712219006147.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

No need for measures.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdO9DoUgDAXgd2E2oS3Izwvc+e7GwdHkbr5/cikOYoAzmi8th1q2zYhPIXuzGBFLbIVEP77HdZl9aZjXhj/H+XtzgsrJsii7MTNk32gfTKJlBkwNv3uLspsGV+Y4HYpyhsp5GryyTG9NymQpzdlBLUOhOD66MkMusR7um0eoruQOQ05Bjw6W3HDgNzPkskgPv4Lf7DHj5vNkdZFma1a13Lr8sOGta/HacF+t7wNU6/sA1R6qrkIGTB3vfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item number" = _t, #"Test Date" = _t, Results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item number", Int64.Type}, {"Test Date", type date}, {"Results", type text}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item number", "Results"}, {{"Rows", each _, type table},{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Results] = "Pass" and [Count] > 1),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Latest Pass", each [Rows]{0}[Test Date ],type date),
    Custom1 = Table.AddColumn(#"Added Custom", "Second Latest Pass", each [Rows]{1}[Test Date ],type date),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Item number", "Latest Pass", "Second Latest Pass"})
in
    #"Removed Other Columns"

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 Source step with your own source.

 

Obligatory credit: @ImkeF 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.