Forum Discussion
Solved
Iterating over rows based on filter to write multiple if else statement and perform calculations
In the above table: 1. Need to FILTER by ([Month Name not equal]="Y" and based on Asset Number) , since there are many other assets and this has to be done for each asset, ([Month Name not e...
ASI80486 create this calculated column:
Final Column = VAR _asset = 'asset_downtime (3)'[Asset Number] VAR _time = 'asset_downtime (3)'[Start Date] VAR _total = SUMX(FILTER('asset_downtime (3)', 'asset_downtime (3)'[Asset Number] = _asset && 'asset_downtime (3)'[Month Name not equal] = "Y"),'asset_downtime (3)'[New DT]) VAR _substract = SUMX( FILTER( 'asset_downtime (3)', 'asset_downtime (3)'[Asset Number] = _asset && 'asset_downtime (3)'[Month Name not equal] = "Y" && 'asset_downtime (3)'[Start Date] < _time ), 'asset_downtime (3)'[Hrs Bw St and End Date] ) VAR _result = SWITCH( TRUE(), 'asset_downtime (3)'[Month Name not equal] = "N", 'asset_downtime (3)'[New DT], 'asset_downtime (3)'[Hrs Bw St and End Date] <= 'asset_downtime (3)'[New DT], 'asset_downtime (3)'[Hrs Bw St and End Date], _total - _substract > 'asset_downtime (3)'[Hrs Bw St and End Date], 'asset_downtime (3)'[Hrs Bw St and End Date], _total - _substract ) RETURN MAX(_result, 0 )
here the issue is to update the New DT column with new value and then use that updated new DT value in the next IF statement to calculate the next value in the new column
Oh, I see, it's on a row-by-row basis. I don't think there's a solution using purely the Power Query UI, I think you'd have to add an index column and write some M code. It's probably something I'd solve in DAX though. Sorry I don't have time to flesh it out further, maybe other people will chime in.