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) :

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

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 :

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 :

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) :

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 :

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

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]
)