Vue d’ensemble de DAX

DAX (Data Analysis Expressions) est un langage d’expression de formule utilisé dans les applications Analysis Services, Power BI et Power Pivot dans Excel. Les formules DAX incluent des fonctions, des opérateurs et des valeurs qui permettent d’effectuer des requêtes et des calculs complexes sur des données de colonnes et tables associées dans des modèles de données tabulaires.

Cet article offre une présentation générale des principaux concepts de DAX. Il décrit DAX tel qu’il s’applique à tous les produits qui l’utilisent. Certaines de ses fonctionnalités peuvent ne pas s’appliquer à des produits ou cas d’usage particuliers. Pour plus d’informations sur l’implémentation de DAX de votre produit, consultez la documentation correspondante.

Calculs

Les formules DAX s’utilisent dans les mesures, les colonnes calculées, les tables calculées et la sécurité au niveau des lignes.

Mesures

Les mesures sont des formules de calcul dynamique dont les résultats changent selon le contexte. Les mesures sont utilisées dans les rapports qui prennent en charge la combinaison et le filtrage des données de modèle à l’aide de plusieurs attributs, comme dans les rapports Power BI ou les tableaux et graphiques croisés dynamiques Excel. Les mesures sont créées au moyen de la barre de formule DAX dans le concepteur de modèle.

Une formule dans une mesure peut utiliser des fonctions d’agrégation standard qui sont créées automatiquement avec une fonction de somme automatique, comme COUNT ou SUM. Vous pouvez également définir votre propre formule à l’aide de la barre de formule DAX. Une mesure nommée peut être passée comme argument à d’autres mesures.

Lorsque vous définissez une formule pour une mesure dans la barre de formule, une info-bulle affiche un aperçu rapide de ce que seront les résultats pour l’ensemble du contexte actuel, mais autrement, aucun résultat n’est immédiatement généré. La raison pour laquelle vous ne pouvez pas voir les résultats (filtrés) du calcul immédiatement tient au fait que le résultat d’une mesure ne peut pas être déterminé sans contexte. Évaluer une mesure requiert une application cliente de création de rapports qui peut fournir le contexte nécessaire pour récupérer les données relatives à chaque cellule, puis évaluer l’expression pour chaque cellule. Cette application cliente peut être un tableau ou graphique croisé dynamique Excel, un rapport Power BI, ou une expression de table dans une requête DAX dans SSMS (SQL Server Management Studio).

Quelle que soit l’application cliente utilisée, une requête distincte est exécutée pour chaque cellule dans les résultats. Autrement dit, chaque combinaison d’en-têtes de ligne et de colonne dans un tableau croisé dynamique, ou chaque sélection de segments et de filtres dans un rapport Power BI, génère un sous-ensemble de données différent sur lequel la mesure est calculée. Par exemple, c’est ce qui se produit avec cette formule de mesure très simple :

DAXCopier

Total Sales = SUM([Sales Amount])

Quand un utilisateur ajoute la mesure TotalSales à un rapport, puis ajoute la colonne Catégorie de produit d’une table Produit dans Filtres, la somme des ventes (Sales Amount) est calculée et affichée pour chaque catégorie de produit.

Contrairement aux colonnes calculées, la syntaxe d’une mesure inclut le nom de la mesure avant la formule. Dans l’exemple juste au-dessus, le nom Total Sales est placé avant la formule proprement dite. Une fois que vous avez créé une mesure, le nom et sa définition s’affichent dans la liste de champs de l’application cliente de création de rapports et, selon les perspectives et les rôles, la mesure est disponible pour tous les utilisateurs du modèle.

Pour plus d’informations, consultez les rubriques suivantes :
Mesures dans Power BI Desktop
Mesures dans Analysis Services
Mesures dans Power Pivot

Colonnes calculées

Une colonne calculée est une colonne que vous ajoutez à une table existante (dans le concepteur de modèle), puis dans laquelle vous créez une formule DAX qui définit ses valeurs. Quand une colonne calculée contient une formule DAX valide, les valeurs sont calculées pour chaque ligne dès que la formule est entrée. Les valeurs sont ensuite stockées dans le modèle de données en mémoire. Par exemple, dans une table Date, lorsque la formule est entrée dans la barre de formule :

DAXCopier

= [Calendar Year] & " Q" & [Calendar Quarter]

Une valeur pour chaque ligne de la table est calculée en prenant les valeurs de la colonne Calendar Year (dans la même table Date), en ajoutant un espace et la lettre majuscule Q, puis en ajoutant les valeurs de la colonne Calendar Quarter (toujours dans la même table Date). Le résultat pour chaque ligne de la colonne calculée est calculé immédiatement, puis est affiché sous cette forme : 2017 Q1. Les valeurs de la colonne sont recalculées uniquement si la table ou une table associée est traitée (actualisée) ou bien si le modèle est déchargé de la mémoire, puis rechargé, comme c’est le cas notamment lors de la fermeture et de la réouverture d’un fichier Power BI Desktop.

Pour en savoir plus, consultez :
Colonnes calculées dans Power BI Desktop
Colonnes calculées dans Analysis Services
Colonnes calculées dans Power Pivot

Tables calculées

Une table calculée est un objet calculé, sur la base d’une expression de formule et d’une partie ou de la totalité des autres tables contenues dans le même modèle. Au lieu d’interroger et de charger les valeurs dans les colonnes de votre nouvelle table à partir d’une source de données, c’est une formule DAX qui définit les valeurs de la table.

Les tables calculées peuvent être utiles dans une dimension de rôle actif. La table Date en est un exemple, où OrderDate, ShipDate et DueDate dépendent de la relation de clé étrangère. En créant explicitement une table calculée pour ShipDate, vous obtenez une table autonome utilisable pour les requêtes et qui fonctionne comme n’importe quelle autre table. Les tables calculées sont également utiles quand vous configurez un ensemble de lignes filtré, ou un sous-ensemble ou sur-ensemble de colonnes issues d’autres tables existantes. Vous pouvez ainsi conserver la table d’origine inchangée et en créer des variantes pour les besoins de scénarios particuliers.

Les tables calculées prennent en charge les relations avec d’autres tables. Les colonnes de votre table calculée ont des types de données et une mise en forme, et elles peuvent appartenir à une catégorie de données. Les tables calculées peuvent être nommées et être exposées ou masquées comme toutes les autres tables. Les tables calculées sont recalculées si l’une des tables dont elles tirent (pull) des données sont actualisées ou mises à jour.

Pour en savoir plus, consultez :
Tables calculées dans Power BI Desktop
Tables calculées dans Analysis Services

Sécurité au niveau des lignes

Avec la sécurité au niveau des lignes, une formule DAX doit évaluer une condition booléenne TRUE/FALSE, qui définit les lignes pouvant être retournées dans les résultats d’une requête par les membres d’un rôle donné. Par exemple, pour les membres du rôle Sales, la table Customers avec la formule DAX suivante :

DAXCopier

= Customers[Country] = "USA"

Les membres du rôle Sales peuvent uniquement voir les données des clients situés aux États-Unis, et les agrégats, tels que SUM, sont retournés uniquement pour ces clients. La sécurité au niveau des lignes n’est pas disponible dans Power Pivot dans Excel.

Quand vous définissez la sécurité au niveau des lignes à l’aide d’une formule DAX, vous créez un ensemble de lignes autorisées. Cela ne signifie pas que les autres lignes ne sont pas accessibles ; en fait, elles ne sont simplement pas retournées dans l’ensemble de lignes autorisées. D’autres rôles peuvent autoriser l’accès aux lignes qui sont exclues par la formule DAX. Si un utilisateur est membre d’un autre rôle et que la sécurité au niveau des lignes de ce rôle autorise l’accès à cet ensemble de lignes particulier, l’utilisateur peut voir les données de ces lignes.

Les formules de la sécurité au niveau des lignes s’appliquent aux lignes spécifiées ainsi qu’aux lignes associées. Si une table possède plusieurs relations, les filtres appliquent la sécurité de la relation qui est active. Les formules de la sécurité au niveau des lignes se recoupent avec d’autres formules définies pour les tables associées.

Pour en savoir plus, consultez :
Sécurité au niveau des lignes (RLS) avec Power BI
Rôles dans Analysis Services

Requêtes

Les requêtes DAX peuvent être créées et exécutées dans SSMS (SQL Server Management Studio) et des outils open source comme DAX Studio (daxstudio.org). À la différence des formules de calcul DAX, qui peuvent uniquement être créées dans des modèles de données tabulaires, les requêtes DAX peuvent également être exécutées sur des modèles multidimensionnels dans Analysis Services. Les requêtes DAX sont souvent plus faciles à écrire et plus performantes que les requêtes multidimensionnelles (MDX).

Une requête DAX est une instruction comparable à une instruction SELECT dans T-SQL. Le type de requête DAX le plus simple est une instruction d’évaluation (Evaluate). Par exemple,

DAXCopier

EVALUATE
 ( FILTER ( 'DimProduct', [SafetyStockLevel] < 200 ) )
ORDER BY [EnglishProductName] ASC

Retourne comme résultats une table qui liste uniquement les produits dont le niveau de stock de sécurité (valeur SafetyStockLevel) est inférieur à 200, dans l’ordre croissant du nom de produit (valeur EnglishProductName).

Vous pouvez créer des mesures à utiliser dans la requête. Ces mesures existent seulement pour la durée de la requête. Pour en savoir plus, consultez Requêtes DAX.

Formules

Les formules DAX sont essentielles pour créer des calculs dans des colonnes calculées et des mesures, et pour sécuriser vos données à l’aide de la sécurité au niveau des lignes. Pour créer des formules pour des colonnes calculées et des mesures, utilisez la barre de formule en haut de la fenêtre du concepteur de modèle ou l’éditeur DAX. Pour créer des formules pour la sécurité au niveau des lignes, utilisez la boîte de dialogue Gestionnaire de rôles ou Gérer les rôles. Les informations de cette section sont destinées à vous aider à comprendre les principes de base des formules DAX.

Principes de base des formules

Les formules DAX peuvent être très simples ou assez complexes. Le tableau suivant présente quelques exemples de formules simples qui peuvent être utilisées dans une colonne calculée.Agrandir le tableau

Formule	Définition
= TODAY()	Insère la date du jour dans chaque ligne d’une colonne calculée.
= 3	Insère la valeur 3 dans chaque ligne d’une colonne calculée.
= [Column1] + [Column2]	Ajoute les valeurs dans la même ligne des colonnes [Column1] et [Column2], et insère le résultat dans la colonne calculée de la même ligne.

Que vous souhaitiez créer une formule simple ou complexe, vous pouvez suivre les étapes ci-dessous :

1. Chaque formule doit commencer par un signe égal (=).
2. Tapez ou sélectionnez un nom de fonction, ou tapez une expression.
3. Tapez les premières lettres de la fonction ou du nom que vous souhaitez ; la fonctionnalité Autocomplétion affiche alors une liste de fonctions, tables et colonnes disponibles. Appuyez sur Tab pour insérer un élément de la liste Autocomplétion dans la formule.Vous pouvez aussi cliquer sur le bouton Fx pour afficher une liste de fonctions disponibles. Pour sélectionner une fonction dans la liste déroulante, utilisez les flèches afin de mettre en surbrillance l’élément, puis cliquez sur OK pour ajouter la fonction à la formule.
4. Spécifiez les arguments de la fonction en les sélectionnant dans une liste déroulante de tables et de colonnes possibles, ou en tapant leurs valeurs.
5. Recherchez les éventuelles erreurs de syntaxe : vérifiez que toutes les parenthèses sont fermées et que les colonnes, les tables et les valeurs sont correctement référencées.
6. Appuyez sur Entrée pour valider la formule.

Notes

Dans une colonne calculée, dès que la formule entrée est validée, la colonne est remplie avec des valeurs. Dans une mesure, la définition de la mesure est enregistrée avec la table quand vous appuyez sur Entrée. Si une formule n’est pas valide, une erreur s’affiche.

Dans cet exemple, examinons une formule dans une mesure nommée Days in Current Quarter :

DAXCopier

Days in Current Quarter = COUNTROWS( DATESBETWEEN( 'Date'[Date], STARTOFQUARTER( LASTDATE('Date'[Date])), ENDOFQUARTER('Date'[Date])))

Cette mesure est utilisée pour créer un ratio de comparaison entre une période incomplète et la période précédente. La formule doit prendre en compte la proportion de la période déjà écoulée et la comparer à la même proportion de la période précédente. Dans ce cas, le ratio [Days Current Quarter to Date]/[Days in Current Quarter] donne la proportion écoulée de la période actuelle.

Cette formule contient les éléments suivants :Agrandir le tableau

Élément de la formule	Description
Days in Current Quarter	Nom de la mesure.
=	Le signe égal (=) commence la formule.
COUNTROWS	COUNTROWS compte le nombre de lignes dans la table Date.
()	Les parenthèses ouvrantes et fermantes spécifient des arguments.
DATESBETWEEN	La fonction DATESBETWEEN retourne les dates entre la dernière date pour chaque valeur de la colonne Date dans la table Date.
'Date'	Spécifie la table Date. Les tables sont spécifiées entre deux guillemets simples.
[Date]	Spécifie la colonne Date dans la table Date. Les colonnes sont spécifiées entre crochets.
,
STARTOFQUARTER	La fonction STARTOFQUARTER retourne la date du début du trimestre.
LASTDATE	La fonction LASTDATE retourne la dernière date du trimestre.
'Date'	Spécifie la table Date.
[Date]	Spécifie la colonne Date dans la table Date.
,
ENDOFQUARTER	Fonction ENDOFQUARTER
'Date'	Spécifie la table Date.
[Date]	Spécifie la colonne Date dans la table Date.

Utilisation de la fonctionnalité Autocomplétion dans les formules

La saisie semi-automatique vous aide à saisir une syntaxe de formule valide en vous proposant des options pour chaque élément de la formule.

• Vous pouvez utiliser la saisie semi-automatique des formules au milieu d’une formule existante avec les fonctions imbriquées. Le texte immédiatement avant le point d’insertion est utilisé pour afficher des valeurs dans la liste déroulante, et tout le texte après le point d’insertion reste inchangé.
• La saisie semi-automatique n’ajoute pas la parenthèse fermante des fonctions, ni ne met automatiquement en correspondance les parenthèses. Vous devez vous assurer que chaque fonction est correcte syntaxiquement ou vous ne pouvez pas enregistrer ni utiliser la formule.

Utilisation de plusieurs fonctions dans une formule

Vous pouvez imbriquer des fonctions, ce qui signifie que vous utilisez les résultats d’une fonction comme un argument d’une autre fonction. Vous pouvez imbriquer jusqu’à 64 niveaux de fonctions dans les colonnes calculées. Toutefois, l’imbrication peut rendre la création ou le dépannage de formules difficile. De nombreuses fonctions sont conçues pour être utilisées uniquement comme fonctions imbriquées. Ces fonctions retournent une table, qui ne peut pas être enregistrée directement comme résultat ; elle doit être fournie comme entrée à une fonction de table. Par exemple, les fonctions SUMX, AVERAGEX et MINX requièrent toutes une table comme premier argument.

Fonctions

Une fonction est une formule nommée dans une expression. La plupart des fonctions prennent en entrée des arguments obligatoires et facultatifs, également appelés paramètres. Lorsque la fonction est exécutée, une valeur est retournée. DAX fournit des fonctions qui vous permettent d’effectuer des calculs avec des dates et des heures, de créer des valeurs conditionnelles, d’utiliser des chaînes, d’effectuer des recherches basées sur des relations et d’itérer sur une table pour faire des calculs récursifs. Si vous connaissez les formules Excel, plusieurs de ces fonctions vous seront familières ; toutefois, les formules DAX diffèrent à de nombreux égards. Voici quelques différences de taille :

• Une fonction DAX fait toujours référence à une table ou une colonne complète. Si vous souhaitez utiliser certaines valeurs particulières d’une table ou colonne, vous pouvez ajouter des filtres à la formule.
• Si vous devez personnaliser des calculs en fonction de chaque ligne, DAX fournit des fonctions qui vous permettent d’utiliser la valeur de ligne actuelle ou une valeur associée comme genre de paramètre pour effectuer des calculs qui varient selon le contexte. Pour comprendre le fonctionnement de ces fonctions, consultez la section Contexte dans le présent article.
• DAX inclut de nombreuses fonctions qui retournent une table, plutôt qu’une valeur. La table n’est pas affichée dans un client de création de rapports, mais elle est utilisée pour fournir une entrée à d’autres fonctions. Par exemple, vous pouvez récupérer une table, puis compter les valeurs distinctes qu’elle contient, ou calculer les sommes dynamiques entre les colonnes ou les tables filtrées.
• Les fonctions DAX intègrent une grande diversité de fonctions d’intelligence temporelle. Ces fonctions vous permettent de définir ou de sélectionner des plages de dates, et effectuer des calculs dynamiques basés sur ces dates ou plages. Par exemple, vous pouvez comparer les sommes sur des périodes parallèles.

Fonctions d’agrégation

Les fonctions d’agrégation calculent une valeur (scalaire) telle que count, sum, average, minimum ou maximum pour toutes les lignes d’une colonne ou d’une table, comme défini par l’expression. Pour en savoir plus, voir Fonctions d’agrégation.

Fonctions de date et heure

Les fonctions de date et d’heure de DAX sont semblables à celles de Microsoft Excel. Toutefois, les fonctions DAX sont basées sur un type de données datetime dont la date de début est le 1er mars 1900. Pour en savoir plus, consultez Fonctions de date et heure.

Fonctions de filtrage

Les fonctions de filtrage de DAX permettent de retourner des types de données spécifiques, de rechercher des valeurs dans les tables associées et de procéder à un filtrage par valeurs associées. Les fonctions de recherche s’appuient sur des tables et des relations, comme une base de données. Les fonctions de filtrage vous permettent de manipuler le contexte de données pour créer des calculs dynamiques. Pour en savoir plus, consultez Fonctions de filtre.

Fonctions financières

Les fonctions financières dans DAX s’utilisent dans des formules qui effectuent des calculs financiers, par exemple, pour connaître la valeur actuelle nette et le taux de rendement. Ces fonctions sont similaires aux fonctions financières utilisées dans Microsoft Excel. Pour en savoir plus, consultez Fonctions financières.

Fonctions d’information

Une fonction d’information examine la cellule ou la ligne qui est fournie comme argument et vous indique si la valeur correspond au type attendu. Par exemple, la fonction ISERROR retourne TRUE si la valeur que vous référencez contient une erreur. Pour en savoir plus, consultez Fonctions d’information.

Fonctions logiques

Les fonctions logiques agissent sur une expression pour retourner des informations sur les valeurs au sein de l’expression. Par exemple, la fonction TRUE vous permet de savoir si une expression que vous évaluez retourne une valeur TRUE. Pour en savoir plus, consultez Fonctions logiques.

Fonctions mathématiques et trigonométriques

Les fonctions mathématiques dans DAX sont très semblables aux fonctions mathématiques et trigonométriques Excel. Il existe quelques différences mineures dans les types de données numériques utilisés par les fonctions DAX. Pour en savoir plus, consultez Fonctions mathématiques et trigonométriques.

Autres fonctions

Ces fonctions effectuent des actions uniques qui n’entrent dans aucune des catégories définissant la plupart des autres fonctions. Pour en savoir plus, consultez Autres fonctions.

Fonctions de relation

Les fonctions de relation dans DAX vous permettent de retourner des valeurs d’une autre table associée, de spécifier une relation particulière à utiliser dans une expression et de définir la direction du filtrage croisé. Pour en savoir plus, consultez Fonctions de relation.

Fonctions statistiques

Les fonctions statistiques calculent des valeurs liées aux distributions statistiques et à la probabilité, telles que l’écart type et le nombre de permutations. Pour en savoir plus, consultez Fonctions statistiques.

Fonctions de texte

Les fonctions de texte dans DAX sont très semblables à leurs équivalents dans Excel. Vous pouvez retourner une partie d’une chaîne, rechercher un texte dans une chaîne ou concaténer des valeurs de chaîne. DAX fournit également des fonctions pour le contrôle des formats pour les dates, les heures et les nombres. Pour en savoir plus, consultez Fonctions de texte.

Fonctions d’intelligence temporelle

Les fonctions d’intelligence temporelle fournies dans DAX vous permettent de créer des calculs qui utilisent la connaissance intégrée relative aux calendriers et aux dates. En utilisant des plages de dates et d’heures en association avec des agrégations ou des calculs, vous pouvez générer des comparaisons significatives sur des périodes de temps comparables pour les ventes, les stocks, etc. Pour en savoir plus, consultez Fonctions d’intelligence temporelle (DAX).

Fonctions de manipulation de tables

Ces fonctions retournent une table ou manipulent des tables existantes. Par exemple, vous pouvez ajouter des colonnes calculées à une table spécifiée avec la fonction ADDCOLUMNS, ou retourner une table de résumé sur un ensemble de groupes avec la fonction SUMMARIZECOLUMNS. Pour en savoir plus, consultez Fonctions de manipulation de tables.

Variables

Vous pouvez créer des variables dans une expression en utilisant VAR. VAR n’est pas une fonction d’un point de vue technique : c’est un mot clé qui stocke le résultat d’une expression sous la forme d’une variable nommée. Cette variable peut ensuite être passée comme argument à d’autres expressions de mesure. Par exemple :

DAXCopier

VAR
    TotalQty = SUM ( Sales[Quantity] )

Return

    IF (
        TotalQty > 1000,
        TotalQty * 0.95,
        TotalQty * 1.25
        )

Dans cet exemple, TotalQty peut être passé en tant que variable nommée à d’autres expressions. Les variables peuvent être de n’importe quel type de données scalaire, y compris des tables. L’emploi de variables dans vos formules DAX peut être extrêmement puissant.

Types de données

Vous pouvez importer des données dans un modèle à partir de nombreuses sources de données différentes, qui peuvent prendre en charge des types de données différents. Lorsque vous importez des données dans un modèle, les données sont converties en un des types de données de modèle tabulaire. Quand les données du modèle sont utilisées dans un calcul, les données sont converties en un type de données DAX pour la durée et le résultat du calcul. Lorsque vous créez une formule DAX, les termes utilisés dans la formule déterminent automatiquement le type de données de valeur retournées.

DAX prend en charge les types de données suivants :Agrandir le tableau

Type de données dans le modèle	Type de données dans DAX	Description
Nombre entier	Valeur entière de 64 bits (huit octets) 1, 2	Nombres qui n’ont pas de décimales. Les entiers peuvent être des nombres positifs ou négatifs, mais doivent être compris entre -9 223 372 036 854 775 808 (-2^63) et 9 223 372 036 854 775 807 (2^63-1).
Nombre décimal	Nombre réel de 64 bits (huit octets) 1, 2	Les nombres réels sont des nombres qui peuvent avoir des décimales. Les nombres réels couvrent une large gamme de valeurs : Valeurs négatives de -1.79E +308 à -2.23E -308 Zéro Valeurs positives de 2.23E -308 à -1.79E +308 Toutefois, le nombre de bits significatifs est limité à 17 chiffres décimaux.
Booléen	Boolean	Valeur True ou valeur False.
Texte	String	Chaîne de données caractères au format Unicode. Il peut s’agir de chaînes, de nombres ou de dates représentés dans un format texte.
Date	Date/heure	Dates et heures dans une représentation date-heure acceptée. Les dates valides sont toutes les dates après le 1er mars 1900.
Devise	Devise	Le type de données devise autorise des valeurs entre -922 337 203 685 477,5808 et 922 337 203 685 477,5807 avec quatre chiffres décimaux à précision fixe.
N/A	Vide	Le type de données Vide (Blank) de DAX représente et remplace les valeurs Null SQL. Vous pouvez créer une valeur vide à l’aide de la fonction BLANK et tester les valeurs vides à l’aide de la fonction logique ISBLANK.

Les modèles de données tabulaires incluent également le type de données Table en entrée ou en sortie dans de nombreuses fonctions DAX. Par exemple, la fonction FILTER prend une table en entrée et génère en sortie une autre table qui contient uniquement les lignes qui répondent aux conditions de filtre. En associant des fonctions de table à des fonctions d’agrégation, vous pouvez effectuer des calculs complexes sur des jeux de données définis de façon dynamique.

Bien que les types de données soient généralement définis automatiquement, il est important de comprendre de quelle façon ils s’appliquent, notamment, aux formules DAX. Des erreurs dans les formules ou des résultats inattendus sont notamment dus à l’emploi d’un opérateur spécifique qui ne peut pas être utilisé avec le type de données spécifié dans un argument. Par exemple, la formule = 1 & 2, retourne un résultat de chaîne de 12. Toutefois, la formule = "1" + "2"retourne un résultat entier de 3

Context

Le contexte est un concept important qu’il faut bien comprendre quand vous créez des formules DAX. Le contexte vous permet d’effectuer une analyse dynamique dans la mesure où les résultats d’une formule changent pour refléter la sélection de ligne ou de cellule actuelle, ainsi que toutes les données associées. Il est impératif de comprendre ce qu’est un contexte et de savoir utiliser un contexte à bon escient pour créer des analyses dynamiques performantes et pour résoudre les problèmes dans les formules.

Les formules dans les modèles tabulaires peuvent être évaluées dans un contexte différent, en fonction d’autres éléments de conception.

• Filtres appliqués dans un tableau croisé dynamique ou un rapport
• Filtres définis dans une formule
• Relations spécifiées à l’aide de fonctions spéciales dans une formule

Il existe différents types de contexte : contexte de ligne, contexte de requête et contexte de filtre.

Contexte de ligne

Le contexte de ligne peut être vu comme « la ligne active ». Si vous créez une formule dans une colonne calculée, le contexte de ligne correspondant à cette formule inclut les valeurs de toutes les colonnes dans la ligne actuelle. Si la table est associée à une autre table, le contenu inclut également toutes les valeurs de cette autre table qui sont mises en relation avec la ligne actuelle.

Par exemple, supposons que vous créez une colonne calculée, = [Freight] + [Tax], qui additionne les valeurs de deux colonnes, Freight et Tax, de la même table. Cette formule obtient automatiquement les valeurs de la ligne actuelle uniquement dans les colonnes spécifiées.

Le contexte de ligne suit également toutes les relations définies entre les tables, notamment les relations définies dans une colonne calculée à l’aide de formules DAX, afin de déterminer les lignes des tables liées qui sont associées à la ligne actuelle.

Par exemple, la formule suivante utilise la fonction RELATED pour extraire une valeur de taxe d’une table associée, selon la région vers laquelle la commande a été expédiée. La valeur de taxe est déterminée par l’utilisation de la valeur de la région dans la table actuelle, la recherche de la région dans la table associée, puis l’obtention du taux d’imposition de cette région à partir de la table associée.

DAXCopier

= [Freight] + RELATED('Region'[TaxRate])  

Cette formule obtient le taux d’imposition pour la région active de la table Region et l’ajoute à la valeur de la colonne de Freight. Dans les formules DAX, vous n’avez pas besoin de connaître ou spécifier la relation spécifique qui connecte les tables.

Contextes de ligne multiples

Le langage DAX (Data Analysis Expressions) inclut des fonctions qui itèrent des calculs sur une table. Ces fonctions peuvent avoir plusieurs lignes actives, chacune avec son propre contexte de ligne. Fondamentalement, ces fonctions vous permettent de créer des formules qui effectuent des opérations de manière récursive sur une boucle interne et externe.

Par exemple, supposons que votre modèle contienne une table Products et une table Sales . Les utilisateurs peuvent vouloir parcourir la totalité de la table Sales, remplie de transactions impliquant plusieurs produits, et rechercher la quantité maximale commandée pour chaque produit dans l’ensemble de ces transactions.

Avec DAX, vous pouvez générer une formule unique qui retourne la valeur correcte, et les résultats sont automatiquement mis à jour chaque fois que l’utilisateur ajoute des données aux tables.

DAXCopier

= MAXX(FILTER(Sales,[ProdKey] = EARLIER([ProdKey])),Sales[OrderQty])  

Pour obtenir un exemple détaillé de cette formule, consultez EARLIER.

Pour résumer, la fonction EARLIER stocke le contexte de ligne de l’opération qui a précédé l’opération active. Cette fonction stocke systématiquement en mémoire deux ensembles de contexte : le premier ensemble représente la ligne actuelle pour la boucle interne de la formule, et le second ensemble représente la ligne actuelle pour la boucle externe de la formule. DAX fournit automatiquement les valeurs entre les deux boucles pour vous permettre de créer des agrégats complexes.

Contexte de requête

Uncontexte de requête fait référence au sous-ensemble de données qui est implicitement récupéré pour une formule. Par exemple, quand un utilisateur insère une mesure ou un champ dans un rapport, le moteur examine les en-têtes de ligne et de colonne, les segments et les filtres de rapport pour déterminer le contexte. Les requêtes nécessaires sont ensuite exécutées sur les données de modèle pour obtenir le sous-ensemble correct de données, effectuer les calculs définis par la formule, puis remplir les valeurs dans le rapport.

Étant donné que le contexte change en fonction de l’endroit où vous placez la formule, les résultats de la formule peuvent varier également. Supposons, par exemple, que vous créez une formule qui additionne les valeurs de la colonne Profit de la table Sales : = SUM('Sales'[Profit]). Si vous utilisez cette formule dans une colonne calculée dans la table Sales, les résultats de la formule seront identiques pour toute la table, car le contexte de requête utilisé par la formule est toujours l’ensemble de données complet de la table Sales. Les résultats indiqueront les bénéfices pour toutes les régions, tous les produits, toutes les années, etc.

Cependant, plutôt que d’obtenir le même résultat des centaines de fois, les utilisateurs voudront connaître le bénéfice pour une année, un pays ou un produit en particulier (ou pour une combinaison de ces éléments), avant d’avoir un total général.

Dans un rapport, le contexte peut être changé par le filtrage, l’ajout ou la suppression de champs et l’utilisation de segments. Cela entraîne le changement du contexte de requête dans lequel la mesure est évaluée. Par conséquent, la même formule, utilisée dans une mesure, est évaluée dans un contexte de requête différent pour chaque cellule.

Contexte de filtre

Lecontexte de filtre est l’ensemble de valeurs autorisé dans chaque colonne, ou dans les valeurs récupérées d’une table associée. Des filtres peuvent être appliqués à la colonne dans le concepteur, ou dans la couche de présentation (rapports et tableaux croisés dynamiques). Des filtres peuvent également être définis explicitement par des expressions de filtre dans la formule.

Un contexte de filtre est ajouté lorsque vous spécifiez des contraintes de filtre sur l’ensemble de valeurs autorisé dans une colonne ou une table, en utilisant les arguments d’une formule. Un contexte de filtre s’applique au-dessus des autres contextes, tels qu’un contexte de ligne ou un contexte de requête.

Dans des modèles tabulaires, plusieurs méthodes s’offrent à vous pour créer le contexte de filtre. Dans le contexte des clients consommateurs du modèle, comme les rapports Power BI, les utilisateurs peuvent créer des filtres instantanément en ajoutant des segments ou des filtres de rapport sur les en-têtes de ligne et de colonne. Vous pouvez également spécifier des expressions de filtre directement dans la formule, pour spécifier des valeurs associées, filtrer les tables utilisées comme entrées ou obtenir de façon dynamique le contexte des valeurs utilisées dans les calculs. Vous pouvez également supprimer entièrement les filtres ou les effacer de manière sélective sur des colonnes spécifiques. C’est très utile lors de la création de formules qui calculent des totaux généraux.

Pour en savoir plus sur la création de filtres dans les formules, consultez FILTER, fonction (DAX).
Pour obtenir un exemple de la procédure de suppression des filtres pour créer des totaux généraux, consultez Fonction ALL (DAX).

Pour obtenir des exemples d’application et de suppression sélective des filtres dans les formules, consultez ALLEXCEPT.

Détermination du contexte dans les formules

Lorsque vous créez une formule DAX, la formule est d’abord testée pour vérifier si la syntaxe est valide, puis pour vérifier que les noms des colonnes et des tables inclus dans la formule figurent dans le contexte actuel. Si aucune colonne ou table spécifiée par la formule ne peut être trouvée, une erreur est retournée.

Le contexte au cours de la validation (et des opérations de recalcul) est déterminé comme décrit dans les sections précédentes, à l’aide des tables disponibles dans le modèle, des relations entre les tables et des éventuels filtres qui ont été appliqués.

Par exemple, si vous venez d’importer des données dans une nouvelle table et que celle-ci n’est pas associée à d’autres tables (et que vous n’avez pas appliqué de filtres), le contexte actuel correspond à l’ensemble entier de colonnes dans la table. Si la table est liée par des relations à d’autres tables, le contexte actuel inclut les tables associées. Si vous ajoutez une colonne de la table à un rapport qui contient des segments et éventuellement certains filtres de rapport, le contexte de la formule correspond au sous-ensemble de données dans chaque cellule du rapport.

Le contexte est un concept puissant qui peut également compliquer la résolution des problèmes liés aux formules. Nous vous recommandons de commencer par des formules et des relations simples pour voir comment le contexte s’exécute. La section suivante fournit quelques exemples d’utilisation de différents types de contextes par les formules pour un retour dynamique des résultats.

Opérateurs

Le langage DAX utilise quatre types différents d’opérateurs de calcul dans les formules :

• Opérateurs de comparaison pour comparer des valeurs et retourner une valeur TRUE\FALSE logique.
• Opérateurs arithmétiques pour effectuer des calculs arithmétiques qui retournent des valeurs numériques.
• Opérateurs de concaténation de texte pour joindre deux chaînes de texte ou plus.
• Opérateurs logiques qui combinent deux expressions ou plus pour retourner un résultat unique.

Pour plus d’informations sur les opérateurs utilisés dans les formules DAX, consultez Opérateurs DAX.

Utilisation de tables et de colonnes

Les tables dans les modèles de données tabulaires ont une apparence similaire aux tables Excel, mais elles interagissent différemment avec les données et les formules :

• Les formules fonctionnent uniquement avec les tables et les colonnes, et non avec les cellules individuelles, les références de plage ou les tableaux.
• Les formules peuvent utiliser des relations pour obtenir des valeurs à partir de tables associées. Les valeurs récupérées sont toujours associées à la valeur de ligne actuelle.
• Vous ne pouvez pas avoir de données irrégulières ou « déséquilibrées », comme vous le pouvez dans une feuille de calcul Excel. Chaque ligne d’une table doit contenir le même nombre de colonnes. Toutefois, certaines colonnes peuvent comporter des valeurs vides. Les tables de données Excel et les tables de données de modèle tabulaire ne sont pas interchangeables.
• Un type de données étant défini pour chaque colonne, toutes les valeurs de cette colonne doivent être du même type.

Référence aux tables et aux colonnes dans les formules

Vous pouvez faire référence à toute table et colonne à l’aide de son nom. Par exemple, la formule suivante indique comment faire référence aux colonnes de deux tables à l’aide du nom complet :

DAXCopier

= SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])  

Lorsqu’une formule est évaluée, le générateur de modèles vérifie en premier la syntaxe générale, puis il contrôle les noms des colonnes et des tables que vous fournissez par rapport aux colonnes et tables possibles dans le contexte actuel. Si le nom est ambigu ou si la colonne/la table est introuvable, vous obtiendrez une erreur dans votre formule (une #chaîne ERROR au lieu d’une valeur de données dans les cellules où l’erreur se produit). Pour en savoir plus sur les conventions de nommage des tables, colonnes et autres objets, consultez la section en question dans Syntaxe DAX.

Relations entre les tables

Créer des relations entre les tables vous donne la possibilité d’utiliser des valeurs associées dans d’autres tables pour les calculs. Par exemple, utilisez une colonne calculée pour déterminer tous les enregistrements d’expédition associés au revendeur actuel et calculer ensuite le total des frais d’expédition pour chacun d’eux. Dans de nombreux cas, toutefois, il est possible qu’une relation ne soit pas nécessaire. Vous pouvez utiliser la fonction LOOKUPVALUE dans une formule afin de retourner la valeur dans result_columnName pour la ligne répondant aux critères spécifiés dans les arguments search_column et search_value.

De nombreuses fonctions DAX requièrent l’existence d’une relation entre les tables ou entre plusieurs tables, afin de localiser les colonnes que vous avez référencées et de retourner des résultats qui ont un sens. D’autres fonctions essaient d’identifier la relation ; toutefois, lorsque cela est possible, créez une relation pour optimiser les résultats. Les modèles de données tabulaires prennent en charge les relations multiples entre les tables. Pour éviter toute confusion ou des résultats incorrects, une seule relation à la fois est désignée comme la relation active. Vous pouvez changer la relation active si nécessaire pour parcourir les différentes relations dans les données des calculs. La fonction USERELATIONSHIP permet de spécifier une ou plusieurs relations à utiliser dans un calcul spécifique.

Il est important d’observer les règles de conception de formule suivantes si vous utilisez des relations :

• Quand des tables sont liées par une relation, vous devez vous assurer que les deux colonnes utilisées comme clés ont des valeurs qui correspondent. En effet, l’intégrité référentielle n’étant pas vérifiée ici, il est possible de créer une relation même s’il y a des valeurs sans correspondance dans une colonne clé. Si cela se produit, vous devez savoir que des valeurs vides ou sans correspondance peuvent affecter les résultats des formules.
• Lorsque vous liez des tables dans votre modèle à l’aide de relations, vous agrandissez l’étendue, ou le contexte, dans laquelle vos formules sont évaluées. Des modifications dans le contexte résultant de l’ajout de nouvelles tables, de nouvelles relations ou de modifications de la relation active peuvent entraîner des modifications de vos résultats qu’il n’est pas possible d’anticiper. Pour en savoir plus, consultez Contexte dans cet article.

Traitement et actualisation

Le traitement et le recalcul sont deux opérations distinctes mais interdépendantes. Vous devez comprendre en détail ces concepts lorsque vous concevez un modèle qui contient des formules complexes, de grandes quantités de données ou des données obtenues de sources de données externes.

Le traitement (actualisation) met à jour les données dans un modèle avec de nouvelles données provenant d’une source de données externe.

Lerecalcul est le processus de mise à jour des résultats des formules afin de refléter toutes les modifications qui leur ont été apportées, ainsi que toutes les modifications des données sous-jacentes. Le recalcul peut affecter les performances des façons suivantes :

• Les valeurs dans une colonne calculée sont calculées et stockées dans le modèle. Pour mettre à jour les valeurs dans la colonne calculée, vous devez traiter le modèle à l’aide de l’une des trois commandes de traitement – Traiter entièrement, Traiter les données ou Traiter le recalcul. Le résultat de la formule doit toujours être recalculé, pour la colonne entière, chaque fois que vous modifiez la formule.
• Les valeurs calculées par une mesure sont évaluées dynamiquement chaque fois qu’un utilisateur ajoute la mesure à un tableau croisé dynamique ou ouvre un rapport. Quand l’utilisateur change le contexte, les valeurs retournées par la mesure changent également. Les résultats de la mesure reflètent toujours les valeurs les plus récentes dans le cache en mémoire.

Le traitement et le recalcul n’ont pas d’impact sur les formules de sécurité au niveau des lignes, sauf si le résultat d’un recalcul retourne une valeur différente, rendant la ligne interrogeable ou non interrogeable par les membres du rôle.

Mises à jour

DAX est constamment amélioré. Les fonctions nouvelles et modifiées sont publiées avec chaque mise à jour suivante disponible, qui est généralement mensuelle. Les mises à jour sont appliquées aux services en premier, puis aux applications installées telles que Power BI Desktop, Excel, SQL Server Management Studio (SSMS) et l’extension de projets Analysis Services pour Visual Studio (SSDT). SQL Server Analysis Services est mis à jour avec la mise à jour cumulative suivante. Les nouvelles fonctions sont d’abord annoncées et décrites dans la documentation de référence des fonctions DAX, en même temps que les mises à jour de Power BI Desktop.

Les fonctions ne sont pas toutes prises en charge dans les versions antérieures de SQL Server Analysis Services et d’Excel.

Dépannage

Si vous obtenez une erreur lorsque vous définissez une formule, la formule peut contenir une erreur syntaxique, une erreur sémantiqueou une erreur de calcul.

Les erreurs syntactiques sont les plus faciles à résoudre. Elles impliquent en général une virgule ou une parenthèse manquante.

Dans les deux autres types d’erreurs, la syntaxe est correcte, mais la valeur ou une colonne référencée n’a pas de sens dans le contexte de la formule. De telles erreurs sémantiques et de calcul peuvent être provoquées par l’un des problèmes suivants :

• La formule fait référence à une colonne, une table ou une fonction non existante.
• La formule semble être correcte, mais lorsque le moteur de données extrait les données, il trouve une incompatibilité de type et génère une erreur.
• La formule transmet un nombre ou un type d’arguments incorrect à une fonction.
• La formule fait référence à une colonne différente qui comporte une erreur, et par conséquent, ses valeurs ne sont pas valides.
• La formule fait référence à une colonne qui n’a pas été traitée, ce qui signifie qu’elle comporte des métadonnées, mais aucune donnée à proprement parler à utiliser pour les calculs.

Dans les quatre premiers cas, DAX signale la colonne entière qui contient la formule non valide. Dans le dernier cas, DAX grise la colonne pour indiquer que la colonne se trouve dans un état non traité.

Applications et outils

Power BI Desktop

Power BI Desktop est une application gratuite conçue pour la modélisation des données et la création de rapports. Le concepteur de modèle comprend un éditeur DAX permettant de créer des formules de calcul DAX.

Power Pivot dans Excel

Le concepteur de modèle Power Pivot dans Excel comprend un éditeur DAX permettant de créer des formules de calcul DAX.

Visual Studio

Visual Studio avec l’extension de projets Analysis Services (VSIX) s’utilise pour créer des projets de modèle Analysis Services. Le concepteur de modèle tabulaire, installé avec l’extension de projets, comprend un éditeur DAX.

SQL Server Management Studio

SSMS (SQL Server Management Studio) est un outil essentiel pour travailler avec Analysis Services. SSMS comprend un éditeur de requête DAX permettant d’interroger à la fois des modèles tabulaires et des modèles multidimensionnels.

DAX Studio

DAX Studio est un outil client open source qui permet de créer et d’exécuter des requêtes DAX sur des modèles Analysis Services, Power BI Desktop et Power Pivot dans Excel.

Éditeur tabulaire

Éditeur tabulaire est un outil open source qui fournit une vue hiérarchique et intuitive de chaque objet dans les métadonnées du modèle tabulaire. Éditeur tabulaire inclut un éditeur DAX avec mise en surbrillance syntaxique, qui permet de modifier facilement des mesures, des colonnes calculées et des expressions de table calculée.

Ressources d’apprentissage

Pendant la phase d’apprentissage de DAX, il est préférable d’utiliser l’application dont vous vous servirez ensuite pour créer vos propres modèles de données. Que ce soit pour Analysis Services, Power BI Desktop ou Power Pivot dans Excel, vous trouverez de nombreux articles et tutoriels qui expliquent pas à pas comment créer des mesures, des colonnes calculées et des filtres de lignes à l’aide du langage DAX. Voici quelques ressources supplémentaires :

Vidéos

Parcours d’apprentissage Utiliser DAX dans Power BI Desktop.

Definitive Guide to DAX, des auteurs Alberto Ferrari et Marco Russo (Microsoft Press). Maintenant dans ce deuxième édition, ce guide complet est destiné aux modélisateurs de données et aux professionnels en Business Intelligence qui débutent. Il fournit des informations générales sur des techniques innovantes et très performantes.

Communauté

DAX a une communauté dynamique, toujours désireuse de partager son expertise. La communauté Power BI de Microsoft a un forum de discussion spécial pour DAX, ses commandes et des conseils.