ALLEXCEPT dans Power BI : garder un filtre, supprimer tous les autres
ALLEXCEPT supprime les filtres d'une table, sauf ceux que vous citez.
Son écriture est inversée : vous nommez ce que vous gardez, pas ce que vous retirez.
C'est la fonction du pourcentage à l'intérieur d'un groupe.
Cet article la démontre sur un jeu de données fictif, valeur par valeur.
Télécharger l'exercice ALLEXCEPT en DAX
Le pack est gratuit et ne demande aucune inscription.
Il contient tout ce qu'il faut pour refaire la démonstration.
Un classeur Excel avec la table Ventes complète, soit 60 lignes.
Huit questions corrigées, avec validation automatique de vos réponses.
Un onglet Exemple qui reprend exactement les cinq lignes ci-dessous.
Un fichier texte contenant les sept mesures DAX de l'article.
La validation s'affiche dès que votre réponse est correcte.
Les données du classeur sont fictives. Elles ne décrivent aucune entreprise réelle.
Un exemple simple pour comprendre ALLEXCEPT
Prenons cinq lignes de ventes. Chaque ligne porte une ville, sa région et un chiffre d'affaires.
Ville | Région | CA |
Lyon | Sud-Est | 40 000 € |
Grenoble | Sud-Est | 20 000 € |
Lille | Nord | 25 000 € |
Amiens | Nord | 15 000 € |
Nantes | Ouest | 20 000 € |
Le total vaut 120 000 €. Le Sud-Est pèse 60 000 €, le Nord 40 000 €, l'Ouest 20 000 €.
Plaçons ces données dans une matrice. La région en lignes, la ville en sous-lignes.
Voici la mesure qui calcule le chiffre d'affaires de la région.
CA de la région =
CALCULATE (
[CA total],
ALLEXCEPT ( Ventes, Ventes[Région] )
)Plaçons-nous sur la ligne Lyon. Le visuel y pose deux filtres.
Le premier vaut Région égale Sud-Est. Le second vaut Ville égale Lyon.
ALLEXCEPT retire le filtre de ville. Il conserve celui de région, la seule colonne citée.
Ville | Région | CA | Retenu ? |
Lyon | Sud-Est | 40 000 € | ✓ |
Grenoble | Sud-Est | 20 000 € | ✓ |
Lille | Nord | 25 000 € | ✗ |
Amiens | Nord | 15 000 € | ✗ |
Nantes | Ouest | 20 000 € | ✗ |
Total | 60 000 € |
La ligne Lyon affiche donc 60 000 €. C'est le chiffre d'affaires de toute sa région.
ALLEXCEPT n'a rien additionné. Il a seulement décidé quels filtres survivent au calcul.
Syntaxe
ALLEXCEPT ( <table>, <colonne> [, <colonne> [, …] ] )
table : la table dont les filtres sont supprimés.
colonne : la colonne dont le filtre doit être conservé.
Le premier argument doit être une table de base. Les suivants doivent être des colonnes de base.
Aucune expression de table ni de colonne n'est acceptée.
Au moins une colonne est obligatoire. Sans elle, écrivez plutôt ALL ou REMOVEFILTERS.
Le sens inversé : vous citez ce que vous gardez
C'est la source de confusion la plus fréquente.
ALL cite ce qu'il supprime. ALLEXCEPT cite ce qu'il conserve.
Écriture | Ce qu'elle fait |
ALL ( Ventes[Ville] ) | Supprime le seul filtre posé sur la colonne Ville |
ALLEXCEPT ( Ventes, Ventes[Ville] ) | Supprime tous les filtres de Ventes, sauf celui de Ville |
Les deux écritures ne sont donc pas équivalentes. Elles sont presque opposées.
Le pourcentage à l'intérieur d'un groupe
Voici le cas d'usage principal de la fonction.
Vous voulez la part de chaque ville dans sa propre région.
La mesure, pas à pas
Le numérateur reste soumis au contexte du visuel.
Le dénominateur doit remonter au niveau de la région. C'est le travail d'ALLEXCEPT.
% de la région =
VAR CaVille = [CA total]
VAR CaRegion =
CALCULATE (
[CA total],
ALLEXCEPT ( Ventes, Ventes[Région] )
)
RETURN
DIVIDE ( CaVille, CaRegion )Sur la ligne Lyon, le numérateur vaut 40 000 €. Le dénominateur vaut 60 000 €.
La part affichée est donc de 66,7 %.
Les variables ne changent pas le résultat. Elles rendent la mesure lisible et facile à déboguer.
La fonction DIVIDE protège le calcul si une région se retrouve sans vente.
La ligne de région affiche 100 %
Le comportement se répète à chaque niveau de la matrice.
Sur la ligne Sud-Est, aucun filtre de ville n'est actif.
Le numérateur et le dénominateur valent alors tous les deux 60 000 €.
La ligne de région affiche donc 100 %. C'est le sens même d'un pourcentage de groupe.
Le piège n°1 : ALLEXCEPT ne pose aucun filtre
Voici l'erreur qui produit des chiffres faux en silence.
Retirez la région de la matrice. Ne gardez que la ville en lignes.
La mesure ne change pas. Le résultat, lui, change du tout au tout.
ALLEXCEPT cherche un filtre à conserver sur la colonne Région. Il n'en trouve aucun.
Il ne le crée pas pour autant. Il supprime donc tout le contexte de la table.
Le dénominateur devient le total général, soit 120 000 €.
Sur la ligne Lyon, la part tombe à 33,3 %. La valeur attendue était 66,7 %.
Attention
ALLEXCEPT conserve un filtre existant. Il n'en ajoute jamais.
Si la colonne citée n'est filtrée nulle part, la fonction efface tout le contexte de la table.
Le symptôme à repérer
Un pourcentage de groupe dont l'ensemble des lignes totalise 100 % trahit ce défaut.
Chaque groupe devrait totaliser 100 % de son côté, pas le visuel entier.
Posez une mesure de contrôle pour lever le doute.
Région filtrée ? =
ISFILTERED ( Ventes[Région] )Si elle renvoie Faux, ALLEXCEPT ne conserve rien.
La parade : REMOVEFILTERS puis VALUES
Cette écriture est plus longue. Elle ne dépend pas de la disposition du visuel.
% de la région =
DIVIDE (
[CA total],
CALCULATE (
[CA total],
REMOVEFILTERS ( Ventes ),
VALUES ( Ventes[Région] )
)
)REMOVEFILTERS vide le contexte de la table. VALUES relit la région visible, puis la réapplique.
Sur la ligne Lyon, VALUES renvoie Sud-Est, même si la colonne n'est pas affichée.
Le dénominateur redevient 60 000 €, dans les deux dispositions de matrice.
Le piège n°2 : la table étendue
Le second piège apparaît dès que le modèle compte plusieurs tables.
Reprenons un schéma en étoile. La table Ventes porte les montants.
Les tables Villes, Calendrier et Canaux portent les colonnes d'analyse.
Vous écrivez cette mesure pour ne conserver que le canal.
CA du canal =
CALCULATE (
[CA total],
ALLEXCEPT ( Ventes, Ventes[Canal] )
)Vous attendez la suppression des seuls filtres posés sur les colonnes de Ventes.
Le moteur fait beaucoup plus.
Ce que la table étendue change
ALLEXCEPT agit sur la table étendue de Ventes.
Cette table virtuelle contient aussi les colonnes de toutes les tables liées.
Le lien doit être une relation régulière plusieurs-à-un. C'est le cas d'un schéma en étoile.
Les filtres de Villes, de Calendrier et de Canaux disparaissent donc également.
Le segment de région posé par votre lecteur s'évapore sans aucun avertissement.
Ce comportement vaut aussi pour ALL et REMOVEFILTERS appliqués à une table entière.
Viser la table de dimension
La règle tient en une phrase.
Appliquez ALLEXCEPT à la table qui porte réellement les colonnes d'analyse.
CA de la région =
CALCULATE (
[CA total],
ALLEXCEPT ( Villes, Villes[Région] )
)La table Villes se trouve du côté un de la relation.
Sa table étendue se limite à elle-même. Le périmètre supprimé redevient prévisible.
L'expansion ne joue pas sur les relations limitées, comme une cardinalité plusieurs-à-plusieurs.
Les limites à connaître
Ce que la fonction refuse
Le premier argument doit être une table de base, jamais une expression.
Les arguments suivants doivent être des colonnes de base.
Au moins une colonne est obligatoire.
Employée seule, elle renvoie une table. Dans CALCULATE, elle modifie le contexte.
ALLEXCEPT renvoie une table. Elle peut donc alimenter un itérateur, contrairement à REMOVEFILTERS.
Contextes non pris en charge
Trois restrictions méritent d'être retenues.
Contexte | ALLEXCEPT |
Calcul visuel | Non prise en charge |
Colonne calculée en DirectQuery | Non prise en charge |
Règle de sécurité au niveau des lignes en DirectQuery | Non prise en charge |
La première ligne distingue ALLEXCEPT de ALL et de REMOVEFILTERS.
Ces deux dernières sont acceptées dans les calculs visuels. Pas ALLEXCEPT.
La documentation Microsoft confirme ces trois restrictions.
Ce qu'il faut retenir
ALLEXCEPT cite les colonnes à conserver, pas celles à supprimer.
Son cas d'usage principal est le pourcentage à l'intérieur d'un groupe.
La fonction conserve un filtre existant. Elle n'en crée jamais.
Sans filtre sur la colonne citée, le calcul retombe sur le total général.
La suppression porte sur la table étendue, donc sur les tables de dimension liées.
Visez la table de dimension, ou préférez REMOVEFILTERS avec VALUES.
Le classeur d'exercices permet de vérifier chacun de ces points.
Si le mécanisme du contexte de filtre ne vous parle pas encore, relisez notre article sur la fonction CALCULATE.
[[ LIENS INTERNES à ajouter après publication : ALL, KEEPFILTERS, DIVIDE ]]
