Fonctions de table

En bref

Objectifs : regrouper une table avec SUMMARIZE, SUMMARIZECOLUMNS et GROUPBY ; ajouter des colonnes avec ADDCOLUMNS ; générer des tables (GENERATESERIES, CROSSJOIN, GENERATE, ROW, UNION, CALENDAR) ; utiliser une table virtuelle dans une mesure.
Prérequis : « Itérateurs et variables ».
Niveau : intermédiaire à avancé.

Pourquoi ?

Certaines questions portent sur des groupes plutôt que sur des lignes : la moyenne des ventes par couleur, le meilleur pays, les deux meilleurs produits de chaque pays, les couples pays et produit sans vente. Les fonctions de table construisent ces groupes. Leur résultat s'utilise de deux façons : comme table calculée du modèle, ou comme table virtuelle à l'intérieur d'une mesure, parcourue par un itérateur (AVERAGEX, MAXX, SUMX, COUNTROWS).

Notions clés

Table calculée ou table virtuelle ?

Point	Table calculée	Table virtuelle dans une mesure
Où	Modélisation > Nouvelle table	À l'intérieur d'une formule de mesure
Quand	À l'actualisation du modèle	À chaque affichage, pour chaque cellule
Réagit aux segments	Non : elle est figée	Oui
Usage type	Calendrier, paramètre, table de synthèse, table de correspondance	Moyenne par groupe, meilleur élément, Top N

Fonctions de regroupement (PBIP FonctionsRegroupement)

Fonction	Rôle
SUMMARIZE	Une ligne par combinaison distincte des colonnes de regroupement présente dans la table de départ
ADDCOLUMNS	Ajoute des colonnes calculées à une table, ligne par ligne
SUMMARIZECOLUMNS	Regroupe et calcule en une seule fonction ; supprime les lignes dont toutes les expressions sont vides
GROUPBY et CURRENTGROUP	Regroupe, puis agrège chaque groupe avec un itérateur appliqué à CURRENTGROUP ()

Correction par rapport à l'ancienne version du support : GROUPBY ne remplace pas SUMMARIZECOLUMNS. Les deux fonctions coexistent. SUMMARIZECOLUMNS est la fonction que Power BI utilise pour interroger le modèle à chaque visuel ; GROUPBY sert surtout à agréger une table construite dans la formule, et n'accepte que des itérateurs en X sur CURRENTGROUP (), sans transition de contexte, donc sans mesure.

Fonctions de génération (PBIP FonctionsGeneration)

Fonction	Rôle
GENERATESERIES	Suite de valeurs d'une colonne Value, base des paramètres de simulation
CROSSJOIN	Toutes les combinaisons des lignes de plusieurs tables (produit cartésien)
GENERATE	Évalue une 2e table pour chaque ligne de la 1re, puis assemble les résultats
ROW	Table d'une seule ligne, une colonne par paire nom et expression
UNION	Empile des tables qui ont le même nombre de colonnes
CALENDAR	Table de dates continue entre deux bornes

D'autres fonctions de table reviennent souvent : VALUES et DISTINCT (valeurs distinctes d'une colonne), SELECTCOLUMNS (choisir et renommer des colonnes), TOPN (les N premières lignes selon une expression), EXCEPT et INTERSECT (différence et intersection de deux tables).

Exemple

FonctionsRegroupement.zip53 KB⬇

FonctionsGeneration.zip56 KB⬇

SUMMARIZE puis un itérateur

Nb couleurs vendues = COUNTROWS ( SUMMARIZE ( Ventes, Produit[Couleur] ) )

Moyenne par couleur = AVERAGEX ( SUMMARIZE ( Ventes, Produit[Couleur] ), [Montant vente] )

SUMMARIZE renvoie les couleurs présentes dans les ventes visibles (les colonnes peuvent venir d'une table liée) ; AVERAGEX calcule le montant de chaque couleur, puis la moyenne. La table calculée SyntheseCouleur liste les couples catégorie et couleur vendus :

SyntheseCouleur = SUMMARIZE ( Ventes, Produit[Categorie], Produit[Couleur] )

ADDCOLUMNS : ajouter un calcul à chaque ligne

Montant du meilleur pays =
MAXX ( ADDCOLUMNS ( VALUES ( Geographie[Pays] ), "@Montant", [Montant vente] ), [@Montant] )

Bonne pratique : regroupez avec SUMMARIZE ou VALUES, et ajoutez les calculs avec ADDCOLUMNS plutôt que dans SUMMARIZE (performances et résultats plus prévisibles). Dans ADDCOLUMNS, une mesure déclenche la transition de contexte ; une expression nue doit être entourée de CALCULATE.

SUMMARIZECOLUMNS : regrouper et calculer

SynthesePaysCategorie =
SUMMARIZECOLUMNS (
    Geographie[Pays],
    Produit[Categorie],
    "Montant", [Montant vente],
    "Nombre de ventes", COUNTROWS ( Ventes )
)

Une ligne par couple pays et catégorie ayant des ventes. Dans le PBIP, la matrice du bas calcule la même chose avec des mesures, qui, elles, suivent les filtres. SUMMARIZECOLUMNS est aussi la fonction de base des requêtes dans la vue de requête DAX et DAX Studio.

[Capture : PBIP FonctionsRegroupement, page SUMMARIZECOLUMNS, tableau de la table SynthesePaysCategorie et matrice équivalente à base de mesures]

GROUPBY et CURRENTGROUP

GroupbyCategorie =
GROUPBY (
    Ventes,
    Produit[Categorie],
    "Plus grosse vente", MAXX ( CURRENTGROUP (), Ventes[Montant] ),
    "Nombre de ventes", COUNTX ( CURRENTGROUP (), Ventes[Montant] )
)

Moyenne des plus grosses ventes par pays =
AVERAGEX (
    GROUPBY ( Ventes, Geographie[Pays], "@Max", MAXX ( CURRENTGROUP (), Ventes[Montant] ) ),
    [@Max]
)

GROUPBY crée une ligne par pays avec sa plus grosse vente ; AVERAGEX fait la moyenne de ces maximums.

GENERATESERIES : un paramètre de simulation

ParamRemise = GENERATESERIES ( 0, 0.3, 0.05 )

Remise choisie = SELECTEDVALUE ( ParamRemise[Value], 0 )

Montant après remise = [Montant vente] * ( 1 - [Remise choisie] )

C'est la table que Power BI crée pour un paramètre de simulation (Modélisation > Nouveau paramètre). Elle n'est reliée à rien : la sélection du segment est lue par SELECTEDVALUE.

CROSSJOIN : trouver les combinaisons absentes

Couples pays-produit sans vente =
COUNTROWS (
    FILTER (
        CROSSJOIN ( VALUES ( Geographie[Pays] ), VALUES ( Produit[ProduitID] ) ),
        ISBLANK ( [Montant vente] )
    )
)

6 pays x 22 produits donnent 132 couples ; FILTER ne garde que ceux dont le montant est vide. La table calculée PaysCategorie croise les 6 pays et les 4 catégories (24 lignes) :

PaysCategorie = CROSSJOIN ( VALUES ( Geographie[Pays] ), VALUES ( Produit[Categorie] ) )

GENERATE et TOPN : les 2 meilleurs produits par pays

Top2ProduitsPays =
GENERATE (
    VALUES ( Geographie[Pays] ),
    TOPN ( 2, ADDCOLUMNS ( VALUES ( Produit[Produit] ), "Montant", [Montant vente] ), [Montant], DESC )
)

Montant top 2 produits = SUMX ( TOPN ( 2, VALUES ( Produit[Produit] ), [Montant vente] ), [Montant vente] )

Pour chaque pays, la 2e table est évaluée avec le pays en cours (comme un CROSS APPLY en SQL ; GENERATEALL garde aussi les lignes sans résultat). La table calculée est figée ; la mesure fait le même calcul en suivant les filtres.

[Capture : PBIP FonctionsGeneration, page GENERATE, tableau de la table Top2ProduitsPays et matrice avec Montant top 2 produits par pays]

ROW, UNION et CALENDAR

Indicateurs =
ROW (
    "Montant total", [Montant vente],
    "Nombre de ventes", COUNTROWS ( Ventes ),
    "Panier moyen calculé", DIVIDE ( [Montant vente], COUNTROWS ( Ventes ) )
)

PaysContinents =
UNION (
    SELECTCOLUMNS ( Geographie, "Libelle", Geographie[Pays], "Niveau", "Pays" ),
    SELECTCOLUMNS ( DISTINCT ( Geographie[Continent] ), "Libelle", Geographie[Continent], "Niveau", "Continent" )
)

Calendrier =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Annee", YEAR ( [Date] ),
    "NumMois", MONTH ( [Date] ),
    "Mois", FORMAT ( [Date], "mmmm" )
)

ROW donne une ligne et trois colonnes, calculées une fois pour toutes à l'actualisation.
UNION associe les colonnes par position et conserve les doublons (entourez de DISTINCT pour les retirer) ; SELECTCOLUMNS aligne les colonnes avant l'union. Le résultat compte 10 lignes : 6 pays et 4 continents.
CALENDAR crée 731 dates sans trou ; CALENDARAUTO déduit les bornes des dates du modèle. Marquez ensuite la table comme table de dates (voir « Time intelligence »).

[Capture : PBIP FonctionsGeneration, page CALENDAR, tableau de la table Calendrier et matrice Nb jours par pays]

Pièges et bonnes pratiques

Une table calculée ne réagit pas aux segments. Si le résultat doit suivre les filtres, écrivez une mesure qui construit la table virtuelle.
Auto-exist : SUMMARIZECOLUMNS ne renvoie que les combinaisons qui existent quand plusieurs colonnes d'une même table sont filtrées, ce qui peut surprendre dans certains calculs (voir l'article SQLBI ci-dessous).
ADDCOLUMNS et CALCULATE : dans ADDCOLUMNS, une expression nue comme SUM ( Ventes[Montant] ) ne voit que le contexte de ligne ; utilisez une mesure ou CALCULATE.
CROSSJOIN sur de grandes tables : le nombre de lignes est le produit des nombres de lignes ; partez de VALUES de colonnes de dimension.
Testez vos tables virtuelles dans la vue de requête DAX avec EVALUATE avant de les intégrer à une mesure.

À retenir

Une fonction de table renvoie une table : table calculée figée, ou table virtuelle parcourue par un itérateur dans une mesure.
SUMMARIZE regroupe, ADDCOLUMNS ajoute des calculs, SUMMARIZECOLUMNS fait les deux.
GROUPBY agrège avec CURRENTGROUP et des itérateurs en X ; il ne remplace pas SUMMARIZECOLUMNS.
GENERATESERIES, CROSSJOIN, GENERATE, ROW, UNION et CALENDAR génèrent des tables.

Pour aller plus loin

Source (Microsoft Learn, fonctions de manipulation de tables)
Source (Microsoft Learn, SUMMARIZECOLUMNS)
Source (Microsoft Learn, GROUPBY)
Source (Microsoft Learn, GENERATE)
Source (Microsoft Learn, tables calculées)
Source (Microsoft Learn, paramètres de simulation)

Exercice

Mise en pratique DAX, thème 4 | Classements et Top N (TOPN, Top 5 et Autres avec EXCEPT) et thème 3 | Parts et contributions, question 3.5 (Pareto avec ADDCOLUMNS et FILTER).