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 permettent de modifier le contexte dans lequel le calcul sera effectué. Vous pouvez également personnaliser le type d’agrégation ou d’opération mathématique à effectuer. Pour obtenir des exemples, consultez les rubriques suivantes.

• CALCULER

• CALCULATETABLE

Appliquer un filtre à une formule

Dans la plupart des cas où une fonction DAX prend une table comme argument, vous pouvez généralement passer une table filtrée à la place, soit en utilisant la fonction FILTER au lieu du nom de la table, soit en spécifiant une expression de filtre comme l’un des arguments de fonction. Les rubriques suivantes fournissent des exemples de création de filtres et d’impact sur les résultats des formules. Pour plus d’informations, consultez Filtrer les données dans les formules DAX.

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

• FILTRE

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

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

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

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

• Combien cette division a-t-elle contribué aux bénéfices totaux pour toutes les années d’exploitation, comparativement 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 modifier de manière sélective le contexte en ajoutant ou en supprimant des filtres. L’exemple de la rubrique ALL vous montre comment procéder. Pour trouver le ratio 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ée par la valeur du contexte ALL.

La rubrique ALLEXCEPT fournit un exemple d’effacement sélectif des filtres sur une formule. Les deux exemples vous guident dans la façon dont les résultats changent en fonction de la conception du tableau croisé dynamique.

• TOUT

• ALLEXCEPT

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

• ISBLANK

• ISERROR

Utilisation d’une valeur à partir d’une boucle externe

En plus d’utiliser des valeurs du contexte actuel dans les calculs, DAX peut utiliser une valeur d’une boucle précédente pour créer un ensemble de calculs associés. La rubrique suivante fournit une procédure pas à pas montrant comment générer une formule qui référence une valeur à partir d’une boucle externe. La fonction EARLIER prend en charge jusqu’à deux niveaux de boucles imbriquées.

• FONCTION EARLIER

Pour en savoir plus sur le contexte de ligne et les tables associées et sur l’utilisation de ce concept dans les formules, consultez Contexte dans les formules DAX.

Créer une colonne clé par concaténation

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 de clé. La rubrique suivante fournit un exemple de création d’une colonne calculée basée sur une clé composite.

• CONCATENATE, fonction

Composer une date en fonction des parties de date extraites d’une date de texte

Power Pivot utilise un type de données date/heure SQL Server pour utiliser des dates ; Par conséquent, si vos données externes contiennent des dates qui sont mises en forme différemment (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 entières), vous devrez peut-être utiliser une formule DAX pour extraire les parties de date, puis composer les parties dans une représentation de date/heure valide.

Par exemple, si vous avez une colonne de dates qui ont été représentées sous forme d’entier, puis importées en tant que chaîne de texte, vous pouvez convertir la chaîne en valeur de date/heure à l’aide de la formule suivante :

=DATE(RIGHT([Valeur1],4),LEFT([Valeur1],2),MID([Valeur1],2))

Les rubriques suivantes fournissent plus d’informations sur les fonctions utilisées pour extraire et composer des dates.

• DATEVALUE, fonction

• TIMEVALUE, fonction

• DATE, fonction

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

Si vos données contiennent des dates ou des nombres qui ne sont pas 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éfinis disponibles pour l’utilisation des dates et des nombres.

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

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

• Formats de date et d’heure prédéfinis 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 implicites de type de données effectuées par Power Pivot pour manipuler le type de données de sortie. 

• Pour convertir une date ou une chaîne numérique en nombre, multipliez par 1,0. Par exemple, la formule suivante calcule la date actuelle moins 3 jours, puis génère la valeur entière correspondante.

  =(TODAY()-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 retourne la date du jour sous forme de chaîne.

  =""& TODAY()

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

Convertir des nombres réels en entiers

• ROUND, fonction

• FONCTION CEILING

• FLOOR, fonction

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

• FIXED, fonction

• FORMAT, fonction

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

• VALUE, fonction

• DATEVALUE, fonction

• TIMEVALUE, fonction

Créer une valeur basée sur une condition

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

• SI

• OR, fonction

• CONCATENATE, fonction

Tester les erreurs au sein d’une formule

Contrairement à Excel, vous ne pouvez pas avoir de valeurs valides dans une ligne d’une colonne calculée et de valeurs non valides dans une autre ligne. Autrement dit, s’il y a une erreur dans une partie d’une colonne Power Pivot, la colonne entière est marquée d’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 pouvez obtenir le résultat infini ou une erreur. Certaines formules échouent également si la fonction rencontre une valeur vide lorsqu’elle attend une valeur numérique. Pendant que vous développez votre modèle de données, il est préférable d’autoriser les erreurs à apparaître afin que vous puissiez cliquer sur le message et résoudre le problème. Toutefois, lorsque vous publiez des classeurs, vous devez incorporer la gestion des erreurs pour éviter que des valeurs inattendues ne provoquent l’échec des calculs.

Pour éviter de retourner des erreurs dans une colonne calculée, vous utilisez une combinaison de fonctions logiques et d’informations pour tester les erreurs et retourner toujours des valeurs valides. Les rubriques suivantes fournissent quelques exemples simples de la procédure à suivre dans DAX :

• FONCTION ISBLANK

• Fonction IFERROR

Calculer les ventes cumulées

Les rubriques suivantes contiennent des exemples de calcul des soldes de fermeture et d’ouverture. Les exemples vous permettent de créer des soldes en cours d’exécution sur différents intervalles tels que des jours, des mois, des trimestres ou des années.

• FONCTION CLOSINGBALANCEMONTH, FONCTION CLOSINGBALANCEQUARTER, FONCTION CLOSINGBALANCEYEAR

• Fonction OPENINGBALANCEMONTH, fonction OPENINGBALANCEQUARTER, fonction OPENINGBALANCEYEAR

Comparer les valeurs au fil du temps

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

• PREVIOUSMONTH, fonction, PREVIOUSQUARTER, PREVIOUSYEAR, fonction

• Fonction TOTALMTD, fonction TOTALQTD, fonction TOTALYTD

• PARALLELPERIOD, fonction

Calculer une valeur sur une plage de dates personnalisée

Consultez les rubriques suivantes pour obtenir des exemples de récupération de plages de dates personnalisées, telles que les 15 premiers jours après le début d’une promotion commerciale.

• DATESINPERIOD, fonction

• DATESBETWEEN, fonction

• DATEADD, fonction

• FIRSTDATE, fonction

• LASTDATE, fonction

Si vous utilisez des fonctions Time Intelligence pour récupérer un ensemble personnalisé de dates, vous pouvez utiliser ce jeu de dates comme entrée dans une fonction qui effectue des calculs, afin de créer des agrégats personnalisés sur des périodes. Consultez la rubrique suivante pour obtenir un exemple de procédure à suivre :

• PARALLELPERIOD, fonction

  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 principaux éléments d’un tableau croisé dynamique

Trier les éléments dynamiquement à l’aide d’une formule

La rubrique suivante contient un exemple d’utilisation de DAX pour créer un classement stocké dans une colonne calculée. Étant donné que les formules DAX sont calculées dynamiquement, vous pouvez toujours être sûr que le classement est correct même si les données sous-jacentes ont changé. En outre, étant donné que la formule est utilisée dans une colonne calculée, vous pouvez utiliser le classement dans un segment, puis sélectionner les 5 premières, les 10 premières ou même les 100 premières valeurs.

• Fonction EARLIER.