What if I want to use a cumulative measure but still be able to apply filters

[osinquinvdm]

I created a measure that it is accurately giving a running count over a subset of the data

Nb cumulatif de DDS_ALL = CALCULATE (
    COUNT ( '311_Details'[DDS] );
    FILTER (
         ALL('311_Details');('311_Details'[Nature]<>"Information")&&(
        '311_Details'[Date de Création] <= MAX ( '311_Details'[Date de Création] ))
    )
)	

But I’d still like to apply filters so I tried replacing ALL('311_Details') by ALL('311_Details'[Date de Création]),

Nb cumulatif de DDS_ALL Dates = CALCULATE (
    COUNT ( '311_Details'[DDS] );
    FILTER (
         ALL('311_Details'[Date de Création]);('311_Details'[Nature]<>"Information")&&(
        '311_Details'[Date de Création] <= MAX ( '311_Details'[Date de Création] ))
    )
)		

But then I get an error message

That I solved by wrapping the column in a MAX() function.

WHY DO I GET THAT ERROR MESSAGE WHEN USING ALL() ON A SPECIFIC COLUMN?

IS WRAPPING THE COLUMN INTO A MAX() THE WAY TO GO?

Nb cumulatif de DDS_ALL Dates = CALCULATE (
    COUNT ( '311_Details'[DDS] );
    FILTER (
         ALL('311_Details'[Date de Création]);(Max('311_Details'[Nature])<>"Information")&&(
        '311_Details'[Date de Création] <= MAX ( '311_Details'[Date de Création] ))
    )
)	

But then, when I filter on a specific activity  I can see that filtering get applied because in 2014 I go from 338,132 to 11,391.

But the filter on [Nature])<>"Information” did not get applied

I have to manually apply it to get to the expected number

Which actually answers to my question above, wrapping the column into a MAX() function is not the way to go! J

I also tried using ALLEXCEPT('311_Details'; '311_Details'[Nature]), but that did not make a difference: I still get to use the max() wrapper.

and I get crazy numbers because the filter on the activity is not applied either

Which I am able to solve by adding the activity column to the set of columns I can filter in

ALLEXCEPT('311_Details'; '311_Details'[Nature]; '311_Details'[Activité]))

But I can’t add all columns that way.

What I want is to be able to filter on any column but still get the embedded filter on [Nature])<>"Information” to apply – without having to resort to a page wide filter or some error prone filter like this.

Any explanation/idea?

Thanks! 

[osinquinvdm · Accepted solution]

It's funny how a good night of sleep can bring clarity to any issue.

In the end it is just a matter of including in the ALL() every column that is used in the filtering conditions

Nb cumulatif de DDS_ALL Dates = CALCULATE (
    COUNT ( '311_Details'[DDS] );
    FILTER (
         ALL('311_Details'[Date de Création]; '311_Details'[Nature]);('311_Details'[Nature]<>"Information")&&(
        '311_Details'[Date de Création] <= MAX ( '311_Details'[Date de Création] ))
    )
)	

 or like in this more advanced measure that counts only open requests and requires to 5 columns for the filtering logic:

Nb cumulatif de DDS ouvertes = CALCULATE (
    COUNT ( '311_Details'[DDS] );
    FILTER (
        ALL('311_Details'[Date de Création];'311_Details'[Date de Fin];'311_Details'[Date de Début];'311_Details'[Type de Statut];'311_Details'[Nature]);
        (('311_Details'[Date de Création]) <= MAX('311_Details'[Date de Création]))
        && (('311_Details'[Nature]) <> "Information")
		&& 
			((('311_Details'[Type de Statut])="Ouvertes")
            ||
			(('311_Details'[Date de Fin]) > MAX('311_Details'[Date de Création]) && ('311_Details'[Type de Statut])="Closes"))
             )
        )