Forum Discussion

Titatovenaar2's avatar
Titatovenaar2
Advocate II
5 years ago
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...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.