Increment a counter based upon the changing value of another field

[wynrod]

Dear All,

I'm trying to build a calculated column which will increment a value whenever an adjacent field value changes, like a break-key. In the example below, GroupID increments by 1 whenever the value in the GroupKey column changes:

GroupKey		GroupID
C00001R000001D99999918704		1
C00001R000001D99999918704		1
C00001R000001D9999991121053		2
C00001R000001D9999991121053		2
C00001R000001D9999991161408		3
C00001R000001D9999991161408		3
C00001R000001D9999991161408		3
C00001R000001D9999991243920		4
C00001R000001D9999991243920		4
C00001R000001D9999991244723		5
C00001R000001D9999991244723		5

Any suggestions on the best approach would be very welcomed, achieving this in Excel is very straightforward but less so  in Power BI.

Best regards,

Nick

[az38]

Hi @wynrod 

whats rule for GroupKey sorting?

How DAX will define the next and the previous row?

[wynrod]

Hi there, thanks for the response.

The GroupKey field is pre-sorted ascending, its actually a compund key of 5 separate fields concatenated, each sorted asc. The approach I've been trying to use is to determine when the current value of GroupKey is different from the previous one. If this is the case then increment GroupID by 1, otherwise keep it the same.

I hope that clarifies the situation some more.

Best regards,

N

[dax · Accepted solution]

Hi @wynrod , 

You could try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjYAAsMgAzDlYgkGhhbmBiZKOkqGSrE6ZKswNDI0MDUGqjGiUI2ZoYmBBVCNMe3VGJkYWxoZANWYUKrGxNwI5C9T4tTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [GroupKey = _t, GroupID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GroupKey", type text}, {"GroupID", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"GroupID"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"GroupKey"}, {{"all", each _, type table [GroupKey=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded all" = Table.ExpandTableColumn(#"Added Index", "all", {"GroupKey"}, {"GroupKey.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded all",{"GroupKey"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"GroupKey.1", "GroupKey"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

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

[wynrod]

@dax many thanks, that's worked well!

Much appreciated,

Nick