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 )
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 )
