Forum Discussion

Mickey123's avatar
Mickey123
Regular Visitor
1 year ago
Solved

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

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 CoOrder NumberOr TyLine NumberRequest DateItemPriceUser IDDate Change countPart changePrice change 
1001003SN14/4/2024A20410JACK000 
1001003SN14/10/2024A20512PDAVIS111 
1001004SN14/4/2024A14515JACK000 
1001004SN14/12/2024A14515AMLY100 
1001005SN14/30/2024A99980JACK000 
1001005SN14/30/2024A99980AMLY000 
1001005SN24/30/2024A99792JACK000 
1001005SN25/12/2024A99895AMLY111 
  • 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 :-)
     
     
     
  • sevenhills's avatar
    sevenhills
    1 year ago
    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

7 Replies

  • 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's avatar
      Mickey123
      Regular Visitor

      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's avatar
        sevenhills
        Icon for Super User rankSuper User

        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's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion

    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

     

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion

    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