Maîtriser COUNTIF() dans Power BI : une approche basée sur DAX

La fonction COUNTIF() est très utilisée dans Excel car elle permet de compter des données selon une condition. Cependant, en passant à Power BI, de nombreux utilisateurs constatent que Power BI ne propose pas de fonction COUNTIF() native.

Heureusement, vous pouvez reproduire le comportement de COUNTIF() grâce à différentes formules DAX (Data Analysis Expressions). Cet article vous guide pour obtenir des calculs Power BI de type COUNTIF() à l’aide de fonctions DAX telles que CALCULATE(), COUNTROWS(), COUNTA() et FILTER().

Pourquoi COUNTIF() n’existe pas dans Power BI

Contrairement à Excel, fondé sur des formules cellule par cellule, Power BI repose sur des calculs tabulaires et le contexte de filtre. Les calculs s’appliquent aux tables et à leurs relations, en s’appuyant sur DAX.

Au lieu de référencer des cellules individuelles, DAX travaille sur des colonnes et applique dynamiquement les calculs selon le contexte de filtre, défini par des segmentations, des filtres de lignes/colonnes et les relations entre tables.

COUNTIF() d’Excel utilise une approche orientée cellules, où les formules s’appliquent ligne par ligne sur une plage. L’approche Power BI est différente : elle exploite les relations entre tables pour affiner les calculs. Plutôt que de balayer des cellules, elle applique des filtres selon le contexte du rapport à l’aide de DAX.

Implications pour les utilisateurs

En venant d’Excel, vous devez adopter l’approche tabulaire de Power BI. Les méthodes DAX, qui opèrent sur les colonnes, sont plus efficaces que des itérations ligne à ligne.

Contrairement à Excel, les utilisateurs Power BI doivent choisir la méthode la plus adaptée pour reproduire COUNTIF() selon leur modèle de données. Il faut donc abandonner le réflexe d’appliquer des formules cellule par cellule et raisonner en tables et en contextes de filtre.

Fonctions DAX essentielles pour reproduire la logique de COUNTIF()

Votre manière d’implémenter COUNTIF() dans Power BI dépend de facteurs tels que les relations entre tables, la présence de valeurs manquantes, la volumétrie, etc. Certaines fonctions DAX sont mieux optimisées pour des traitements plus rapides et plus efficients.

Aperçu des fonctions clés

Les fonctions DAX servent à créer des colonnes calculées, des mesures et des tables. Voici celles qui vous aideront à reproduire la fonctionnalité de COUNTIF() dans Power BI.

• CALCULATE() : modifie le contexte de filtre d’une expression et permet d’appliquer ou d’ajuster des filtres dynamiquement. Par exemple, calculer le total des ventes pour une région donnée.

• COUNTROWS() : compte le nombre de lignes d’une table et peut se combiner avec des fonctions de filtrage pour ne compter que certaines lignes. Exemple : compter les commandes supérieures à 1 000 $.

• COUNTA() : compte les valeurs non vides d’une colonne, utile en présence de données manquantes. Exemple : compter les identifiants clients non vides pour ne prendre en compte que les enregistrements valides.

• FILTER() : souvent utilisée avec CALCULATE() et COUNTROWS(), renvoie une table selon une condition. Exemple : renvoyer les commandes de plus de 500 $.

• ALLEXCEPT() : supprime tous les filtres appliqués sauf ceux explicitement conservés ; par exemple, afficher le total des ventes par région même si des segmentations ou filtres sont en place. Indispensable pour les agrégations sur des hiérarchies.

• RELATEDTABLE() : renvoie une table selon les relations existantes. Exemple : compter le nombre de commandes par client via la relation avec la table clients.

Comment elles interagissent

Puisque COUNTROWS() compte les lignes d’une table, vous pouvez d’abord renvoyer une table répondant à une condition avec FILTER(). Ensuite, utilisez CALCULATE() pour modifier le contexte de filtre afin que COUNTROWS() ne considère que les lignes répondant à la condition. Voici un exemple de syntaxe DAX illustrant ce mécanisme.

Sales over 5 quantities = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER('Sales, 'Sales[Quantity] > 5)
)

La fonction CALCULATE() revient en quelque sorte à combiner deux expressions : au lieu de laisser COUNTROWS() compter toutes les lignes, CALCULATE() restreint le calcul au contexte de filtre défini par FILTER().

Exemples simples d’équivalents à COUNTIF() dans Power BI

Avant de passer aux exemples d’implémentation de COUNTIF() dans Power BI, veillez à charger les données de ventes de supermarché.

Exemple 1 : nombre de commandes passées par des clientes

La syntaxe DAX ci-dessous calcule le nombre de commandes passées par des clientes.

Orders by female customers = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER(Sales, Sales[Gender] = "Female")
)

Nombre de commandes passées par des clientes. Image de l’auteur.

• FILTER(Sales, Sales[Gender] = "Female") filtre les commandes passées par des clientes.

• COUNTROWS(Sales), au lieu de compter toutes les données, compte la table filtrée grâce à l’effet de CALCULATE().

Exemple 2 : compter les commandes par ville

Voici une syntaxe DAX pour compter le nombre de commandes par ville.

Total Orders by City = CALCULATE(COUNTA(Sales[City]), ALLEXCEPT(Sales, Sales[City]))

Nombre de commandes par ville. Image de l’auteur.

• COUNTA(Sales[City]) compte le nombre de valeurs non vides dans la colonne City.

• ALLEXCEPT(Sales, Sales[City]) supprime tous les filtres sauf la colonne City de la table Sales.

• CALCULATE() garantit que COUNTA() est évaluée séparément pour chaque ville, à la manière d’un COUNTIF() dans Excel.

Exemple 3 : méthode alternative avec COUNTROWS

Autre option : utiliser la syntaxe DAX ci-dessous, qui emploie COUNTROWS() au lieu de COUNTA().

Total Orders by City = CALCULATE(COUNTROWS(Sales), ALLEXCEPT(Sales, Sales[City]))

COUNTROWS() compte le nombre de lignes de la table Sales indépendamment de la présence de valeurs vides.

Si la colonne City est complète, les deux approches renverront le même résultat. En revanche, s’il y a des valeurs manquantes dans City, COUNTA() les exclut tandis que COUNTROWS() les inclut.

Utilisez COUNTROWS() lorsque chaque ligne représente un enregistrement (p. ex. une commande). Mais si vos données contiennent des entrées invalides, préférez COUNTA(), qui ne compte que les enregistrements avec une valeur valide.

Utiliser des mesures pour des calculs dynamiques

Certaines situations nécessitent de créer des mesures qui s’adaptent dynamiquement selon les filtres ou segmentations. Cette section explique comment implémenter COUNTIF() dans ces cas.

Scénario : comptage des commandes à forte valeur

Voici un exemple où l’on souhaite connaître les commandes dont la quantité totale est supérieure à cinq.

High Value Orders = COUNTROWS(FILTER(Sales, Sales[Quantity] > 5))

Nombre de commandes à forte valeur. Image de l’auteur.

• FILTER(Sales, Sales[Quantity] > 5) renvoie les commandes dont la quantité est supérieure à cinq.

• COUNTROWS() compte les lignes de la table filtrée (quantité > 5).

Contrairement aux colonnes calculées qui, une fois créées, augmentent la taille du modèle, les mesures sont calculées à la volée et sont donc plus efficientes. La syntaxe DAX pour créer une colonne calculée serait :

 High Value = IF(Sales[Quantity] > 5, 1, 0)

Colonne calculée High Value. Image de l’auteur.

Cette colonne stockerait un 1 ou 0 statique à chaque ligne. Pour compter les commandes à forte valeur, vous devrez créer une mesure avec SUM(Sales[High Value]).

Imaginez créer des colonnes calculées pour chaque filtre : cela gonflerait le stockage et réduirait la flexibilité.

Voici un tableau qui résume pourquoi privilégier les mesures plutôt que les colonnes calculées.

Techniques avancées et conditions multiples

La fonction Excel COUNTIFS() gère plusieurs conditions ; vous pouvez reproduire cela dans Power BI.

Critères multiples / logique COUNTIFS()

Voici une syntaxe DAX pour compter les commandes dont la quantité est supérieure à cinq et payées par carte de crédit.

Quantity Above 5 and Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 && Sales[Payment] = "Credit card"
    )
)

Nombre de commandes de quantité > 5 et payées par carte de crédit. Image de l’auteur.

La formule filtre la table Sales pour les lignes où la quantité dépasse 5 et où le moyen de paiement est la carte de crédit. && représente l’opérateur ET, garantissant que seules les commandes répondant aux deux conditions sont comptées.

Si vous vous intéressez aux commandes satisfaisant l’une OU l’autre des conditions, utilisez l’opérateur OU, représenté par || en DAX.

Quantity Above 5 or Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 || Sales[Payment] = "Credit card"
    )
)

Nombre de commandes avec quantité > 5 ou payées par carte de crédit. Image de l’auteur.

Utiliser des opérateurs logiques et des variables

La syntaxe DAX qui réplique COUNTIF() peut devenir complexe. Les variables améliorent la lisibilité, imposent l’ordre d’évaluation souhaité et évitent des recalculs inutiles en mémorisant des valeurs.

Voici un autre exemple : compter les commandes passées par des clientes, soit payées par carte de crédit, soit de quantité supérieure à cinq, en utilisant des variables.

CreditCard Or Above 5 By Female = 
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > 5 || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
RETURN 
    COUNTROWS(FilteredSales)

Nombre de commandes par des clientes, payées par carte de crédit ou de quantité > 5. Image de l’auteur.

Le filtre est stocké dans une variable réutilisée par COUNTROWS(). Voici une autre écriture de la même expression :

CreditCard Or Above 5 By Female = 
VAR MinQuantity = 5
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > MinQuantity || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
VAR Result = COUNTROWS(FilteredSales)
RETURN 
    Result

Ici, la quantité minimale, le filtre et le résultat sont chacun stockés dans des variables. Comme en mathématiques avec la priorité aux parenthèses, DAX évalue d’abord les conditions entre parenthèses.

Dans l’exemple ci-dessus, l’opérateur OU || est évalué avant l’opérateur ET && car il se trouve à l’intérieur des parenthèses : on filtre d’abord les commandes dont la quantité > 5 ou payées par carte, puis on retient uniquement celles passées par des clientes.

COUNTIF() entre tables liées

Si vous travaillez avec plusieurs tables, vous pouvez aussi utiliser COUNTROWS() ou COUNTA() pour compter des enregistrements selon une condition satisfaite dans une autre table via RELATEDTABLE().

La fonction RELATEDTABLE() respecte les relations du modèle (un-à-un, un-à-plusieurs).

Supposons une seconde table de ventes nommée Sales2, basée sur les mêmes données de supermarché, et que Sales et Sales2 soient reliées par Invoice ID.

Relation entre les tables Sales et Sales2. Image de l’auteur.

Vous pouvez utiliser la syntaxe DAX ci-dessous pour compter le nombre de commandes de clients membres, en supposant que Sales n’a pas de colonne Customer type mais que Sales2 l’a.

Orders by members = 
COUNTROWS(FILTER(RELATEDTABLE(Sales2), Sales2[Customer Type] = "Member"))

Nombre de commandes passées par des clients membres. Image de l’auteur.

• RELATEDTABLE(Sales2) filtre automatiquement Sales2 pour ne conserver que les lignes partageant le même Invoice ID que dans Sales.

• La fonction FILTER() restreint ensuite aux commandes « Member », tandis que COUNTROWS() compte la table filtrée.

Cas d’usage et conseils d’intégration

Voici des cas d’usage concrets et des conseils pour implémenter COUNTIF() dans Power BI.

Scénarios réels

Si vous avez une table de résultats d’examens ou de commandes, vous pouvez utiliser COUNTROWS() et FILTER() pour compter les doublons. À partir de la colonne des notes, vous pouvez compter le nombre d’admis ou de non-admis. Un autre équivalent utile à COUNTIF() est SUMX(), pour sommer des scores selon une condition.

Outils d’intégration de données

Plutôt que de téléverser systématiquement votre fichier Excel ou Google Sheets pour l’analyse, vous pouvez automatiser l’extraction des données avec des outils comme Coupler.io.

Dès que votre feuille de calcul est mise à jour, le tableau de bord Power BI se met à jour automatiquement, sans action manuelle.

Power BI reflète ainsi en permanence les données les plus récentes, ce qui vous permet de vous concentrer sur l’analyse et la prise de décision. Ces outils externes facilitent aussi la connexion de multiples sources dans un même tableau de bord.

Conseils pratiques pour lisibilité et performance

Utilisez des colonnes calculées quand le calcul doit s’effectuer au niveau ligne avant agrégation. Préférez une mesure si le résultat dépend des filtres/segmentations choisis par l’utilisateur, ou si vous avez besoin d’une valeur agrégée plutôt que de calculs ligne à ligne.

La compréhension du contexte de filtre est essentielle pour choisir entre mesure et colonne calculée. Le contexte de filtre s’applique avec des segmentations, filtres ou regroupements, contrairement au contexte de ligne, propre à une ligne de table. Les colonnes calculées utilisent le contexte de ligne et peuvent créer des colonnes superflues, augmentant l’empreinte mémoire.

Tenez aussi compte du contexte de filtre lors de la création de mesures. Par exemple, au lieu de SUM(Sales[Amount]), utilisez CALCULATE(SUM(Sales[Amount])) pour maîtriser le filtrage. Si un filtre est déjà appliqué, la première formule peut donner une valeur erronée, tandis que la seconde permet de contrôler les filtres pris en compte.

Évitez également d’utiliser trop d’itérateurs (comme FILTER()) dans une même expression DAX : sur de gros volumes, ces itérations ligne à ligne dégradent les performances.

Conclusion

Même si la fonction COUNTIF() d’Excel n’existe pas nativement dans Power BI, les fonctions DAX comme CALCULATE(), COUNTROWS(), COUNTA() et FILTER() permettent d’en reproduire la logique.

L’approche à retenir dépend de la nature de vos données : en cas de valeurs manquantes, envisagez COUNTA() ; si vos données sont complètes, COUNTROWS() convient. Pour exclure des filtres externes, utilisez ALLEXCEPT() au sein de CALCULATE().

Gardez à l’esprit que la manière dont vous écrivez votre DAX influe sur la vitesse d’affichage des visuels. Rédigez un DAX optimisé et sachez quand utiliser des mesures ou des colonnes calculées. Voici quelques ressources DataCamp pour explorer d’autres techniques DAX avancées.

• Power BI Calculate Tutorial
• Power BI RELATED DAX Function: Introduction and Use Cases
• Mastering SWITCH in DAX for Power BI: A Comprehensive Guide
• How to Use the SUMX Power BI Functions
• DAX SUMMARIZE(): A Guide to Grouping and Summarizing Data
• A Comprehensive Guide to DAX LOOKUPVALUE