Filtrer des données dans des formules
Vous pouvez créer des filtres dans des formules afin de limiter les valeurs des données sources qui seront utilisées dans les calculs. Pour ce faire, vous devez spécifier une table que la formule prendra en entrée, puis définir une expression de filtre. L'expression de filtre que vous fournissez sert à interroger les données et à ne retourner qu'un sous-ensemble des données sources. Le filtre est appliqué de façon dynamique chaque fois que vous mettez à jour les résultats de la formule, selon le contexte actuel de vos données. Cette section explique comment créer des filtres dans des formules DAX (Data Analysis Expressions).
Création d'un filtre sur une table utilisée dans une formule
Vous pouvez appliquer des filtres dans les formules qui prennent une table en entrée. Au lieu d'entrer un nom de table, vous utilisez la fonction FILTER pour définir un sous-ensemble de lignes de la table spécifiée. Ce sous-ensemble est ensuite passé à une autre fonction pour les opérations telles que les agrégations personnalisées.
Par exemple, supposons que vous ayez une table des données contenant des informations de commande pour les revendeurs, et que vous souhaitiez calculer le montant des ventes de chaque revendeur. Toutefois, vous souhaitez afficher le montant des ventes uniquement pour les revendeurs qui ont vendu plusieurs unités de vos produits les plus chers. La formule suivante, reposant sur l'exemple de classeur DAX, illustre une façon de créer ce calcul à l'aide d'un filtre :
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
La première partie de la formule spécifie l'une des fonctions d'agrégation PowerPivot, qui prend une table comme argument. SUMX calcule une somme sur une table.
La deuxième partie de la formule, FILTER(table, expression), indique à SUMX quelles données utiliser. SUMX requiert une table ou une expression qui donne une table. Ici, au lieu d'utiliser toutes les données de la table, vous utilisez la fonction FILTER pour spécifier quelles lignes de la table sont utilisées.
L'expression de filtre est composée de deux parties : la première nomme la table à laquelle le filtre s'applique. La seconde définit une expression à utiliser comme condition de filtre. Dans le cas présent, vous filtrez les revendeurs qui ont vendu plus de 5 unités et les produits qui coûtent plus de 100 $. L'opérateur, &&, est un opérateur AND logique, qui indique que les deux parties de la condition doivent être vraies pour la ligne qui appartient au sous-ensemble filtré.
La troisième partie de la formule indique à la fonction SUMX quelles valeurs doivent être additionnées. Dans le cas présent, vous utilisez juste le montant des ventes.
Notez que les fonctions telles que FILTER, qui retournent une table, ne retournent jamais directement la table ni les lignes au classeur PowerPivot, mais sont toujours incorporées dans une autre fonction. Pour plus d'informations sur FILTER et d'autres fonctions utilisées pour le filtrage, et obtenir des exemples supplémentaires, consultez Fonctions de filtrage (DAX).
[!REMARQUE]
L'expression de filtre est affectée par le contexte dans lequel elle est utilisée. Par exemple, si vous utilisez un filtre dans une mesure, et que cette mesure est utilisée dans un tableau croisé dynamique ou un graphique croisé dynamique, le sous-ensemble de données retourné peut être affecté par des filtres supplémentaires ou des segments appliqués par l'utilisateur dans le tableau croisé dynamique. Pour plus d'informations sur le contexte, consultez Contexte dans les formules DAX.
Filtres qui suppriment des doublons
Outre le filtrage visant à obtenir des valeurs spécifiques, vous pouvez retourner un jeu unique de valeurs d'une autre table ou colonne. Cela peut être utile lorsque vous souhaitez compter le nombre de valeurs uniques dans une colonne ou utiliser une liste de valeurs uniques pour d'autres opérations. DAX fournit deux fonctions qui permettent de retourner des valeurs distinctes : Fonction DISTINCT et Fonction VALUES.
La fonction DISTINCT examine une colonne unique que vous spécifiez comme argument de la fonction et retourne une nouvelle colonne ne contenant que les valeurs distinctes.
La fonction VALUES retourne également une liste de valeurs uniques, mais retourne aussi le membre inconnu. Cela peut être utile lorsque vous utilisez des valeurs provenant de deux tables jointes par une relation et qu'une valeur est absente dans une table, mais présente dans l'autre. Pour plus d'informations sur le membre inconnu, consultez Contexte dans les formules DAX.
Ces deux fonctions retournent une colonne entière de valeurs. Par conséquent, vous utilisez ces fonctions pour obtenir une liste de valeurs qui est ensuite passée à une autre fonction. Par exemple, vous pouvez utiliser la formule suivante pour obtenir une liste des différents produits vendus par un revendeur particulier, en utilisant la clé de produit unique, puis compter les produits de cette liste à l'aide de la fonction COUNTROWS :
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Incidence du contexte sur les filtres
Lorsque vous ajoutez une formule DAX à un tableau croisé dynamique ou un graphique croisé dynamique, les résultats de la formule peuvent être affectés par le contexte. Si vous travaillez dans une table PowerPivot, le contexte est la ligne actuelle et ses valeurs. Si vous travaillez dans un tableau croisé dynamique ou un graphique croisé dynamique, le contexte est l'ensemble ou le sous-ensemble de données défini par des opérations telles que le découpage ou le filtrage. La conception du tableau croisé dynamique ou du graphique croisé dynamique impose également son propre contexte. Par exemple, si vous créez un tableau croisé dynamique qui regroupe des ventes par région et par année, seules les données qui concernent ces régions et années s'affichent dans le tableau croisé dynamique. Par conséquent, toutes les mesures que vous ajoutez au tableau croisé dynamique sont calculées dans le contexte des en-têtes de ligne et de colonne et dans le cadre de tous les filtres définis dans la formule de mesure.
Pour plus d'informations, consultez Contexte dans les formules DAX.
Suppression de filtres
Lorsque vous utilisez des formules complexes, vous pouvez avoir besoin de connaître exactement les filtres actuels ou de modifier la partie filtre de la formule. DAX fournit plusieurs fonctions qui vous permettent de supprimer des filtres et de contrôler les colonnes à conserver dans le cadre du contexte de filtre actuel. Cette section propose une vue d'ensemble de l'incidence de ces fonctions sur les résultats d'une formule.
Remplacement de tous les filtres par la fonction ALL
Vous pouvez utiliser la fonction ALL pour remplacer tous les filtres précédemment appliqués et retourner toutes les lignes de la table à la fonction qui effectue l'agrégation ou une autre opération. Si vous utilisez une ou plusieurs colonnes, au lieu d'une table, comme arguments de la fonction ALL, la fonction ALL retourne toutes les lignes, en ignorant tous filtres de contexte.
[!REMARQUE]
Si vous connaissez la terminologie des bases de données relationnelles, vous pouvez considérer que la fonction ALL génère la jointure externe gauche naturelle de toutes les tables.
Par exemple, supposons que vous disposiez des tables Sales et Products, et que vous souhaitiez créer une formule qui calcule la somme des ventes réalisées pour le produit actuel, divisée par les ventes réalisées pour tous les produits. Vous devez tenir compte du fait que, si la formule est utilisée dans une mesure, l'utilisateur du tableau croisé dynamique peut utiliser un segment pour effectuer un filtrage en fonction d'une produit spécifique, avec le nom du produit sur les lignes. Par conséquent, pour obtenir la valeur réelle du dénominateur quels que soient les filtres ou segments appliqués, vous devez ajouter la fonction ALL pour remplacer tous les filtres. La formule suivante est un exemple d'utilisation de ALL pour remplacer les effets des filtres précédents :
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
La première partie de la formule, SUM (Sales[Amount]), calcule le numérateur.
La somme prend en considération le contexte actuel, ce qui signifie que si vous ajoutez la formule dans une colonne calculée, le contexte de ligne est appliqué, et que si vous ajoutez la formule dans un tableau croisé dynamique en tant que mesure, tous les filtres appliqués dans le tableau croisé dynamique (contexte de filtre) sont appliqués.
La deuxième partie de la formule calcule le dénominateur. La fonction ALL remplace tous les filtres éventuellement appliqués à la table Products.
Pour plus d'informations, y compris des exemples détaillés, consultez Fonction ALL.
Remplacement de filtres spécifiques par la fonction ALLEXCEPT
La fonction ALLEXCEPT remplace également des filtres existants, mais vous permet de spécifier que certains doivent être conservés. Les colonnes que vous transmettez en tant qu'arguments à la fonction ALLEXCEPT spécifient les colonnes qui resteront filtrées. Si vous souhaitez remplacer les filtres de la plupart des colonnes mais pas tous, ALLEXCEPT est plus pratique que ALL. La fonction ALLEXCEPT se révèle particulièrement utile lorsque vous créez des tableaux croisés dynamiques qui peuvent être filtrés sur de nombreuses colonnes, et que vous souhaitez contrôler les valeurs utilisées dans la formule. Pour plus d'informations et un exemple détaillé sur l'utilisation de ALLEXCEPT dans un tableau croisé dynamique, consultez Fonction ALLEXCEPT.