Count the changes made on fields in a order compare to previous value in the same order.

[Mickey123]

I have a requirement to get the count of the manual changes users made on the given fields.Order co , Order#, Ordertype and Line # are unique. Here is the data set. Can someone advise me on how to code to get these values?

Order Co	Order Number	Or Ty	Line Number	Request Date	Item	Price	User ID	Date Change count	Part change	Price change
100	1003	SN	1	4/4/2024	A204	10	JACK	0	0	0
100	1003	SN	1	4/10/2024	A205	12	PDAVIS	1	1	1
100	1004	SN	1	4/4/2024	A145	15	JACK	0	0	0
100	1004	SN	1	4/12/2024	A145	15	AMLY	1	0	0
100	1005	SN	1	4/30/2024	A999	80	JACK	0	0	0
100	1005	SN	1	4/30/2024	A999	80	AMLY	0	0	0
100	1005	SN	2	4/30/2024	A997	92	JACK	0	0	0
100	1005	SN	2	5/12/2024	A998	95	AMLY	1	1	1

[sevenhills · Accepted solution]

I will suggest to use column and not a measure for this.

 

Tip: Best is to get in the power query than using DAX!

 

Dax Column syntax (and not measure syntax): For some reason it is not allowing to provide the DAX here, please check below as the reply :-)

[dufoq3]

@Mickey123, another solution this time with desired output:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9LCoAwDETv0rVgGlM0y6IbvwiCIOL9r2HiRqtVXHQmtH1MZl2NBTCJaiY2DTrLoZRSBCQZPQIdP0QaX7ZmS94pCxfM6TWKjJWf6ykE6S3O0sG5aNyNshjDfN8tIeZCLDu3ZGaxIl7uF/aRhk8sF2P8TtNnd+3GXCh2dtt2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Co" = _t, #"Order Number" = _t, #"Or Ty" = _t, #"Line Number" = _t, #"Request Date" = _t, Item = _t, Price = _t, #"User ID" = _t]),
    FnShift = (tbl as table, optional shift as number, optional columns as list) =>
            // v 2. (nepovinnom) parametri zadaj kladne cislo ak chces posunut riadky dole, aby si videl predchadzajuce riadky (default 1)
            // v 3. (nepovinnom) parametri zadaj nazvy stlpcov ako list, ktore chces posunut (default vsetky stlpce tabulky)
            [
                cols = List.Buffer(columns ?? Table.ColumnNames(tbl)),
                sh = shift ?? 1,
                
                selectedCols = Table.SelectColumns(tbl, cols),
                shifted = Table.FromColumns(
                                Table.ToColumns(tbl) &
                                Table.ToColumns(
                                    Table.FromRows(
                                        [ shiftDown = List.Repeat({List.Repeat({null}, List.Count(cols))}, Number.Abs(sh)) &
                                                        Table.ToRows(Table.RemoveLastN(selectedCols, Number.Abs(sh))),
                                            shiftUp = Table.ToRows(Table.RemoveFirstN(selectedCols, Number.Abs(sh))) &
                                                        List.Repeat({List.Repeat({null}, List.Count(cols))}, Number.Abs(sh)),
                                            check = if sh > 0 then shiftDown else shiftUp
                                        ][check]
                                    )
                                ),
                                Value.Type( 
                                    [ a = Table.FirstN(tbl, 0),
                                      colnames = List.Transform(cols, each _ & (if sh > 0 then "_Prev" else "_Next") ),
                                      colnamesZip = List.Zip({ cols, colnames }),
                                      b = Table.RenameColumns(Table.FirstN(selectedCols, 0), colnamesZip),
                                      c = a & b
                                    ][c]
                                )
                        )
                        
            ][shifted],
    ChangedType = Table.TransformColumnTypes(Source,{{"Order Co", Int64.Type}, {"Order Number", Int64.Type}, {"Line Number", Int64.Type}, {"Request Date", type date}, {"Price", Currency.Type}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"Order Co", "Order Number", "Or Ty", "Line Number"}, {{"T", each 
        [ a = Table.AddColumn(Table.AddColumn(_, "AR", (x)=> Record.RemoveFields(x, {"Order Co", "Order Number", "Or Ty", "Line Number", "User ID"})), "AL", (y)=> Record.ToList(y[AR])),
          b = Table.AddColumn(FnShift(a, 1, {"AL"}), "B", (x)=> if x[AL_Prev] = null then null else List.Transform(List.Select(List.Zip({ x[AL], x[AL_Prev] }), (y)=> y{0} <> y{1}), (z)=> z{0})), //different values
          c = Table.AddColumn(b, "Changed Cols", (x)=> if List.Contains({null, {}}, x[B]) then null else List.Transform(x[B], (y)=> Record.FieldNames(x[AR]){List.PositionOf(x[AL], y)})),
          d = List.Accumulate(Record.FieldNames(c{0}[AR]), c, (st, cur)=> Table.AddColumn(st, cur & " Change", (x)=> if x[Changed Cols] = null then 0 else if List.Contains(x[Changed Cols], cur) then 1 else 0, Int64.Type)),
          e = Table.RemoveColumns(d, {"AR", "AL", "AL_Prev", "B", "Changed Cols"})
        ][e], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[dufoq3]

Hi @Mickey123, what about this?

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9LCoAwDETv0rVgGlM0y6IbvwiCIOL9r2HiRqtVXHQmtH1MZl2NBTCJaiY2DTrLoZRSBCQZPQIdP0QaX7ZmS94pCxfM6TWKjJWf6ykE6S3O0sG5aNyNshjDfN8tIeZCLDu3ZGaxIl7uF/aRhk8sF2P8TtNnd+3GXCh2dtt2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Co" = _t, #"Order Number" = _t, #"Or Ty" = _t, #"Line Number" = _t, #"Request Date" = _t, Item = _t, Price = _t, #"User ID" = _t]),
    FnShift = (tbl as table, optional shift as number, optional columns as list) =>
            // v 2. (nepovinnom) parametri zadaj kladne cislo ak chces posunut riadky dole, aby si videl predchadzajuce riadky (default 1)
            // v 3. (nepovinnom) parametri zadaj nazvy stlpcov ako list, ktore chces posunut (default vsetky stlpce tabulky)
            [
                cols = List.Buffer(columns ?? Table.ColumnNames(tbl)),
                sh = shift ?? 1,
                
                selectedCols = Table.SelectColumns(tbl, cols),
                shifted = Table.FromColumns(
                                Table.ToColumns(tbl) &
                                Table.ToColumns(
                                    Table.FromRows(
                                        [ shiftDown = List.Repeat({List.Repeat({null}, List.Count(cols))}, Number.Abs(sh)) &
                                                        Table.ToRows(Table.RemoveLastN(selectedCols, Number.Abs(sh))),
                                            shiftUp = Table.ToRows(Table.RemoveFirstN(selectedCols, Number.Abs(sh))) &
                                                        List.Repeat({List.Repeat({null}, List.Count(cols))}, Number.Abs(sh)),
                                            check = if sh > 0 then shiftDown else shiftUp
                                        ][check]
                                    )
                                ),
                                Value.Type( 
                                    [ a = Table.FirstN(tbl, 0),
                                      colnames = List.Transform(cols, each _ & (if sh > 0 then "_Prev" else "_Next") ),
                                      colnamesZip = List.Zip({ cols, colnames }),
                                      b = Table.RenameColumns(Table.FirstN(selectedCols, 0), colnamesZip),
                                      c = a & b
                                    ][c]
                                )
                        )
                        
            ][shifted],
    ChangedType = Table.TransformColumnTypes(Source,{{"Order Co", Int64.Type}, {"Order Number", Int64.Type}, {"Line Number", Int64.Type}, {"Request Date", type date}, {"Price", Currency.Type}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"Order Co", "Order Number", "Or Ty", "Line Number"}, {{"T", each 
        [ a = Table.AddColumn(Table.AddColumn(_, "AR", (x)=> Record.RemoveFields(x, {"Order Co", "Order Number", "Or Ty", "Line Number", "User ID"})), "AL", (y)=> Record.ToList(y[AR])),
          b = Table.AddColumn(FnShift(a, 1, {"AL"}), "B", (x)=> if x[AL_Prev] = null then null else List.Transform(List.Select(List.Zip({ x[AL], x[AL_Prev] }), (y)=> y{0} <> y{1}), (z)=> z{0})), //different values
          c = Table.AddColumn(b, "Changed Cols", (x)=> if List.Contains({null, {}}, x[B]) then null else Text.Combine(List.Transform(x[B], (y)=> Record.FieldNames(x[AR]){List.PositionOf(x[AL], y)}), " | "), type text),
          d = Table.AddColumn(Table.RemoveColumns(c, {"AR", "AL", "AL_Prev", "B"}), "Change", (x)=> if x[Changed Cols] = null then 0 else 1, Int64.Type)
        ][d], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[sevenhills]

I guess you are looking for group by count for the columns.

Try this

Measure Change Count = calculate (
countrows(), allexcept(tbl, tbl[Order Co],tbl[Order Number], tbl[Or Ty], tbl[Line Number])
)

Optional Try this, if you are interested in using group by

Measure Change Count 2 = 
   sumx(  GROUPBY(tbl, tbl[Order Co], tbl[Order Number], tbl[Or Ty], tbl[Line Number], "cnt", SUMX ( CURRENTGROUP(), 1 ) ), [cnt])

Sample output:

[Mickey123]

Hey Sevenhills, Thanks, but I am looking for something different. For, e.g., in Order 1003, the req date, item, and price changed. Hence, the count calc column will have respective 1. The original order line will have zero for all the count fields. If there is no change, the count column value will be zero. I hope I did not confuse the requirement.

Thanks

Mickey123

[sevenhills · Accepted solution]

I will suggest to use column and not a measure for this.

 

Tip: Best is to get in the power query than using DAX!

 

Dax Column syntax (and not measure syntax): For some reason it is not allowing to provide the DAX here, please check below as the reply :-)

[Mickey123]

Hey, Sevenhills, Excellent. It serves the purpose. Thank you!

[sevenhills · Accepted solution]

Row Index By Group = ROWNUMBER( ALLSELECTED(tbl[Order Co], tbl[Order Number], tbl[Or Ty], tbl[Line Number], tbl[Item], tbl[Request Date]), ORDERBY( tbl[Item], ASC, tbl[Request Date], ASC), PARTITIONBY(tbl[Order Co], tbl[Order Number], tbl[Or Ty], tbl[Line Number])) - 1