Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Generate groups using previous rows

Hi there! I have the following table order by Date DESC:   Product Type Cycle Date Product 1 WO NO 15/03/2015 Product 1 WO YES 22/10/2015 Product 1 WO NO 31/03/2016 Produ...
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    You can create an index column in query editor:

     

    And the use the following calculated column:

     

     

    Column = VAR A = CALCULATE(DISTINCTCOUNT('Table'[Index]),FILTER('Table','Table'[Index]<= EARLIER('Table'[Index])&&'Table'[Cycle] = "YES")) RETURN IF('Table'[Cycle]= "NO",A+1,A)

     

     

    For more details, please refer to the pbix file https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcJOk6WxpPJGgtsQubDfKaUBvgNo2B1qbezD1agnJv8lvw?e=3F4fbx

     

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

     

    Best Regards,

    Dedmon Dai

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi everyone!

     

    Thanks all for your help. As lbendlin , Greg_Deckler  and v-deddai1-msft  said I needed an index into dataset.

     

    I got a solution doing this (i think is very similar to the v-deddai1-msft solution):

     

    Group = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            Table,
            Table[Date] < EARLIER ( Table[Date] )
                && Table[Product] = EARLIER (Table[Product] )
        ),
        [Date], DESC
    )
VAR PreviousCycle =
    MINX ( PreviousRow, [Cycle] )
VAR PreviousIndex =
    MINX ( PreviousRow, [Index] )
RETURN
    If(isblank(PreviousCycle),Table[Index], If (PreviousCycle = "YES", PreviousIndex+1,PreviousIndex))

     

    Thanks everyone again!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

I think you meant ASCending sort.  Can you add an index column to your source data? If yes then you can use that when you add the group column.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRCvcHEn4gwtBU38BY38jA0FQpVgeLokjXYCBpZKRvaIBHFdgoY0NijDIw1zewAKkyx6cKZJYlHlVgC4EqIK4iZJShIVRVLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Type = _t, Cycle = _t, Date = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", 
each 1+List.Count(
    List.Select(
        Table.Column(
            Table.FirstN(#"Added Index",[Index])
            ,"Cycle")
        ,each _ ="YES")
    )
)
in
    #"Added Custom"

 

 

Note that your sample data is inconsistent.