Scénarios DAX dans Power Pivot

Cette section fournit des liens vers des exemples qui illustrent l’utilisation de formules DAX dans les scénarios suivants.

  • Effectuer des calculs complexes

  • Texte et dates

  • Valeurs conditionnelles et test des erreurs

  • Utilisation de Time Intelligence

  • Classement et comparaison de valeurs

Contenu de cet article

Prise en main

Visitez le Site Wiki du Centre de ressources DAX où vous pouvez trouver toutes sortes d’informations sur DAX, y compris des blogs, des exemples, des livre blanc et des vidéos fournis par des professionnels du secteur et Microsoft.

Scénarios : effectuer des calculs complexes

Les formules DAX peuvent effectuer des calculs complexes qui impliquent des agrégations personnalisées, un filtrage et l’utilisation de valeurs conditionnelles. Cette section fournit des exemples de la mise en place de calculs personnalisés.

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.

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.

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.

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

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.

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.

Scénarios : Travail avec du texte et des dates

Cette section fournit des liens vers des rubriques de référence DAX qui contiennent des exemples de scénarios courants impliquant l’utilisation de texte, l’extraction et la composition de valeurs de date et d’heure, ou la création de valeurs basées sur une condition.

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.

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

valeur1

Résultat

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

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

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.

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

Scénario : Valeurs conditionnelles et test des erreurs

Comme Excel, DAX dispose de fonctions qui vous permet de tester des valeurs dans les données et de renvoyer une valeur différente en fonction d’une condition. Par exemple, vous pouvez créer une colonne calculée qui étiquette les revendeurs comme valeur préférée ou valeur en fonction du montant des ventes annuelles. Les fonctions qui testent des valeurs sont également utiles pour vérifier la plage ou le type de valeurs, afin d’éviter que des erreurs de données inattendues ne se soient produits lors de la rupture des calculs.

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 :

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 :

Scénarios : Utilisation de Time Intelligence

Les fonctions Time Intelligence DAX incluent des fonctions pour vous aider à récupérer des dates ou des plages de dates à partir de vos données. Vous pouvez ensuite utiliser ces dates ou plages de dates pour calculer des valeurs sur des périodes similaires. Les fonctions Time Intelligence incluent également des fonctions qui fonctionnent avec des intervalles de date standard, pour vous permettre de comparer des valeurs sur des mois, des années ou des trimestres. Vous pouvez également créer une formule qui compare les valeurs de la première et de la dernière date d’une période spécifiée.

Pour obtenir la liste de toutes les fonctions Time Intelligence, voir Fonctions Time Intelligence (DAX). Pour obtenir des conseils sur l’utilisation efficace de dates et d’heures dans Power Pivot d’analyse, voir Dates dans Power Pivot.

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

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.

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.

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.

Scénarios : Classement et comparaison de valeurs

Pour afficher uniquement le n premier nombre d’éléments d’une colonne ou d’un tableau croisé dynamique, plusieurs options s’offrent à vous :

  • Vous pouvez utiliser les fonctionnalités de Excel 2010 pour créer un filtre top. Vous pouvez également sélectionner plusieurs valeurs hautes ou inférieures dans un tableau croisé dynamique. La première partie de cette section décrit comment filtrer les 10 premiers éléments d’un tableau croisé dynamique. Pour plus d’informations, consultez la Excel documents.

  • Vous pouvez créer une formule qui calcule le classement dynamique des valeurs, puis filtrer selon les valeurs du classement ou utiliser la valeur de classement en tant que slicer. La deuxième partie de cette section explique comment créer cette formule et utiliser ce classement dans un slicer.

Chaque méthode in avantages et inconvénients.

  • Le Excel filtre supérieur est facile à utiliser, mais le filtre est uniquement à des fins d’affichage. Si les données sous-jacentes au tableau croisé dynamique changent, vous devez actualiser le tableau croisé dynamique manuellement pour voir les modifications. Si vous devez travailler de façon dynamique avec des classements, vous pouvez utiliser DAX pour créer une formule qui compare des valeurs avec d’autres valeurs au sein d’une colonne.

  • La formule DAX est plus puissante . de plus, en ajoutant la valeur de classement à un slicer, vous pouvez simplement cliquer sur le slicer pour modifier le nombre de valeurs les plus élevées affichées. Toutefois, les calculs sont coûteux et cette méthode peut ne pas être adaptée aux tables de nombreuses lignes.

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

Pour afficher les valeurs inférieures ou les plus hautes dans un tableau croisé dynamique

  1. Dans le tableau croisé dynamique, cliquez sur la flèche vers le bas dans l’en-tête Étiquettes de lignes.

  2. Sélectionnez Filtres de> les 10 premiers.

  3. Dans la boîte de dialogue Filtrer <10 premières colonnes,>la colonne à classer et le nombre de valeurs, comme suit :

    1. Sélectionnez Haut pour voir les cellules avec les valeurs les plus élevées ou inférieures pour voir les cellules avec les valeurs les plus basses.

    2. Tapez le nombre de valeurs les plus ou moins hautes que vous souhaitez voir. La valeur par défaut est 10.

    3. Sélectionnez la façon dont les valeurs sont affichées :

Nom

Description

Éléments

Sélectionnez cette option pour filtrer le tableau croisé dynamique afin d’afficher uniquement la liste des éléments les plus ou moins importants selon leurs valeurs.

Pourcentage

Sélectionnez cette option pour filtrer le tableau croisé dynamique afin d’afficher uniquement les éléments qui additionnant le pourcentage spécifié.

Somme

Sélectionnez cette option pour afficher la somme des valeurs des éléments les plus ou moins importants.

  1. Sélectionnez la colonne qui contient les valeurs à classer.

  2. Cliquez sur OK.

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.

Besoin d’aide ?

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

×