none
Power BI Filter table to get last version RRS feed

  • Question

  • Hi,

    As you can see i've got a table with an id for each test and a version.

    i would like to filter the table and only get the lines with the last version for each test.

    I want to do it with DAX if its possible or PowerQuery

    For example in the screen i would like to get the last version for the test "327" which is version 3 so i want all the lines off the test 327 with version 3.

    Thanks


    • Edited by LitSin Monday, May 13, 2019 8:05 AM
    Monday, May 13, 2019 7:58 AM

Answers

  • In that case, try this

    #"Latest per test" = Table.Group(#"Source", {"TestCase"},{{"version", each List.Max([version]), Int64.Type}, 
    {"id", each 
        [id]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type} } ),

    And in a similar way to "id", you can decide to keep any number of column by adding more of "id" code like this:

    #"Latest per test" = Table.Group(#"Source", {"TestCase"},{{"version", each List.Max([version]), Int64.Type}, 
    
    {"id", each 
        [id]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type}, 
    
    {"id2", each 
        [id2]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type},
    
    {"id3", each 
        [id3]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type},
    
    } ),

    • Marked as answer by LitSin Tuesday, May 14, 2019 8:34 AM
    Monday, May 13, 2019 1:48 PM

All replies

  •     #"Filtered Rows" = Table.SelectRows(Source, each [version] = List.Max(Source[version]) )
    Replace "Source" and "[version]" with whatever table and column name you have
    Monday, May 13, 2019 8:52 AM
  •     #"Filtered Rows" = Table.SelectRows(Source, each [version] = List.Max(Source[version]) )
    Replace "Source" and "[version]" with whatever table and column name you have

    @S.Risemann

    i add this 

        #"Filtered Rows" = Table.SelectRows(testlink_tcversions, each [version] =List.Max(testlink_tcversions[version]) )
    in
        #"Filtered Rows"

    But i don't get what i want, its only select the row of version 3 and me i want for each "TestCase" the last version  of each Test

    Monday, May 13, 2019 12:18 PM
  • In that case, try this

    #"Latest per test" = Table.Group(#"Source", {"TestCase"},{{"version", each List.Max([version]), Int64.Type}, 
    {"id", each 
        [id]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type} } ),

    And in a similar way to "id", you can decide to keep any number of column by adding more of "id" code like this:

    #"Latest per test" = Table.Group(#"Source", {"TestCase"},{{"version", each List.Max([version]), Int64.Type}, 
    
    {"id", each 
        [id]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type}, 
    
    {"id2", each 
        [id2]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type},
    
    {"id3", each 
        [id3]{List.PositionOf(
            [version],List.Max([version]) 
        ) }
    , Int64.Type},
    
    } ),

    • Marked as answer by LitSin Tuesday, May 14, 2019 8:34 AM
    Monday, May 13, 2019 1:48 PM