DAX


Le  modèle de table paramètre est utile pour segmenter des données, ou pour injecter des paramètres dans des mesures. L’astuce va consister à créer une table déconnectée des autres tables du modèle. Cette table permettra d’offrir une liste de choix permettant d’altérer les calculs des mesures. Par exemple cette table pourra proposer à l’utilisateur un choix parmi différents taux d’inflation.   Modèle de base Supposons que nous souhaitions faire varier les valeurs prévues d’un budget en fonction d’un taux d’inflation prévisionnel. Après avoir importé votre table budget, il vous faudra créer une table Taux d’inflation, déconnectée. Nous allons devoir capter la valeur sélectionnée par l’utilisateur pour l’intégrer en tant que paramètre de notre mesure [CA prévu avec inflation]. Pour ce faire, nous allons utiliser la fonction SELECTEDVALUE() dans une mesure [Taux], qui permettra de récupérer la valeur sélectionnée par l’utilisateur. Cette valeur doit être unique (si l’utilisateur sélectionne plusieurs taux, la mesure retournera (Vide).   Maintenant que nous savons récupérer la sélection, nous allons pouvoir créer notre mesure [CA prévu avec inflation]. Cas d’usage Comme nous venons de le voir, ce modèle permet d’injecter autant de paramètres que nécessaire dans une ou plusieurs mesures, ce qui permet de créer de véritables algorithmes […]

DAX-Modèles Table paramètre



Le modèle de total cumulé permet de réaliser des sommes à date, particulièrement utiles en gestion. Par exemple, ce modèle pourra être utilisé pour créer une mesure de solde cumulé dans un grand livre, ou un stock à date.   Modèle simple Pour commencer nous allons utiliser un modèle très simple, contenant deux tables jointes par la colonne date : Une table de dates (Calendrier) Une table des mouvements de stocks (Mouvements)   Si maintenant nous créons un rapport comprenant la date et la somme des mouvements, nous obtenons ceci : Dans chaque ligne, la somme des mouvements est limitée à la journée, puisque le champs date agit comme filtre de chaque ligne du rapport. Pour obtenir un total cumulé, nous devons éliminer ce filtre pour le remplacer par: Date de début = Aucune; Date de fin = Date maximale de la ligne en cours. Par exemple ici : La date minimale sera la première date de la table Calendrier; La date maximale sera le 04/01/2019. La mesure adaptée est la suivante : La fonction Filter() permet de modifier le filtre du rapport : Le premier argument est une table (la fonction All() permet de supprimer des filtres existant); Le […]

DAX-Modèles Total cumulé



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