CREER UNE TABLE AVEC GENERATE – PERFECTIONNEMENT DAX

Le langage DAX est à la fois simple à prendre en main mais peut être très complexe à optimiser. Aujourd’hui, je vais me pencher sur un cas très courant notamment dans le domaine de l’industrie et des ressources humaines : comment calculer mon en-cours (ou mon effectif) à partir d’une date de début et d’une date de fin ?

Solution n°1 : utiliser CALCULATE et ALL

Le modèle est simple et très courant :je souhaite connaître le nombre d’en-cours à partir d’une table qui ressemble à ça :

Mon modèle est le suivant :

Je peux alors créer une mesure pour calculer mon en-cours :

En Cours =

VAR minDate = MIN(T_CALENDRIER[JOUR])

VAR maxDate = MAX(T_CALENDRIER[JOUR])

RETURN

CALCULATE(

COUNTROWS(T_PRODUCTION);

T_PRODUCTION[Date cloture]>=minDate;

T_PRODUCTION[Date démarrage]<=maxDate;

ALL(T_CALENDRIER)

)

Explication :

• minDate et maxDate capturent la plus petite et la plus grande date dans le contexte courant
• CALCULATE permet de sortir du contexte courant et d’appliquer des filtres
  • COUNTROWS permet de compter le nombre de ligne, ce sera notre résultat
  • La date de clôture doit être plus grande que minDate
  • La date de démarrage doit être plus petite que maxDate
  • ALL permet de se défaire de la relation avec T_CALENDRIER

Le résultat obtenu est le suivant :

Solution n°2 : créer une table d’en-cours avec GENERATE

La solution n°1 est tout à fait valide et répondra à la plupart des cas rencontrés. Elle est même plus performante sur une machine « classique » que la solution n°2. En revanche, pour un très gros volume de données et sur des serveurs qui peuvent scale, on peut faire mieux.

Dans ce cas, il n’est pas vraiment possible d’optimiser ma mesure DAX. Je vais donc devoir améliorer mon modèle de données. L’idéal serait d’obtenir une ligne pour chaque produit et chaque jour en cours. Je souhaiterai donc réaliser la transformation suivante :

Cette table d’en-cours aura bien plus de lignes que la table d’origine, mais je peux déterminer le nombre d’en-cours avec un simple DISTINCTCOUNT.

Pour créer cette nouvelle table, nous allons écrire du DAX. Sur Power BI, je clique sur « Nouvelle table » et définit la table « T_ENCOUR » grâce à du DAX :

T_ENCOURS =

SELECTCOLUMNS(

    GENERATE(

    T_PRODUCTION;

    DATESBETWEEN(

        T_CALENDRIER[JOUR];

        T_PRODUCTION[Date démarrage];

        T_PRODUCTION[Date cloture]

        )

    );

    « ID« ;T_PRODUCTION[ID];

    « Jour« ;T_CALENDRIER[JOUR];

    « Catégorie« ;T_PRODUCTION[Catégorie]

)

Explications :

• SELECTCOLUMNS permet de choisir et renommer les colonnes qui nous intéressent
• GENERATE génère une nouvelle table à partir de deux tables existantes.
  • T_PRODUCTION est notre table « gauche ». On souhaite en effet générer plusieurs lignes pour chaque ligne de T_PRODUCTION
  • DATESBETWEEN est notre table « droite ». Pour chaque ligne de T_PRODUCTION, on créé x lignes avec la liste des dates entre [Date démarrage] et [Date cloture].

J’obtiens alors bien la table souhaitée, que je peux lier dans mon modèle de données :

Il me suffit alors de créer la mesure suivante :

En Cours 2 = DISTINCTCOUNT(T_ENCOURS[ID])

Et je peux vérifier que j’obtiens bien le même résultat :

Conclusion

A partir d’une question récurrente et simple au premier abord, on se rend compte une nouvelle fois que le langage DAX offre plusieurs solutions intéressantes.

Dans la majorité des cas, une mesure DAX bien écrite répond amplement aux besoins. Mais pour des modèles de données complexes et volumineux, c’est souvent dans l’amélioration du design du modèle que l’on peut maximiser la performance.