Running total with ALL() is affected by visual filter

[bonus60]

Dear power bi community,

I have a weird problem. Trying to write a calculated measure to calculate a running total by category. The calculation is working so far, however I have problems when using Filters on the visual, to exclude some categories. Doing so, the calculation will be wrong. In my understanding, the keyword ALL() ignores all filters within the filter context, belongs to inner as well as outer filter. However, my measure is affected by a visual filter. I would like to share the data, unfortuanely it is not possible. Hope you guys can still help me.

My measure is:

RunningTotal =

VAR MaxCategory = MAX(table[category])

RETURN

 CALCULATE(

     CALCULATE(

       DISTINCTCOUNT(table[issues]),

       table[category] <= MaxCategory

      ),

  ALL(table[category])

 )

What I am doing wrong?

[bonus60]

I am not sure why it works, but when I remove the filters from filter context with ALL( ) and set the filter values withthin FILTER( ) again, then it works as expected...

[speedramps]

Hi bonus60

ALL removes context filters.

REMOVEFILTERS will not remove SLICER or visual FILTERS

When visual query runs, it first applies the visual slicer and filters.  (eg customer = 4567)

It then applies the context "grid" filters in the matrix for example by product by year.

ALL will overide the context filter and show all products for all years but will not override the customer slicer/filter

However REMOVEFILTERS will override the customer slicer/filter,

Please click thumbs up and accept as solution button. Thank you ! 😎

See youtube for DAX ALL vs REMOVEFILTERS videos

[bonus60]

Thank you very much @speedramps! I have tried to substitute ALL with REMOVEFILTERS, but that did not work. Or do I have to write the measure in another way?

[PaulDBrown]

Try:

RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER ( ALL ( table ), table[category] <= MaxCategory )
    )

[bonus60]

Thank you very much @PaulDBrown, I think we get closer to the solution, that gives me hope. I did it exactly you described, and checked if the calculation changes, when applying a visual filter and see the calculation is still the same and it is correct. But now I have a nother problem. My four slicers (filters) on the page are affected when using ALL() on the whole table. So the calculation is correct, but the predifined slicer values are missing, so the calculation is not that I want to have. Is there any way, to tell the measure like: Do ALL, but keep the necessary slicers values?
Thank you in advance!

EDIT:

Have tried following, but calculation changes when using table[category] as visual filter.

RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER ( ALLEXCEPT ( table, table[slicer_1], table[slicer_2], table[slicer_3], table[slicer_4] ), table[category] <= MaxCategory )
    )

[v-easonf-msft · Accepted solution]

Hi, @bonus60 

If your just want the four slicers (filters) on the page are affected, please try formula as below:

RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER (
            ALL ( table ),
            table[slicer_1] = MAX ( table[slicer_1] )
                && table[slicer_2] = MAX ( table[slicer_2] )
                && table[slicer_3] = MAX ( table[slicer_3] )
                && table[slicer_4] = MAX ( table[slicer_4] )
                && table[category] <= MaxCategory
        )
    )

Best Regards,
Community Support Team _ Eason

[bonus60]

Hi @v-easonf-msft  thank you very much, unfortuanely it does still not work.

[PaulDBrown]

Try using ALL(Table[Category])... instead of ALL(Table)....

[bonus60]

Thanks @PaulDBrown, unfortuanely the calculation is still changing, when filtering some categories out with visual filter...

[PaulDBrown]

Sorry, I'm getting lost...

any chance you can provide sampe data and a depiction of the expected outcome?