Forum Discussion
PowerBi Desktop Table Dynamically Calculate Table values with filter
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
)
)
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
2 Replies
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
hi vicky_
Measure works with some corrections. Thank youCumulative Hours =
CALCULATE(
SUM('ERO Overtime Request'[Hours]),
FILTER(
ALLSELECTED('ERO Overtime Request'),
'ERO Overtime Request'[HoursID] <= MAX('ERO Overtime Request'[HoursID])
)
)
