Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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")) ) )
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")) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |