Sur Excel, un des outils les plus connus et les plus utilisés est sans doute le tableau croisé dynamique. Cela étant, même cet outil, capable d'une analyse de données poussée en 4 clics, présente ses limites, utilisé sur Excel uniquement. Quelles sont ces limites ? D'abord, un tableau croisé dynamique ne peut avoir qu'une source à la fois, difficile de recroiser plusieurs tableaux. Ensuite, les calculs possibles dans les tableaux sont relativement limités (Somme, Moyenne, Nombre…)

Sur Power BI, nous sommes également dans la même logique : les visuels sont puissants, mais pour en tirer pleinement profit, il va falloir créer des mesures, qui sont donc des calculs personnalisés liés aux colonnes des tables que nous cherchons à analyser. Le langage utilisé pour effectuer ces calculs est le DAX (Data Analysis Expressions).

Comme sur Excel, nous avons des fonctions qui reviennent plus souvent que d'autres, et donc des fonctions plus importantes à connaître que d'autres.

‍

1. Mentions honorables

‍

Elles sont très utiles, très communes, mais pas autant que les fonctions du top 12, les voici :

• COUNTROWS : compte le nombre de lignes dans une table, ce qui a l'air basique, mais se combine très bien avec CALCULATE pour personnaliser le contexte de filtre.
• CALENDAR / CALENDARAUTO : génère un calendrier (liste de dates), donc pas forcément utilisé pour les mesures. Considérant que dans pas mal de rapports, il est question de visualiser des indicateurs dans le temps, elles sont importantes, mais pas primordiales, puisque Power Query peut aussi générer un calendrier (d'ailleurs dans certains cas, c'est conseillé).
• SELECTEDVALUE : Permet de repérer quelle valeur est sélectionnée par un segment, permettant notamment d'avoir plusieurs séries combinées dans la même mesure. Cela étant, elle n'existe pas sur toutes les versions de Power Pivot (Excel), ce qui la relègue du top 12.
• DATESYTD, DATESMTD, DATESQTD et SAMEPERIODLASTYEAR : Ce sont les fonctions qui permettent de contrôler à la perfection les calculs liés au temps.

‍

Les fonctions qui seront citées par la suite ne sont pas classées dans un ordre particulier. À l'exception de CALCULATE, qui est la fonction la plus importante du langage, l'ordre du top reste très ouvert et donc très discutable. Sans plus attendre, allons-y.

‍

2. Les calculs (et du texte)

‍

a. Des fonctions en X (SUMX, AVERAGEX, MAXX, MINX)

J'ai décidé de les mettre toutes les 4 dans la même thématique, puisque leur structure est la même, même si leurs calculs finaux sont différents. Ce qu'elles permettent : effectuer des calculs sur des expressions, ce qui est particulièrement utile quand leurs contreparties n'acceptent que des colonnes. Elles ouvrent donc la porte à des calculs avancés.

‍

b. DIVIDE

Cette fonction est très simple, et en même temps, tellement puissante quand on cherche à diviser des valeurs qui sont calculées sur des colonnes entières, et donc très enclines à produire des erreurs sur les rapports, quand le dénominateur est vide ou égal à 0, ce qui est très vite arrivé. C'est là que la fonction DIVIDE entre en jeu, avec son 3ᵉ argument : le résultat alternatif, qui permet de mettre autre chose, dans le cas d'une erreur.

‍

c. FORMAT

C'est l'équivalent de la fonction TEXTE d'Excel, donc très utile pour contrôler le format d'un nombre ou une date. Couplée à une carte sur Power BI par exemple, elle permet de s'affranchir des formatages complexes qui arrivent par la suite.

‍

3. Les fonctions avec conditions

‍

a. IF

Comme pour à peu près tous les langages, il y a toujours un IF, et il est toujours aussi utile. C'est LA fonction abordable et fiable, qui permet de monter des structures conditionnelles. Sa syntaxe est d'ailleurs la même que pour la fonction SI sur Excel : IF(test logique, valeur si vrai, valeur si faux). Cette fonction est donc utile, facilement maniable, et polyvalente.

‍

b. SWITCH

Si IF est l'équivalent de SI dans Excel, alors SWITCH est celui de SI.MULTIPLE. C'est une fonction qui est donc excellente, dans le cas où on cherche à créer plusieurs tests logiques sur la même valeur, sans passer par l'imbrication à l'ancienne de fonctions SI. La fonction SWITCH se combine d'ailleurs très bien avec la fonction SELECTEDVALUE (dans les mentions honorables), ou alors on peut la détourner en faisant des tests logiques directement à la place des valeurs à tester en remplaçant l'expression à évaluer par un TRUE.

‍

c. FILTER

Cette fonction, comme son nom l'indique, permet de filtrer des tables, ce qui en fait un très beau combo avec la fonction CALCULATE, ainsi que les fonctions en X (MAXX, MINX, SUMX, AVERAGEX…), ce qui permet de préciser les calculs sur une partie d'une table. Cette fonction est très polyvalente. On peut même générer des tables avec.

‍

3. Contrôler les filtres

‍

a. ALL

Cette fonction permet d'effectuer des calculs en ignorant tous les filtres, qu'ils soient inhérents aux filtres ou dans les rapports directement (tableaux croisés dynamiques, graphiques croisés dynamiques sur Excel, le rapport Power BI directement). Très pratique pour calculer des totaux et des sous-totaux.

‍

b. ALLSELECTED

Cette fonction permet d'ignorer les filtres, mais pas complètement : elle ne prend en compte que les filtres appliqués directement sur le rapport/tableau croisé dynamique, mais pas le contexte, à la différence de ALL, qui ne prend en compte aucun filtre ni contexte. Cette fonction est donc très utile pour faire des cumuls, tout en laissant la liberté à l'utilisateur de choisir quelles valeurs garder ou exclure du calcul.

‍

c. ALLEXCEPT

Encore une fonction de la série des ALL, donc toujours dans l'idée de contrôler de plus près les contextes de ligne (plus vulgairement les champs lignes et colonnes du tableau croisé dynamique) ainsi que l'application des filtres et des segments, il y a ALLEXCEPT. Sa particularité par rapport aux autres ? Elle permet de sélectionner les filtres qui impactent le calcul, en ignorant tous les autres.

‍

4. Gérer des tables et des relations

‍

a. VALUES

Cette fonction retourne la liste des valeurs uniques dans une colonne. Cela, en soi, n'est pas vraiment incroyable (utile, bien entendu), mais la puissance de la fonction réside dans le fait qu'elle se combine bien avec notamment CALCULATE, ou encore avec HASONEVALUE, pour pouvoir construire et/ou calculer des résultats en utilisant des filtres dynamiques, basés sur des valeurs uniques.

‍

b. RELATED

Pour faire des calculs qui prennent en compte plusieurs colonnes dans plusieurs tables en même temps, parfois, le lien ne suffit pas, notamment quand la colonne que nous souhaitons utiliser n'est pas directement liée (c'est une autre colonne qui sert de lien). C'est là que la fonction RELATED révèle toute sa puissance : elle permet d'effectuer, en quelque sorte, une recherche par rapport à la correspondance entre la table de faits et la table de dimensions. Par exemple, si j'ai une colonne avec les produits vendus dans ma table de faits, et leurs prix de vente dans la table de dimension, avec le lien fait au niveau du nom du produit, si je cherche à multiplier le nombre de produits par leurs prix respectifs, j'utilise RELATED.

‍

5. La star

‍

CALCULATE

J'ai gardé la plus incontournable pour la fin et pour cause, s'il y a une fonction en DAX à retenir, c'est bien CALCULATE. Cette fonction est la plus puissante du langage : elle permet de contrôler tous les contextes, notamment en la combinant à peu près avec une autre fonction, par exemple : ALL. C'est la fonction la plus polyvalente, dans le sens où, selon la fonction qui y est couplée elle peut (liste non exhaustive) :

• Calculer un total en préfiltrant les données (et donc en circonscrivant le contexte),
• Calculer un total en retirant les filtres,
• Calculer un total sur une temporalité différente (exemple : avec SAMEPERIODLASTYEAR qui va décaler les chiffres d'un an).

‍