Finding the first record in the latest group

[XELANAMYT]

I have a set of data which can be grouped by a Category for each Object, however I need to find the latest grouping, currently I can narrow it down to all the groups, but not the latest. I appreciate that description isn't brilliant so hopefully the following data set will make it clearer.

ObjectId	Revision	ADate	Category
ABC	1	01/01/2000	Apple
ABC	2	01/01/2001	Blackberry
ABC	3	01/01/2002	Blackberry
ABC	4	01/01/2001	Cucumber
ABC	5	01/01/2002	Blackberry
ABC	6	01/01/2000	Blackberry
ABC	7	01/01/2002	Damson
DEF	1	01/01/2001	Apple
DEF	2	01/01/2000	Apple

So in the above example, for ObjectId 'ABC' and Category 'Blackberry', I can filter down to Revision 2 by finding the minimum Revision number where the Category='Blackberry', however I want to filter down to Revision 5, which is the minimum Revision number in the last Category group instance. From there I will access another value for that row, for example ADate here.

I've tried using RANK, and I know I should probably be able to use something like GROUPBY and PARTIONBY that I have used in the past in SQL (quite a long time ago now, hence the brain fog!), but without success.

Any suggestion would be appreicated, Power Query or DAX solutions are fine, bonus cookies for both!

[v-priyankata]

Hi @XELANAMYT 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the relevant solution from users would be awesome for others who might run into the same thing.

[ronrsnfld]

Perhaps this is what you want from your posted data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMgRiA0N9IDIyMDAAchwLCnJSlWJ1YAqMkBWAVDvlJCZnJ6UWFVUiqTJGVmWES5UJulnOpcmluUBVSGpMiTLJDN3ZWFWZo5vlkphbnJ8HVuHi6obufUMU70MUGOEIn1gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, ADate = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ObjectId", type text}, {"Revision", Int64.Type}, {"ADate", type date}, {"Category", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ObjectId","Category"}, {
        {"all", each Record.SelectFields(Table.First(_),{"Revision","ADate"}), type[Revision=Int64.Type, ADate=date] }},
        GroupKind.Local,(x,y)=>(Number.From((x[Category]<>y[Category]) or (x[ObjectId]<>y[ObjectId])) )),
    #"Expanded all" = Table.ExpandRecordColumn(#"Grouped Rows", "all", {"Revision", "ADate"}, {"Revision", "ADate"}),
    
    #"Grouped Rows1" = Table.Group(#"Expanded all", {"ObjectId", "Category"}, {
        {"all", each  Record.SelectFields(Table.Last(_),{"Revision","ADate"}), type  [Revision=number, ADate=date]}}),
    #"Expanded all1" = Table.ExpandRecordColumn(#"Grouped Rows1", "all", {"Revision", "ADate"}, {"Revision", "ADate"})
in
    #"Expanded all1"

[HarishKM]

Hello @XELANAMYT ,
Can try to create a calculated column in power bi to identify latest record?
Sample dax - IsLatestRecord =
IF(
Sales[SaleDate] = CALCULATE(
MAX(Sales[revision_Date]),
ALLEXCEPT(Sales, Sales[ProductID])
),
1,
0
)

Thanks

Harish M
Please accept this as a solution if this solves your problem and give kudos as well.

[v-priyankata]

Hi @XELANAMYT 
@AmiraBedh @jgeddes Thanks for the inputs.

I hope the information provided by the users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @XELANAMYT 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[jgeddes]

If I understand your requirement, you are looking for an output like this...

You can use GroupKind.Local in the Table.Group function.

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WcnRyVtJRMgRix4KCnFSlWB2YmBEQO+UkJmcnpRYVVSJJGOOSMAFi59Lk0lygBJKwKS71ZrgkzIHYJTG3OD8PLOji6obhRoiYEUIsFgA=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, Category = _t]
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"ObjectId", type text}, 
            {"Revision", Int64.Type}, 
            {"Category", type text}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"ObjectId", "Category"}, 
        {
            {"FirstRevision", each List.Min([Revision]), type nullable number}, 
            {"FirstCategory", each Record.Field(Table.SelectRows(_, (r)=>r[Revision] = List.Min([Revision])){0}, "Category"), type text}
        }, 
        GroupKind.Local
    ),
    #"Removed Columns" = 
    Table.RemoveColumns(
        #"Grouped Rows",
        {"Category"}
    )
in
    #"Removed Columns"

If you are looking for a different output, please post what you are expecting to see from the example data you first posted.

Hope this helps.

[XELANAMYT]

Hi, I would just be looking for the single row in this example ObjectId=ABC, Revision=5, Category=Blackberry.

However assume there are many other ObjectIds which could have similar groupings for Category, and the value for Category can be filtered on too, so 'Blackberry' can't be hardcoded.

In fact, the result set you have given would be fine, just without the row ObjectId=ABC, Revision=2, Category=Blackberry

[AmiraBedh]

If you want DAX, you can create a calculated table :

LatestGroupStartRows =
VAR AddGroupStart =
    ADDCOLUMNS (
        'TableA',
        "GroupStart",
        VAR PrevCat =
            CALCULATE (
                MAX ( 'TableA'[Category] ),
                FILTER (
                    'TableA',
                    'TableA'[ObjectId] = EARLIER ( 'TableA'[ObjectId] )
                        && 'TableA'[Revision] = EARLIER ( 'TableA'[Revision] ) - 1
                )
            )
        RETURN IF ( 'TableA'[Category] = PrevCat, 0, 1 )
    )
VAR AddGroupID =
    ADDCOLUMNS (
        AddGroupStart,
        "GroupID",
        VAR ThisObject = [ObjectId]
        VAR ThisRevision = [Revision]
        RETURN
            COUNTROWS (
                FILTER (
                    AddGroupStart,
                    [ObjectId] = ThisObject
                        && [Revision] <= ThisRevision
                        && [GroupStart] = 1
                )
            )
    )
VAR LatestPerObject =
    ADDCOLUMNS (
        AddGroupID,
        "LatestGroupID",
        CALCULATE (
            MAX ( [GroupID] ),
            ALLEXCEPT ( AddGroupID, [ObjectId] )
        )
    )
RETURN
    FILTER (
        LatestPerObject,
        [GroupID] = [LatestGroupID]
            && [Revision]
                = CALCULATE (
                    MIN ( [Revision] ),
                    FILTER (
                        LatestPerObject,
                        [ObjectId] = EARLIER ( [ObjectId] )
                            && [GroupID] = EARLIER ( [GroupID] )
                    )
                )
    )

If you are going to use PQ :

let
    
    Source =
        Excel.CurrentWorkbook(){[Name="Source"]}[Content],  
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {{"ObjectId", type text}, {"Revision", Int64.Type}, {"Category", type text}}
        ),

    
    #"Sorted Rows" =
        Table.Sort(#"Changed Type", {{"ObjectId", Order.Ascending}, {"Revision", Order.Ascending}}),

    
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Idx", 0, 1, Int64.Type),

    
    #"Added PrevRecord" =
        Table.AddColumn(
            #"Added Index",
            "Prev",
            each if [Idx]=0 then null else #"Added Index"{[Idx]-1},
            type any
        ),
    #"Expanded Prev" =
        Table.ExpandRecordColumn(
            #"Added PrevRecord",
            "Prev",
            {"ObjectId", "Category"},
            {"PrevObjectId", "PrevCategory"}
        ),

    
    #"Added GroupStart" =
        Table.AddColumn(
            #"Expanded Prev",
            "GroupStart",
            each if [ObjectId]=[PrevObjectId] and [Category]=[PrevCategory] then 0 else 1,
            Int64.Type
        ),

    
    #"Added GroupID" =
        Table.AddColumn(
            #"Added GroupStart",
            "GroupID",
            each
                List.Sum(
                    Table.SelectRows(
                        #"Added GroupStart",
                        (r) => r[ObjectId]=[ObjectId] and r[Idx] <= [Idx]
                    )[GroupStart]
                ),
            Int64.Type
        ),

    
    LatestGroupID =
        Table.Group(
            #"Added GroupID",
            {"ObjectId"},
            {{"MaxGroupID", each List.Max([GroupID]), Int64.Type}}
        ),

    
    #"Kept Latest Block" =
        Table.NestedJoin(
            #"Added GroupID",
            {"ObjectId", "GroupID"},
            LatestGroupID,
            {"ObjectId", "MaxGroupID"},
            "Join",
            JoinKind.Inner
        ),
    #"Expanded Join" =
        Table.ExpandTableColumn(#"Kept Latest Block", "Join", {}),

    
    #"First Row Of Latest" =
        Table.Group(
            #"Expanded Join",
            {"ObjectId"},
            {
                {"FirstRow",
                 each Table.Min(_, "Revision"),
                 type table [ObjectId=nullable text, Revision=number, Category=text, Idx=number, PrevObjectId=nullable text, PrevCategory=nullable text, GroupStart=number, GroupID=number]
                }
            }
        ),
    #"Expanded FirstRow" =
        Table.ExpandTableColumn(
            #"First Row Of Latest",
            "FirstRow",
            {"ObjectId", "Revision", "Category"},
            {"ObjectId", "Revision", "Category"}
        ),

    
    Output =
        Table.SelectColumns(#"Expanded FirstRow", {"ObjectId", "Revision", "Category"})
in
    Output

[jgeddes]

Does this work for you?

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WcnRyVtJRMgRix4KCnFSlWB2YmBEQO+UkJmcnpRYVVSJJGOOSMAFi59Lk0lygBJKwKS71ZrgkzIHYJTG3OD8PLOji6obhRoiYEUIsFgA=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, Category = _t]
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"ObjectId", type text}, 
            {"Revision", Int64.Type}, 
            {"Category", type text}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"ObjectId", "Category"}, 
        {
            {"FirstRevision", each List.Min([Revision]), type nullable number}
        },
        GroupKind.Local
    ),
    #"Grouped Rows1" = 
    Table.Group(
        #"Grouped Rows", 
        {"ObjectId", "Category"}, 
        {
            {"Revision", each List.Max([FirstRevision]), type nullable number}
        }
    )
in
    #"Grouped Rows1"

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on MS Fabric community.

This is the dataset I used as an example :

If you want to use Power Query, I detailed the major steps in this code :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMgRix4KCnFSlWB2YmBEQO+UkJmcnpRYVVSJJGOOSMAFi59Lk0lygBJKwKS71ZrgkzIHYJTG3OD8PLOji6obhRoiYEUIsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ObjectId", type text}, {"Revision", Int64.Type}, {"Category", type text}}),

    /* Sort by ObjectId then Revision so rows are in logical order */
    #"Sorted Rows" =
        Table.Sort(#"Changed Type", {{"ObjectId", Order.Ascending}, {"Revision", Order.Ascending}}),

    /* Add an Index to compare each row with the previous one */
    #"Added Index"           = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added PrevIndex"       = Table.AddColumn(#"Added Index", "PrevIndex", each [Index]-1, Int64.Type),

    /* Pull the previous row Category & ObjectId */
    #"Merged PrevRow" =
        Table.NestedJoin(
            #"Added PrevIndex",
            {"PrevIndex"},
            #"Added Index",
            {"Index"},
            "PrevRow",
            JoinKind.LeftOuter
        ),
    #"Expanded PrevRow" =
        Table.ExpandTableColumn(
            #"Merged PrevRow",
            "PrevRow",
            {"ObjectId", "Category"},
            {"PrevObjectId", "PrevCategory"}
        ),

    /* Flag the start of a new contiguous group (1 = new group) */
    #"Add GroupStart" =
        Table.AddColumn(
            #"Expanded PrevRow",
            "GroupStart",
            each if [ObjectId] = [PrevObjectId] and [Category] = [PrevCategory] then 0 else 1,
            Int64.Type
        ),

    /* Create a running GroupID per ObjectId */
    #"Add GroupID" =
        Table.AddColumn(
            #"Add GroupStart",
            "GroupID",
            each
                List.Sum(
                    Table.SelectRows(
                        #"Add GroupStart",
                        (r) => r[ObjectId] = [ObjectId] and r[Index] <= [Index]
                    )[GroupStart]
                ),
            Int64.Type
        ),

    /*  For every ObjectId, keep only rows that belong to the latest GroupID */
    LatestGroupPerObject =
        let
            MaxGroups =
                Table.Group(
                    #"Add GroupID",
                    {"ObjectId"},
                    {{"MaxGroupID", each List.Max([GroupID]), Int64.Type}}
                )
        in
            Table.NestedJoin(
                #"Add GroupID",
                {"ObjectId", "GroupID"},
                MaxGroups,
                {"ObjectId", "MaxGroupID"},
                "J",
                JoinKind.Inner
            ),

    /* Within each latest-group keep only the first Revision */
    #"Keep First of Each Latest" =
        Table.SelectRows(
            Table.Sort(LatestGroupPerObject, {{"Revision", Order.Ascending}}),
            (row) =>
                row[Revision]
                    = List.Min(
                        Table.SelectRows(
                            LatestGroupPerObject,
                            (r) => r[ObjectId] = row[ObjectId] and r[GroupID] = row[GroupID]
                        )[Revision]
                    )
        ),
/*  Finally keep just the three original columns */
    Output =
        Table.SelectColumns(#"Keep First of Each Latest", {"ObjectId", "Revision", "Category"})
in
    Output

The output is :

If you want the solution in DAX, I created 3 calculated columns, the 1st one to flag where a new group starts,

the 2nd one is to run the GroupID per ObjectId and the 3rd one to identify the 1st row of the latest group :

GroupStart = 
VAR PrevCat =
    CALCULATE (
        MAX ( 'Table B'[Category] ),
        FILTER (
            'Table B',
            'Table B'[ObjectId] = EARLIER ( 'Table B'[ObjectId] )
                && 'Table B'[Revision] = EARLIER ( 'Table B'[Revision] ) - 1
        )
    )
RETURN IF ( PrevCat = 'Table B'[Category], 0, 1 )



GroupID = 
CALCULATE (
    SUM ( 'Table B'[GroupStart] ),
    FILTER (
        'Table B',
        'Table B'[ObjectId] = EARLIER ( 'Table B'[ObjectId] )
            && 'Table B'[Revision] <= EARLIER ( 'Table B'[Revision] )
    )
)


IsFirstOfLatest = 
VAR LatestGroup =
    CALCULATE ( MAX ( 'Table B'[GroupID] ), ALLEXCEPT ( 'Table B', 'Table B'[ObjectId] ) )
VAR MinRevisionInLatest =
    CALCULATE (
        MIN ( 'Table B'[Revision] ),
        FILTER ( 'Table B', 'Table B'[ObjectId] = EARLIER ( 'Table B'[ObjectId] ) && 'Table B'[GroupID] = LatestGroup )
    )
RETURN
    IF ( 'Table B'[GroupID] = LatestGroup && 'Table B'[Revision] = MinRevisionInLatest, 1, 0 )

In your visual you will need to use IsFirstOfLatest as filter to select only the rows having the value of 1 for that CC :

I attached the pbix file with the solution.

[XELANAMYT]

I've updated my question as I don't think it was clear enough.