topic Repeated Calculated Column Optimization in DAX Commands and Tips

Repeated Calculated Column Optimization

NBU_FFF — Mon, 31 Mar 2025 12:18:47 GMT

Dear community, I have a table need to add several calculation columns.

Day0 = '1'[Qty]-'1'[Qty]/('1'[End_GroupQty_Previous]+'1'[VS_Qty])*'1'[Capacity] Day1 = VAR CurrentDate = '1'[MAV] VAR CurrentVS = '1'[VS] VAR CurrentLine = '1'[Line] VAR Previous_Qty2 = CALCULATE( MAX('1'[Day0]), FILTER( '1', '1'[vs] = CurrentVS && '1'[line] = CurrentLine && '1'[mav] < CurrentDate ), TOPN(1, FILTER('1', '1'[vs] = CurrentVS && '1'[line] = CurrentLine && '1'[mav] < CurrentDate), '1'[mav], DESC ) ) RETURN ('1'[Qty] + Previous_Qty2) - ('1'[Qty] + Previous_Qty2) / ('1'[End_GroupQty_Previous] + '1'[VS_Qty]) * '1'[Capacity] Day2 = VAR CurrentDate = '1'[MAV] VAR CurrentVS = '1'[VS] VAR CurrentLine = '1'[Line] VAR Previous_Qty2 = CALCULATE( MAX('1'[Day1]), FILTER( '1', '1'[vs] = CurrentVS && '1'[line] = CurrentLine && '1'[mav] < CurrentDate ), TOPN(1, FILTER('1', '1'[vs] = CurrentVS && '1'[line] = CurrentLine && '1'[mav] < CurrentDate), '1'[mav], DESC ) ) RETURN ('1'[Qty] + Previous_Qty2) - ('1'[Qty] + Previous_Qty2) / ('1'[End_GroupQty_Previous] + '1'[VS_Qty]) * '1'[Capacity]

The same logic for day2 applied on days3 and next by only change last row '1'[Qty] to '2'[Qty], '3'[Qty]…

In other words, in order to get Day9, I have to create columns next from Day3 to Day9.

Do we have other ways to use recurrence and create day9 directly? I Attached my sample file for your reference.

sample.pbix

Re: Repeated Calculated Column Optimization

DataNinja777 — Mon, 31 Mar 2025 14:50:19 GMT

Hi NBU_FFF ,

The best approach to optimize your repeated Day0–Day9 calculated columns is to offload the recursive logic into Power Query using index-based row referencing. DAX is not well-suited for recursion over calculated columns because it lacks true looping behavior and cannot reference previously calculated column values in subsequent rows. Power Query, on the other hand, allows row-by-row processing using index columns and custom steps. You can start by sorting your table by [Line], [VS], and [MAV], then add an index column starting from 0. This index will serve as the “day” indicator, and you can reference the previous row's result for the recursive logic. After adding the index, you create a custom column such as [DayQty] using a recursive function. Here’s a simplified version of how the logic might look:

let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], SortedRows = Table.Sort(Source,{{"Line", Order.Ascending}, {"VS", Order.Ascending}, {"MAV", Order.Ascending}}), AddIndex = Table.AddIndexColumn(SortedRows, "Day", 0, 1, Int64.Type), AddDayQty = Table.AddColumn(AddIndex, "DayQty", each null, type number), RecursiveCalculation = List.Accumulate( {0..Table.RowCount(AddDayQty)-1}, AddDayQty, (state, current) => let row = state{current}, prev = if current = 0 then null else state{current - 1}, qty = row[Qty], cap = row[Capacity], endGroupQty = row[End_GroupQty_Previous], vsQty = row[VS_Qty], prevQty = if current = 0 then null else prev[DayQty], calc = if current = 0 then qty - qty / (endGroupQty + vsQty) * cap else (qty + prevQty) - (qty + prevQty) / (endGroupQty + vsQty) * cap, newState = Table.ReplaceRows(state, current, {Record.TransformFields(row, {"DayQty", each calc})}) in newState ) in RecursiveCalculation

This logic processes the table row-by-row while calculating the DayQty recursively. The first row (Day0) uses your original formula, and all subsequent rows use the previous row’s DayQty to compute the current one. This way, you no longer need to manually create Day1 to Day9 columns in DAX. You end up with a single [DayQty] column that contains the recursive result for each day index, and the logic can be extended to as many days as needed without repetition.

Best regards,

Re: Repeated Calculated Column Optimization

NBU_FFF — Tue, 01 Apr 2025 13:26:59 GMT

DataNinja777 thanks a lot for your reply and explain. Unfortunately my data was combined from different sources and could not be processed in Power Query only. What's why I asked for the help with DAX.

Re: Repeated Calculated Column Optimization

DataNinja777 — Tue, 01 Apr 2025 14:51:46 GMT

Hi NBU_FFF ,

Apologies, my bad, DAX does not support true recursion, especially in calculated columns or tables where the value of a row depends on the value of a previous row in the same column. If you're trying to implement logic where each "DayN" calculation depends on the previous day’s result, DAX is not the right tool for this kind of recursion.

Best regards,

Re: Repeated Calculated Column Optimization

NBU_FFF — Tue, 01 Apr 2025 14:42:48 GMT

DataNinja777 Thanks for your help again. I tried but showed error below:

Seemed DAX not support Recursive, not sure how you achieve that without error.

I attached my PBI file here, could you pls have a look?

sample_Dax.pbix

Re: Repeated Calculated Column Optimization

Anonymous — Thu, 03 Apr 2025 11:18:46 GMT

Hi NBU_FFF,

DAX doesn't support true recursion in calculated columns or tables because it lacks row-by-row iteration based on previous results. Since Power Query isn't an option either, the best approach would be to use a measure instead of calculated columns.

Limitation: This won’t work for deep recursion across multiple rows because DAX measures rely on context, not row-by-row memory like Power Query.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

Re: Repeated Calculated Column Optimization

v-kathullac — Mon, 07 Apr 2025 12:04:11 GMT

Hi NBU_FFF ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

Re: Repeated Calculated Column Optimization

v-kathullac — Fri, 11 Apr 2025 11:43:16 GMT

Hi @NBU_FFF ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

Re: Repeated Calculated Column Optimization

v-kathullac — Thu, 17 Apr 2025 16:04:59 GMT

Hi @NBU_FFF ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.