PowerBi Desktop Table Dynamically Calculate Table values with filter

[Yerlasdu95]

Hi Folks, 

Could you please help me with this issue?!

I have Column HourID in table. I need new column, which will calculate sum of this Column

f.e 1 = 1

2= 3 (1+2).

3 = 6 (1+2+3)

6 = 12 (1+2+3+6)

It works. But, when I filter the table, it not calculate it again with filtered values:

It should be: 2 =2; 4=6 (2+4)

Calculated Column Dax:

col = 
VAR CurrentHoursID = 'ERO Overtime Request'[HoursID]
RETURN
CALCULATE(
SUM('ERO Overtime Request'[Hours]),
FILTER(
ALL('ERO Overtime Request'),
'ERO Overtime Request'[HoursID] <= CurrentHoursID
)
)

[vicky_ · Accepted solution]

The issue is that you're using a calculated column. Your filters cannot dynamically alter the values that are in a calculated column because these values are only evaluated when the report is refreshed - i.e you need to use a measure instead. 

Your existing DAX can be altered slightly to fit your problem - 

measure = 
var currentHrs = SELECTEDVALUE('ERO Overtime Request'[Hours])
return CALCULATE(SUM('ERO Overtime Request'[Hours]), FILTER(ALL('ERO Overtime Request'[Hours]), 'ERO Overtime Request'[Hours] <= currentHrs))

the only part that changed is that i will only remove the filter on hours, rather than the whole table, so that will allow the table to be filtered by other columns.

with the filter

without the filter

[Yerlasdu95]

hi @vicky_ 
Measure works with some corrections. Thank you

Cumulative Hours = 
CALCULATE(
SUM('ERO Overtime Request'[Hours]),
FILTER(
ALLSELECTED('ERO Overtime Request'),
'ERO Overtime Request'[HoursID] <= MAX('ERO Overtime Request'[HoursID])
)
)