DAX


La plupart des modèles de données contiennent plusieurs tables en relation les unes avec les autres.   Nous allons étudier l’impact de ces relations sur les deux contextes.   Contexte de ligne et relations.   C’est le plus simple à comprendre : il n’y a pas d’interactions.   Supposons une table des Ventes en relation avec une table Articles.   Si l’on crée une colonne visant à calculer la différence entre le prix  unitaire réellement appliqué et le prix de vente standard, prévu dans la table articles :   Différence= Ventes[PU Vente]-Articles[PU Vente]   Retournera une erreur.   En effet, la colonne calculée crée une itération sur la table Ventes et elle seule !   Pour accéder à la valeur en relation dans la table Articles il faudra utiliser la fonction RELATED.   Différence= Ventes[PU Vente]-RELATED(Articles[PU Vente])   Dans le sens inverse (colonne calculée dans la table Articles), il faudra utiliser RELATEDTABLE imbriquée dans une fonction d’aggrégation :   AVERAGE(RELATEDTABLE(Ventes[PU Vente]))   Ce mécanisme fonctionne, quel que soit le nombre de niveaux de la relation (Par exemple Famille->Sous-Famille->Articles->Ventes), sous réserve que les relations soient du même type et de même direction.   Par exemple, dans le cas ci-dessous, il n’est […]

DAX Contexte d’évaluation (Partie 2)



3
Toute mesure est évaluée dans un contexte.   Le contexte, c’est-à-dire l’ensemble des filtres du rapport (lignes et colonnes d’un TCD, slicers..) pour le contexte de filtres, ou la ligne de la table, pour un contexte de ligne , détermine l’environnement dans lequel s’applique la formule de calcul.   Un élément clé à retenir : « Il y a toujours deux contextes qui coexistent : Le contexte de filtres et le contexte de ligne. »   Contexte de filtre. Prenons une mesure simple comme : Sum(Ventes[Total Ventes HT]) Elle retourne la somme des quantités de toute la table Ventes, en l’absence de filtres:   Elle retourne une valeur différente si l’on ajoute un filtre sur l’année dans le rapport :     Comment cela se produit-il ?   Il faut considérer chaque cellule : Pour déterminer sa valeur, la table Ventes est filtrée comme dans un tableur Excel, pour ne retenir que les lignes concernant l’année 2017 (première ligne du rapport.) Ensuite, l’expression s’applique sur les valeurs contenues dans la colonne Total Ventes HT (filtrées.)   Nous pouvons rajouter autant de filtres que nécessaire, la logique sera toujours la même : la table est filtrée en fonction du contexte d’évaluation de chaque cellule, avant […]

DAX – Contexte d’évaluation (Partie 1)



1
Historiquement nous utilisions la fonction SUMMARIZE() pour créer une table agrégée, en d’autres termes pour créer une table ressemblant aux résultats d’un tableau croisé dynamique, sur laquelle de nouveaux calculs pourraient être appliqués. Cette fonction prend une table en premier argument (la table dont les valeurs de certaines colonnes seront agrégées), puis les colonnes à agréger (ces colonnes peuvent provenir de la table ou de toute table liée à cette dernière par une relation un à plusieurs). Exemple : Si nous avons deux tables : ‘Produits’ et ‘Ventes’, nous mettrons la Table vente (côté plusieurs) en premier argument de la fonction, puis ajouterons les colonnes dont les valeurs identiques doivent être agrégées, avant de finir par la formule de calcul (Nb : ne pas oublier de préciser un nom pour cette nouvelle colonne, ici ‘Quantités vendues’) Quantités vendues par couleur et par fournisseur = SUMMARIZE( ‘ventes’; ‘Produits'[Couleur]; ‘Produits'[Fournisseur]; « Quantités vendues »; SUM(‘Ventes'[Quantité]) )   Pour des questions de performance et de compatibilité, il est préférable de n’utiliser SUMMARIZE(), que pour les colonnes d’agrégat, et pas pour la formule de calcul, cette dernière devant être ajoutée par la formule ADDCOLUMNS() :   ADDCOLUMNS( SUMMARIZE( ‘ventes’; ‘Produits'[Couleur]; ‘Produits'[Fournisseur]; ) ; « Quantités vendues »; CALCULATE(SUM(‘Ventes'[Quantité])) ) […]

Tables agrégées : du bon usage de SUMMARIZECOLUMNS()



La fonction IF() est bien connue des utilisateurs d’Excel. Elle permet de tester une condition et de retourner une valeur, dans le cas ou la condition est remplie, ou une autre valeur, dans le cas contraire. Anatomie de la fonction : IF(Test logique,valeur si Vrai,valeur si Faux) Avec DAX il faut envisager deux emplois de la fonction IF() : Emploi de IF()) dans une colonne calculée C’est le cas le plus simple, car alors IF() fonctionne exactement comme dans Excel. A titre d’exemple créons une nouvelle colonne calculée dans notre Table ‘Gd livre’ Test = IF([Solde]>200;1;0) Deuxième cas : Emploi de IF() dans une mesure. Si nous créons une nouvelle mesure et y collons cette formule, nous recevrons un message d’erreur. Ce message est le résultat de la violation d’une règle sacrée du langage DAX : « Pas de colonnes NUES dans une mesure ». Le seul moyen d’utiliser IF() dans une mesure est de le coupler avec la fonction VALUES(). Le meilleur moyen de comprendre ce que retourne VALUES() est  encore de créer une nouvelle Table. Comme on peut le constater, VALUES() retourne une table reprenant l’ensemble des soldes de ‘Gd livre’. Nous pouvons donc écrire la mesure suivante : Test1 = IF(VALUES(‘Gd […]

DAX – IF()



CALCULATETABLE() permet de créer une table filtrée. Cette table sera le plus souvent virtuelle pour être utilisée dans une mesure. Elle peut aussi être visualisée dans Power Bi pour mieux comprendre le fonctionnement de cette fonction. Par exemple imaginons que nous voulions appliquer un calcul sur les ventes antérieures au 31/12/2015. Il nous suffit de générer une nouvelle table avec CALCULATETABLE() : Table =CALCULATETABLE(‘Gd livre’;’Gd livre'[Compte]=70711000; ‘Gd livre'[Date]<=date(2015;12;31) ) Cette formule nous retourne une Table complète filtrée selon ces critères, sur laquelle nous pouvons pratiquer des calculs, comme par la somme de montants créditeurs. Ventes2015 = SUMX( CALCULATETABLE(‘Gd livre’;’Gd livre'[Compte]=70711000; ‘Gd livre'[Date]<=date(2015;12;31) );[Crédit] ) Lorsque l’on utilise cette mesure dans un tableau, on constate que les filtres du contexte s’appliquent bien, mais seulement sur les données retournées par cette table virtuelle.

DAX – CALCULATETABLE()



La fonction DISTINCTCOUNT() prend une colonne de table en entrée, crée une table virtuelle des valeurs uniques (supprime les doublons), puis compte les lignes de cette table virtuelle avant de retourner la valeur. Par exemple la mesure : Distinct = DISTINCTCOUNT(‘Gd livre'[Compte]) Retournera le nombre de comptes présents dans la table ‘Gd livre’, en fonction du contexte. Comme prévu, la mesure réagit au contexte du tableau, et retourne le nombre de comptes utilisés selon les mois. Cette fonction peut aussi être utilisée en prenant deux tables liées comme paramètres. Par exemple, si nous souhaitons calculer le nombre de catégories de produits mouvementées dans nos stocks, par périodes, nous pouvons créer la mesure suivante : TotalCategoriesMouvementées = CALCULATE( DISTINCTCOUNT(Produits[Catégorie] );’Mouvements de stock’ ) Le fait d’inclure DISTINCTCOUNT() dans une fonction CALCULATE() nous permet d’utiliser la table ‘Mouvements de stocks’ comme filtre de la table mono-colonne Produits[Catégorie] . Le tableau retourne pour chaque jour, le nombre de catégories de produits mouvementés dans les stocks. Si nous ne voulions calculer que les catégories sorties du stock, il nous suffirait de modifier le second paramètre de la fonction, en filtrant la table ‘Mouvements de stock’. TotalCategoriesMouvementées = CALCULATE( DISTINCTCOUNT( Produits[Catégorie] ); FILTER(‘Mouvements de stock’;’Mouvements stock'[Mouvement ]= »S » […]

DAX – DISTINCTCOUNT()



CALCULATE() est certainement l’une des fonctions les plus puissantes et les plus utiles du langage DAX. On peut la comparer à la fonction SUMIF() d’Excel, sans ses limitations. Anatomie de la fonction : =CALCULATE(<aggrégateur>, <filtre1>, <filtre2>, … ) Agrégateurs SUM ([Colonne]) SUM ([Colonne1]) / MIN ([Colonne2]) MIN(),AVERAGE(),SUMX() …Ou une autre mesure définie [Mesure 1]. Filtres Avec CALCULATE() nous pouvons non seulement réaliser n’importe quel calcul, mais nous pouvons appliquer autant de filtres que nous le souhaitons sur les données source ! Par exemple, créons une nouvelle mesure dans notre table ‘Gd livre’. MesureExemple = CALCULATE( SUM(‘Gd livre'[Solde]);’Gd livre'[C.j]= »ACH »; ‘Gd livre'[Racine]=607; ‘Gd livre'[Centre Analytique]= »Centre A » ) Cette mesure calcule le total des soldes des écritures du journal des Achats, pour chaque compte dont la racine est 607, et uniquement pour le Centre analytique « Centre A ». CAlCULATE() + ALL() : Du lourd !! Nous n’avons pas jusqu’à présent rencontré encore  la fonction ALL(). Son utilité est simple à décrire : ALL() neutralise tous les filtres appliqués. Reprenons notre exemple précédent, en transformant la carte en histogramme groupé. Maintenant glissons le champs [MoisNom] dans la case Axe. Notre [mesure1] est maintenant « découpée » par mois, c’est-à-dire qu’en arrière-plan, un nouveau filtre s’applique sur chaque barre : Barre du mois de Janvier : […]

DAX – CALCULATE() – Introduction



Parmi les fonctions statistiques, AVERAGE() et AVERAGEX() sont probablement les plus utilisées. AVERAGE() est la plus simple d’utilisation. Elle permet de calculer la moyenne statistique des valeurs présentes dans une colonne. Par exemple, nous pouvons calculer la moyenne des valeurs de la colonne [Débit] de la table ‘Gd livre’. Average = AVERAGE(‘Gd livre'[Débit]) Comme vous pouvez le constater, chaque moyenne est calculée en fonction du contexte du rapport (ici par compte, année, trimestre, et mois.) Si nous souhaitons effectuer la moyenne des valeurs calculées ligne par ligne (par exemple pour calculer une moyenne mobile) , nous devrons utiliser  la fonction itérateur AVERAGEX().

DAX – AVERAGE()



2
Si vous programmez, vous êtes surement familiers du concept « d’itération ». Pour les autres (c’est-à-dire l’écrasante majorité) il s’agit simplement d’une répétition programmée d’actions. C’est ce que SUMX() (et tous les membres de sa famille) MINX(),MAXX() font : ils répètent une action ligne après ligne. Anatomie de la fonction : SUMX(<Table>, <Expression>) Cela peut se lire de cette manière : Va à la première ligne -> fait un calcul -> Mémorise le résultat -> va à la seconde ligne-> Fait le même calcul -> ajoute le résultat au résultat mémorisé -> etc.. jusqu’à la dernière ligne. Pour illustrer le travail que réalise cette fonction, nous allons chercher à répondre à la question suivante : quelle est la valorisation totale du stock ? Pour y répondre nous ne pouvons pas utiliser la fonction SUM() SUM([qté])*SUM([Pu]) Car le résultat serait faux (ci-dessous l’équivalent du calcul dans Excel) Nous devons effectuer le calcul à la ligne. En tant qu’utilisateur d’Excel nous pensons immédiatement à rajouter une nouvelle colonne calculée à la table ‘Mouvements de Stock’. L’équivalent dans Excel . La somme de cette nouvelle colonne est égale à 110 000, ce qui est la bonne valeur. Si le résultat est exact, la méthode n’est pas la bonne ! L’heure est enfin venue de […]

DAX – SUMX()