topic Re: Conditional Column Calculation Running Total in DAX Commands and Tips

Conditional Column Calculation Running Total

Anonymous — Tue, 15 Dec 2020 14:44:59 GMT

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

Re: Conditional Column Calculation Running Total

AlB — Tue, 15 Dec 2020 20:34:56 GMT

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

Re: Conditional Column Calculation Running Total

AlB — Wed, 16 Dec 2020 19:19:40 GMT

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