SUMMARIZECOLUMNS : la super-fonction du DAX

Proposé par André Meyer-Roussilhon

20 septembre 2021

Dax

Restez informé, abonnez-vous à la newsletter (2 fois / mois)

1 + 14 =

SUMMARIZECOLUMNS : le couteau suisse du DAX

SUMMARIZECOLUMNS, c’est l’intégration de trois fonctions : SUMMARIZE, qui sert à créer une table, ADDCOLUMNS, qui permet d’y ajouter de nouvelles colonnes (le plus souvent des indicateurs, sommes, nombres, etc.), et FILTER (ou CALCULATETABLE), pour filtrer les données.

Ces trois actions sont la base d’une bonne partie du DAX : SUMMARIZECOLUMNS permet de les effectuer de manière à la fois simple et particulièrement performante. Notez bien que SUMMARIZECOLUMNS sert à créer une table stockée dans le modèle de données : nous verrons les limites de cette fonction à la fin de cet article.

De manière globale, la syntaxe de SUMMARIZECOLUMNS est la suivante :

SUMMARIZECOLUMNS ( 
  <GroupBy_ColumnNames> ,        // les colonnes de groupement (dimensions)
  <FilterTable> ,		 // les filtres
  <Name> , <Expression> 	 // les indicateurs
)

Ainsi, par exemple, le script suivant permet-il de créer une table avec la catégorie, l’année, et la quantité vendue, uniquement pour les catégories Littérature et Bande dessinée :

SUMMARIZECOLUMNS(
    'Catégories'[Libellé Catégorie],
    'Table Dates'[Année],
    FILTER(
        'Catégories',
        'Catégories'[Libellé Catégorie] IN {"Littérature ","Bande dessinée"}
    ),
    "Qté Cat-Année", [qté]
)

Créer une table avec SUMMARIZECOLUMNS (avec ou sans filtre)

Dans le détail , voici différents cas d’utilisation de SUMMARIZECOLUMNS.

Commençons par une table contenant toutes les combinaisons possibles d’un ensemble de colonnes :

Vous constatez ici que seul le premier argument de la fonction (les colonnes de groupement) est renseigné. Il s’agit bien des combinaisons possibles théoriquement : dans notre modèle, il n’y a pas de ventes en 2020 et 2021.

Rajoutez un indicateur et la table affiche toutes les combinaisons réellement actives du modèle (celles pour lesquelles il y a eu des ventes) :

summarizecolumns
La catégorie Art n’est plus présente qu’en 2018

Enfin ajoutons un filtre pour ne conserver que quelques catégories :

summarizecolumns

Il est très important de noter que le filtre doit être une table (FILTER retourne bien une table), et ne peut pas être un filtre booléen (Vrai/Faux).

SUMMARIZECOLUMNS et TREATAS

Une autre façon d’écrire le code précédent, plus propre et plus performante, consiste à définir le filtre à l’aide de TREATAS (qui retourne une table d’une ou plusieurs colonnes). Cette fonction peu connue est pourtant très efficace :

summarizecolumns

Avec SUMMARIZECOLUMNS et TREATAS, vous avez un code redoutablement puissant pour générer vos tables.

SUMMARIZECOLUMNS et ADDMISSINGITEMS : ajouter des lignes manquantes

Mais revenons un instant sur le cas développé au tout début de l’article : une autre fonction DAX peu connue, ADDMISSINGITEMS, permet d’ajouter dans la table les lignes qui ne sont pas générées à cause de l’absence de transactions (cela permet d’avoir une information utile : pour quelles années ou quelles catégories n’ai-je aucune ventes ?).

Dans le premier exemple, je veux avoir toutes les années même s’il n’y a pas eu de transactions :

summarizecolumns

Notez la syntaxe de la fonction, où la colonne [Année] doit être mentionnée en premier et troisième argument. Le second argument est la table qu’il s’agit de compléter.

Autre exemple, cette fois avec les deux colonnes (vue ici dans la vue Données) :

summarizecolumns
Toutes les combinaisons possibles apparaissent, et pour certaines, il y a des quantités vendues

Attention, si vous ajoutez la quantité au visuel, certaines lignes disparaissent :

Pour éviter cela, vous pouvez modifier légèrement le code en ajoutant +0 à votre indicateur :

ADDMISSINGITEMS(
    'Table Dates'[Année],
    'Catégories'[Libellé Catégorie],
    SUMMARIZECOLUMNS(
        'Table Dates'[Année],
        'Catégories'[Libellé Catégorie]
        , "qté Cat-Année", [qté] +0
    ),
    'Table Dates'[Année],
    'Catégories'[Libellé Catégorie]
)

Les limites de SUMMARIZECOLUMNS

Je le répète : SUMMARIZECOLUMNS sert à créer une table stockée physiquement dans le modèle de données.

Sa grande limite, c’est que cette fonction ne peut en aucun cas être utilisée dans la définition d’une mesure. Regardons un exemple concret de ce point :

summarizecolumns

L’utilisation de SUMMARIZECOLUMNS dans la définition d’une mesure génère une erreur lorsque vous cherchez à afficher cette mesure :

summarizecolumns

La même erreur se produit si vous mettez SUMMARIZECOLUMNS en variable :

moyenne qté cat-année ERREUR = 
VAR agregat = 
    SUMMARIZECOLUMNS(
        'Catégories'[Libellé Catégorie],
       'Table Dates'[Année],
       TREATAS({"Littérature","Arts divinatoires","Bande dessinée"}, 'Catégories'[Libellé Catégorie]),
       "Qté Cat-Année", [qté]
    )
RETURN 
AVERAGEX(
    agregat,       
    [Qté Cat-Année]
)

Dans cette situation, vous devrez revenir à l’écriture moins compacte SUMMARIZE et ADDCOLUMNS.

En revanche, vous pouvez tout à fait appeler une table créée à l’aide de SUMMARIZECOLUMNS pour définir votre mesure :

moyenne qté cat-année = 
AVERAGEX(
    'smc3_ agrégat 3catégorie-année-qté TREATAS', // table définie avec SUMMARIZECOLUMNS
    'smc3_ agrégat 3catégorie-année-qté TREATAS'[Qté Cat-Année]
)

Articles associés :

Créer un graphique à base zéro pour suivre l’évolution

Créer un graphique à base zéro pour suivre l’évolution

Dans un graphique à base zéro, le premier point sert de référence (le point zéro), et tous les autres sont calculés en pourcentage de variation par rapport à cette référence. Ce type de graphique est utilisé par exemple dans le domaine financier, pour suivre l’évolution du cours d’une action – mais il peut servir pour suivre l’évolution de tout type de donnée.
Le graphique est intimidant, mais sa réalisation est en fait très facile, et repose sur des formules DAX étonnamment simples.

Délai entre deux dates, week-end et jours fériés

Délai entre deux dates, week-end et jours fériés

Une table du temps qui calcule automatiquement les jours fériés ? Vous en rêviez, daxone.fr vous l’offre ! Et j’en profite pour vous donner la méthode pour calculer le nombre de jours écoulés entre deux dates, en excluant les dimanches et les jours fériés (je suis sûr que ça va vous servir)

Comprendre le message d’erreur « Dépendance circulaire détectée » et le corriger

Comprendre le message d’erreur « Dépendance circulaire détectée » et le corriger

C’est une erreur courante, et pas seulement lorsque l’on débute avec Power BI. Le message « Une dépendance circulaire a été détectée » repose sur les ressorts fondamentaux du DAX, et implique CALCULATE, la transition de contexte, les mesures et les colonnes, ou encore les filtres. A travers cette erreur, nous allons passer en revue quatre règles fondamentales du DAX.