Cumulative count which resets upon a condition and starts over

[Vivek_KV_]

Hello team,
I have a table consisting of 3 columns - FST, XTRA & THRD. I am encountering challenges in creating a calculated column. The desired output is represented in the Target column. The specified conditions are as follows: if the value in the FST column is "Y," assign 1; if the XTRA column is "Y," assign the previous value in the Target column plus 1; if the THRD column is "Y," also assign the previous value in the Target column plus 1; otherwise, assign the previous value in the Target column. Every time FST becomes "Y" then the count resets and every time none of the condition satisfies then it should print the previous record. Your assistance in resolving this matter would be greatly appreciated and I have attached the power query for data.


let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lPSgWMDpVgdfCKRQGwIF4mEihph6MInEgnFxmCRSCQRQww1hMzxg5uDrAZhMkzEEEMNPhEk22MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FST = _t, XTRA = _t, THRD = _t, #"Target/MSG_SEQ" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FST", type text}, {"XTRA", type text}, {"THRD", type text}, {"Target/MSG_SEQ", Int64.Type}})
in
#"Changed Type"



.

[Anonymous · Accepted solution]

Hi @Vivek_KV_ ,

Please refer these steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lPSgeJYHVy8SDgvkoBKPxSVEH2RKDw/DB6mPmxmRhLhTgzbYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FST = _t, XTRA = _t, THRD = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [FST] = "Y" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Group"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,0,Replacer.ReplaceValue,{"Group"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Group"}, {{"AllData", each Table.AddIndexColumn(_, "Index2", 0, 1, Int64.Type)}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"FST", "XTRA", "THRD", "Index", "Index2"}, {"FST", "XTRA", "THRD", "Index", "Index2"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded AllData", "Value", each if [FST] = "Y" or [XTRA] = "Y" or [THRD] = "Y" then 1 else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "MSG_SEQ", each List.Sum(Table.SelectRows(#"Added Custom1",(x)=>x[Group]=[Group] and x[Index2] <= [Index2])[Value])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Group", "Index", "Index2", "Value"})
in
    #"Removed Columns"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Vivek_KV_]

Hi @Anonymous ,
this solution does the job for small data files, for big data files it's showing performance issues. Can you convert the following custom column query into dax?

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "MSG_SEQ", each List.Sum(Table.SelectRows(#"Added Custom1",(x)=>x[Group]=[Group] and x[Index2] <= [Index2])[Value]))