Conditional Column Calculation Running Total

[Anonymous]

Hi, 

I am trying to set up a conditional running total in a string of numbers similar to my excel screenshot below. The output column will output a nonzero value once column A recoginises a Set point value of 1 (column 1) and keep counting until it sees the end point (column 2) value 1 again. I have this completed in excel, but I am struggling to do this in power bi as i cannot reference the previous row in the calculation column (output) similar to excel. I am trying to mimic this exactly in power bit, any help is appreciated

[AlB · Accepted solution]

Hi @Anonymous 

You could do this in DAX but it's probably best to do it in Power Query. Place the following M code in a blank query to see the steps. See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAeJYHWSWIRYxVJYhTlkS9cYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start point" = _t, #"End point" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start point", Int64.Type}, {"End point", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    listStart_ = List.PositionOf(#"Added Index"[Start point], 1, Occurrence.All),
    listEnd_ = List.PositionOf(#"Added Index"[End point], 1, Occurrence.All),
    listT_ = List.Combine(List.Transform(List.Zip({listStart_, listEnd_}), each List.Numbers(_{0}+1,_{1}-_{0}))),
    listF_ = List.Transform(List.Numbers(0,Table.RowCount(#"Added Index")), each if List.Contains(listT_, _) then 1 else 0),
    final_ = Table.AddColumn(#"Added Index", "Output", each listF_{[Index]}),
    #"Removed Columns" = Table.RemoveColumns(final_,{"Index"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

If you want it in DAX, you need in any case to add an Index column to the table in PQ to establish an order in the rows. Then you can create a calculated column:

Output =
VAR prevStart_ =
    CALCULATE (
        MAX ( Table1[Index] ),
        Table1[Start point] = 1,
        Table1[Index] < EARLIER ( Table1[Index] ),
        ALL ( Table1 )
    )
VAR prevEnd_ =
    CALCULATE (
        MAX ( Table1[Index] ),
        Table1[End point] = 1,
        Table1[Index] < EARLIER ( Table1[Index] ),
        ALL ( Table1 )
    )
RETURN
    IF ( prevStart_ > prevEnd_, 1, 0 )

 See it all at work in the attached file. Note I have left the column aux in the table. You do not need it. It is just to  help understand what Output does. 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @Anonymous 

You could do this in DAX but it's probably best to do it in Power Query. Place the following M code in a blank query to see the steps. See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAeJYHWSWIRYxVJYhTlkS9cYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start point" = _t, #"End point" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start point", Int64.Type}, {"End point", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    listStart_ = List.PositionOf(#"Added Index"[Start point], 1, Occurrence.All),
    listEnd_ = List.PositionOf(#"Added Index"[End point], 1, Occurrence.All),
    listT_ = List.Combine(List.Transform(List.Zip({listStart_, listEnd_}), each List.Numbers(_{0}+1,_{1}-_{0}))),
    listF_ = List.Transform(List.Numbers(0,Table.RowCount(#"Added Index")), each if List.Contains(listT_, _) then 1 else 0),
    final_ = Table.AddColumn(#"Added Index", "Output", each listF_{[Index]}),
    #"Removed Columns" = Table.RemoveColumns(final_,{"Index"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers