Forum Discussion
Solved
DAX: Running Total that doesn't Reset, with Inactive Relationship (pbix included)
Hi guys, I try to calculate a running total with an inactive relationship that cumulates the data per month. It should always start from the very first moment there is data available, yet there i...
Yes, this solved the problem because under the hood your DAX is transformed into:
VAR v1 = MAX('DIM Calendar'[Date]) RETURN CALCULATE( [Count New Materials], // This filter overwrites what's // coming from the outside world. FILTER( ALL( 'DIM Calendar'[Date] ), 'DIM Calendar'[Date] <= v1 ), // If your table 'DIM Calendar' is // marked in the model as a date // table, this ALL (in your code) // is not necessary since the engine // performs this line below automatically. ALL('DIM Calendar') )The other one does not work because it's equivalent to this DAX:
var MaxDate = MAX('DIM Calendar'[Date]) return CALCULATE( [Count New Materials], KEEPFILTERS( 'DIM Calendar'[Date] <= MaxDate ), ALL('DIM Calendar') )KEEPFILTERS prevents the expression from reaching rows outside the current filter context and you need to be able to do it to calculate what you want.
Interesting stuff, reading through it to get a better understanding. Thanks.
Meanwhile this somehow solved the problem:
VAR v1 = MAX('DIM Calendar'[Date])
RETURN
CALCULATE(
[Count New Materials]
,'DIM Calendar'[Date] <= v1
,ALL('DIM Calendar')
)
/* --while the following does not work:
CALCULATE(
[Count New Materials]
,FILTER('DIM Calendar', 'DIM Calendar'[Date] <= MAX('DIM Calendar'[Date]))
,ALL('DIM Calendar')
)
*/Yes, this solved the problem because under the hood your DAX is transformed into:
VAR v1 = MAX('DIM Calendar'[Date])
RETURN
CALCULATE(
[Count New Materials],
// This filter overwrites what's
// coming from the outside world.
FILTER(
ALL( 'DIM Calendar'[Date] ),
'DIM Calendar'[Date] <= v1
),
// If your table 'DIM Calendar' is
// marked in the model as a date
// table, this ALL (in your code)
// is not necessary since the engine
// performs this line below automatically.
ALL('DIM Calendar')
)
The other one does not work because it's equivalent to this DAX:
var MaxDate = MAX('DIM Calendar'[Date])
return
CALCULATE(
[Count New Materials],
KEEPFILTERS(
'DIM Calendar'[Date] <= MaxDate
),
ALL('DIM Calendar')
)
KEEPFILTERS prevents the expression from reaching rows outside the current filter context and you need to be able to do it to calculate what you want.