Scénarios DAX dans Power Pivot

Créer des calculs personnalisés pour un tableau croisé dynamique

CALCULATE et CALCULATETABLE sont des fonctions puissantes et flexibles qui sont utiles pour définir des champs calculés. Ces fonctions vous permet de modifier le contexte dans lequel le calcul sera effectué. Vous pouvez également personnaliser le type d’agrégation ou l’opération mathématique à effectuer. Consultez les rubriques suivantes pour obtenir des exemples.

• CALCULATE

• CALCULATETABLE

Appliquer un filtre à une formule

Dans la plupart des endroits où une fonction DAX prend une table comme argument, vous pouvez généralement passer dans une table filtrée, soit en utilisant la fonction FILTER à la place du nom de la table, soit en spécifiant une expression de filtre comme un des arguments de la fonction. Les rubriques suivantes donnent des exemples de la façon de créer des filtres et de la façon dont les filtres affectent les résultats des formules. Pour plus d’informations, voir Filtrer des données dans des formules DAX.

La fonction FILTRE vous permet de spécifier des critères de filtre à l’aide d’une expression, tandis que les autres fonctions sont spécifiquement conçues pour filtrer les valeurs vides.

• FILTRE

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Supprimer les filtres de manière sélective pour créer un rapport dynamique

En créant des filtres dynamiques dans les formules, vous pouvez facilement répondre aux questions telles que les suivantes :

• Quelle a été la contribution des ventes du produit actuel au total des ventes de l’année ?

• Quelle est la contribution de cette division aux bénéfices totaux pour toutes les années d’exploitation, par rapport aux autres divisions ?

Les formules que vous utilisez dans un tableau croisé dynamique peuvent être affectées par le contexte de tableau croisé dynamique, mais vous pouvez en modifier le contexte de manière sélective en ajoutant ou en supprimant des filtres. L’exemple de la rubrique TOUS vous montre comment faire. Pour rechercher le rapport des ventes d’un revendeur spécifique par rapport aux ventes de tous les revendeurs, vous créez une mesure qui calcule la valeur du contexte actuel divisé par la valeur du contexte ALL.

La rubrique ALLEXCEPT fournit un exemple de la façon d’effacer sélectivement des filtres sur une formule. Ces deux exemples vous indiquent comment les résultats changent selon la conception du tableau croisé dynamique.

• ALL

• ALLEXCEPT

Pour obtenir d’autres exemples de calcul des ratios et des pourcentages, voir les rubriques suivantes :

• ESTBLANK

• ESTERREUR

Utilisation d’une valeur d’une boucle externe

Outre l’utilisation de valeurs du contexte actuel dans les calculs, DAX peut utiliser une valeur d’une boucle précédente dans la création d’un ensemble de calculs connexes. La rubrique suivante présente comment créer une formule qui fait référence à une valeur issue d’une boucle externe. La fonction EARLIER prend en charge jusqu’à deux niveaux de boucles imbrmbrées.

• EARLIER, fonction

Pour en savoir plus sur le contexte de ligne et les tables liées, et comment utiliser ce concept dans les formules, consultez Contexte dans les formules DAX.

Créer une colonne clé par concatenation

Power Pivot n’autorise pas les clés composites ; par conséquent, si vous avez des clés composites dans votre source de données, vous devrez peut-être les combiner en une seule colonne clé. La rubrique suivante fournit un exemple de la façon de créer une colonne calculée basée sur une clé composite.

• Fonction CONCATENER

Composer une date sur la base des parties de date extraites d’une date au texte

Power Pivot utilise un type SQL Server de données date/heure pour l’utilisation des dates. par conséquent, si vos données externes contiennent des dates dont le format de mise en forme est différent, par exemple, si vos dates sont écrites dans un format de date régional qui n’est pas reconnu par le moteur de données Power Pivot, ou si vos données utilisent des clés de substitution d’entiers, vous devrez peut-être utiliser une formule DAX pour extraire les parties de date, puis composer les parties en une représentation date/heure valide.

Par exemple, si vous avez une colonne de dates qui ont été représentées par un nombre complet, puis importées en tant que chaîne de texte, vous pouvez convertir cette chaîne en valeur date/heure en utilisant la formule suivante :

=DATE(DROITE([Valeur1],4),GAUCHE([Valeur1],2),MID([Valeur1],2))

Les rubriques suivantes fournissent des informations supplémentaires sur les fonctions utilisées pour extraire et composer des dates.

• Fonction DATEVAL

• FONCTION TEMPSVAL

• Fonction DATE

Définir un format de date ou de nombre personnalisé

Si vos données contiennent des dates ou des nombres qui ne sont représentés dans l’un des formats de texte Windows standard, vous pouvez définir un format personnalisé pour vous assurer que les valeurs sont gérées correctement. Ces formats sont utilisés lors de la conversion de valeurs en chaînes ou à partir de chaînes. Les rubriques suivantes fournissent également une liste détaillée des formats prédéfinés disponibles pour l’utilisation de dates et de nombres.

• Formats numériques prédéfini pour la fonction FORMAT

• Formats numériques personnalisés pour la fonction FORMAT

• Formats de date et d’heure prédéfiny pour la fonction FORMAT

• Formats de date et d’heure personnalisés pour la fonction FORMAT

Modifier les types de données à l’aide d’une formule

Dans Power Pivot, le type de données de la sortie est déterminé par les colonnes sources et vous ne pouvez pas spécifier explicitement le type de données du résultat, car le type de données optimal est déterminé par Power Pivot. Toutefois, vous pouvez utiliser les conversions de types de données implicites effectuées par Power Pivot pour manipuler le type de données de sortie. 

• Pour convertir une date ou une chaîne de nombres en nombre, multipliez par 1,0. Par exemple, la formule suivante calcule la date actuelle moins 3 jours, puis produit la valeur de l’année.

  =(AUJOURD’HUI()-3)*1,0

• Pour convertir une date, un nombre ou une valeur monétaire en chaîne, concaténer la valeur avec une chaîne vide. Par exemple, la formule suivante renvoie la date du jour en tant que chaîne.

  =""& AUJOURD’HUI()

Les fonctions suivantes peuvent également être utilisées pour s’assurer qu’un type de données particulier est renvoyé :

Convertir des nombres réels en nombres convertis en nombres

• Fonction ROUND

• PLAFOND, fonction

• PLANCHER, fonction

  Convertir des nombres réels, des nombres intéraux ou des dates en chaînes

• CTXT

• Fonction FORMAT

  Convertir des chaînes en nombres ou dates réels

• CTXT

• Fonction DATEVAL

• FONCTION TEMPSVAL

Créer une valeur sur la base d’une condition

Vous pouvez utiliser des conditions SI imbrmbrées pour tester des valeurs et générer de nouvelles valeurs de manière conditions. Les rubriques suivantes contiennent des exemples simples de traitement conditionnel et de valeurs conditionnelles :

• SI

• Fonction OR

• Fonction CONCATENER

Tester les erreurs dans une formule

Contrairement Excel, vous ne pouvez pas avoir de valeurs valides dans une ligne d’une colonne calculée et des valeurs non valides dans une autre ligne. Autrement dit, en cas d’erreur dans une partie d’une colonne Power Pivot, l’ensemble d’une colonne est signalée par une erreur, de sorte que vous devez toujours corriger les erreurs de formule qui entraînent des valeurs non valides.

Par exemple, si vous créez une formule qui divise par zéro, vous risquez d’obtenir le résultat infini, ou une erreur. Certaines formules peuvent également échouer si la fonction rencontre une valeur vide lorsqu’elle attend une valeur numérique. Lorsque vous développez votre modèle de données, il est préférable d’autoriser l’apparition des erreurs de façon à pouvoir cliquer sur le message et résoudre le problème. Toutefois, lorsque vous publiez des manuels, vous devez incorporer la gestion des erreurs pour éviter l’échec de valeurs inattendues.

Pour éviter de renvoyer des erreurs dans une colonne calculée, vous utilisez une combinaison de fonctions logiques et d’informations pour tester les erreurs et toujours renvoyer des valeurs valides. Les rubriques suivantes donnent des exemples simples de la façon de faire dans DAX :

• EstBLANK, fonction

• FONCTION SIERREUR

Calculer les ventes cumulées

Les rubriques suivantes contiennent des exemples de calcul des soldes de clôture et d’ouverture. Les exemples vous viennent vous aider à créer des soldes courants à différents intervalles (jours, mois, trimestres ou années).

• Fonction CLOSINGBALANCEMONTH, fonction CLOSINGBALANCEQUARTER, fonction CLOSINGBALANCEYEAR

• Fonction OPENINGBALANCEMONTH, fonction OPENINGBALANCEQUARTER, fonction OPENINGBALANCEYEAR

Comparer des valeurs au fil du temps

Les rubriques suivantes contiennent des exemples de comparaison des sommes sur différentes périodes. Les périodes de temps par défaut pris en charge par DAX sont les mois, trimestres et années.

• Fonction PREVIOUSMONTH, PREVIOUSQUARTER, Fonction PREVIOUSYEAR

• Fonction TOTALMTD, fonction TOTALQTD, fonction TOTALYTD

• Fonction PARALLELPERIOD

Calculer une valeur sur une plage de dates personnalisée

Pour obtenir des plages de dates personnalisées, par exemple, les 15 premiers jours après le début d’une promotion de ventes, voir les rubriques suivantes.

• DATESINPERIOD, fonction

• FONCTION DATESBETWEEN

• DATEADD, fonction

• FIRSTDATE, fonction

• Fonction LASTDATE

Si vous utilisez les fonctions Time Intelligence pour récupérer un ensemble de dates personnalisé, vous pouvez utiliser cet ensemble de dates comme entrée d’une fonction qui effectue des calculs afin de créer des agrégats personnalisés sur plusieurs périodes. Consultez la rubrique suivante pour obtenir un exemple de la façon de faire :

• Fonction PARALLELPERIOD

  Remarque : Si vous n’avez pas besoin de spécifier une plage de dates personnalisée, mais que vous utilisez des unités comptables standard telles que des mois, des trimestres ou des années, nous vous recommandons d’effectuer des calculs à l’aide des fonctions Time Intelligence conçues à cet effet, telles que TOTALQTD, TOTALMTD, TOTALQTD, etc.

Afficher uniquement les dix premiers éléments d’un tableau croisé dynamique

Commandez les éléments de façon dynamique à l’aide d’une formule

La rubrique suivante contient un exemple de l’utilisation de DAX pour créer un classement stocké dans une colonne calculée. Étant donné que les formules DAX sont calculées de façon dynamique, vous pouvez toujours vous assurer que le classement est correct, même si les données sous-jacentes ont changé. Par ailleurs, étant donné que la formule est utilisée dans une colonne calculée, vous pouvez utiliser le classement dans un slicer, puis sélectionner les 5, 10 premiers, voire les 100 premières valeurs.

• FONCTION EARLIER.