Présentation des fonctions ORDERBY, PARTITIONBYet MATCHBY

2025-04-22

Les fonctions ORDERBY, PARTITIONBY, et MATCHBY dans DAX sont des fonctions spéciales qui ne peuvent être utilisées qu’avec les Window fonctions DAX : INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Comprendre ORDERBY, PARTITIONBYet MATCHBY est essentiel pour utiliser correctement les fonctions Window. Les exemples fournis ici utilisent OFFSET, mais s’appliquent de la même façon aux autres fonctions Window.

Scénario

Commençons par un exemple qui n’utilise pas du tout les fonctions Window. Voici un tableau qui retourne le total des ventes, par couleur, par année civile. Il existe plusieurs façons de définir cette table, mais étant donné que nous sommes intéressés à comprendre ce qui se passe, DAXnous allons utiliser une table calculée. Voici l’expression de table :

BasicTable = 
    SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
    )

Vous verrez que cette expression de table calculée utilise SUMMARIZECOLUMNS pour calculer la SUM de la colonne SalesAmount de la table FactInternetSales, en fonction de la colonne Color de la table DimProduct et de la colonne CalendarYear de la table DimDate. Voici le résultat :

Couleur	CalendarYear	CurrentYearSales
« Noir »	2017	393885
« Noir »	2018	1818835
« Noir »	2019	3981638
« Noir »	2020	2644054
« Bleu »	2019	994448
« Bleu »	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
« NA »	2019	207822
« NA »	2020	227295
« Rouge »	2017	2961198
« Rouge »	2018	3686935
« Rouge »	2019	900175
« Rouge »	2020	176022
« Silver »	2017	326399
« Silver »	2018	750026
« Silver »	2019	2165176
« Silver »	2020	1871788
« Blanc »	2019	2517
« Blanc »	2020	2589
« Jaune »	2018	163071
« Jaune »	2019	2072083
« Jaune »	2020	2621602

À présent, imaginons que nous essayons de résoudre la question commerciale de calculer la différence entre les ventes, l’année sur l’année pour chaque couleur. En fait, nous devons trouver un moyen de calculer les ventes de l’année précédente pour une même couleur et de soustraire ces chiffres des ventes de l’année en cours, en contexte. Par exemple, pour la combinaison [Red, 2019], nous recherchons les ventes pour [Red, 2018]. Une fois que nous l’avons, nous pouvons ensuite la soustraire des ventes actuelles et retourner la valeur requise.

Utilisation de OFFSET

OFFSET est parfait pour la comparaison classique avec les types de calculs précédents requis pour répondre à la question métier décrite ci-dessus, car il nous permet d’effectuer un mouvement relatif. Notre première tentative peut être :

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

Beaucoup de choses se produisent avec cette expression. Nous avons utilisé ADDCOLUMNS pour développer la table d’avant avec une colonne appelée PreviousColorSales. Le contenu de cette colonne est défini sur CurrentYearSales, qui correspond à SUM(FactInternetSales[SalesAmount]), pour la couleur précédente (extraite avec OFFSET).

Le résultat est le suivant :

Couleur	CalendarYear	CurrentYearSales	PreviousColorSales
« Noir »	2017	393885
« Noir »	2018	1818835	393885
« Noir »	2019	3981638	1818835
« Noir »	2020	2644054	3981638
« Bleu »	2019	994448	2644054
« Bleu »	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
« NA »	2019	207822	57849
« NA »	2020	227295	207822
« Rouge »	2017	2961198	227295
« Rouge »	2018	3686935	2961198
« Rouge »	2019	900175	3686935
« Rouge »	2020	176022	900175
« Silver »	2017	326399	176022
« Silver »	2018	750026	326399
« Silver »	2019	2165176	750026
« Silver »	2020	1871788	2165176
« Blanc »	2019	2517	1871788
« Blanc »	2020	2589	2517
« Jaune »	2018	163071	2589
« Jaune »	2019	2072083	163071
« Jaune »	2020	2621602	2072083

Il s’agit d’une étape plus proche de notre objectif, mais si nous regardons de près, cela ne correspond pas exactement à ce que nous sommes après. Par exemple, pour [Silver, 2017] PreviousColorSales est fixé à [Rouge, 2020].

Ajout de ORDERBY

Cette définition ci-dessus équivaut à :

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color], ASC, [CalendarYear], ASC, [CurrentYearSales], ASC)
            ),
            [CurrentYearSales]
        )
    )

Dans ce cas, l’appel à OFFSET utilise ORDERBY pour organiser la table en fonction de Color et CalendarYear par ordre croissant, ce qui détermine ce qui est considéré comme étant la ligne précédente retournée.

La raison pour laquelle ces deux résultats sont équivalents est parce que ORDERBY contient automatiquement toutes les colonnes de la relation qui ne sont pas dans PARTITIONBY. Étant donné que PARTITIONBY n’a pas été spécifié, ORDERBY est défini sur les valeurs de Color, CalendarYear et CurrentYearSales. Toutefois, étant donné que les paires Color et CalendarYear dans la relation sont uniques, l’ajout de CurrentYearSales ne modifie pas le résultat. En fait, même si nous étions à spécifier uniquement La couleur dans ORDERBY, les résultats sont identiques, car CalendarYear serait automatiquement ajouté. Cela est dû au fait que la fonction ajoute autant de colonnes que nécessaire dans ORDERBY pour s’assurer que chaque ligne puisse être identifiée de manière unique par les colonnes ORDERBY et PARTITIONBY.

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS(
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Ajout de PARTITIONBY

Maintenant, pour presque obtenir le résultat que nous recherchons, nous pouvons utiliser PARTITIONBY, comme illustré dans l'expression de tableau calculé suivante :

UsingPARTITIONBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]), 
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Notez que la spécification ORDERBY est facultative ici, car ORDERBY contient automatiquement toutes les colonnes de la relation qui ne sont pas spécifiées dans PARTITIONBY. Par conséquent, l’expression suivante retourne les mêmes résultats, car ORDERBY elle est définie sur CalendarYear et CurrentYearSales automatiquement :

UsingPARTITIONBYWithoutORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Remarque

Bien que ORDERBY soit automatiquement défini sur CalendarYear et CurrentYearSales, aucune garantie n'est donnée quant à l'ordre dans lequel ils seront ajoutés. Si CurrentYearSales est ajouté avant CalendarYear, l’ordre résultant n’est pas inline avec ce qui est attendu. Soyez explicite lors de la ORDERBY spécification et PARTITIONBY pour éviter la confusion et les résultats inattendus.

Les deux expressions retournent le résultat suivant :

Couleur	Année civile	CurrentYearSales	PreviousYearSalesForSameColor
« Noir »	2017	393885
« Noir »	2018	1818835	393885
« Noir »	2019	3981638	1818835
« Noir »	2020	2644054	3981638
« Bleu »	2019	994448
« Bleu »	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
« NA »	2019	207822
« NA »	2020	227295	207822
« Rouge »	2017	2961198
« Rouge »	2018	3686935	2961198
« Rouge »	2019	900175	3686935
« Rouge »	2020	176022	900175
« Silver »	2017	326399
« Silver »	2018	750026	326399
« Silver »	2019	2165176	750026
« Silver »	2020	1871788	2165176
« Blanc »	2019	2517
« Blanc »	2020	2589	2517
« Jaune »	2018	163071
« Jaune »	2019	2072083	163071
« Jaune »	2020	2621602	2072083

Comme vous le voyez dans ce tableau, la colonne PreviousYearSalesForSameColor affiche les ventes de l’année précédente pour la même couleur. Pour [Red, 2020], elle retourne les ventes pour [Red, 2019], et ainsi de suite. S’il n’y a pas d’année précédente, par exemple dans le cas de [Rouge, 2017], aucune valeur n’est retournée.

Vous pouvez considérer PARTITIONBY comme un moyen de diviser la table en parties pour exécuter le calcul OFFSET. Dans l’exemple ci-dessus, le tableau est divisé en autant de parties qu’il y a de couleurs, une pour chaque couleur. Ensuite, dans chaque partie, la valeur de OFFSET est calculée et triée par année calendaire.

Visuellement, ce qui se passe est le suivant :

Tout d'abord, l'appel à PARTITIONBY a pour conséquence que la table est divisée en parties, une pour chaque Couleur. Ceci est représenté par les zones bleues claires dans l’image de tableau. Ensuite, ORDERBY assure que chaque partie est triée par CalendarYear (représentée par les flèches oranges). Enfin, dans chaque partie triée, pour chaque ligne, OFFSET recherche la ligne au-dessus de celle-ci et retourne cette valeur dans la colonne PreviousYearSalesForSameColor. Étant donné que pour chaque première ligne de chaque partie il n’y a pas de ligne précédente dans cette même partie, le résultat de cette ligne pour la colonne PreviousYearSalesForSameColor est vide.

Pour obtenir le résultat final, il nous suffit de soustraire CurrentYearSales des ventes de l’année précédente pour la même couleur retournées par l’appel à OFFSET. Étant donné que nous ne sommes pas intéressés à afficher les ventes de l’année précédente pour la même couleur, mais uniquement dans les ventes de l'année en cours, puis la variation par rapport à l’année précédente. Voici l’expression de table calculée finale :

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Voici le résultat de cette expression :

Couleur	Année Calendaire	CurrentYearSales	YoYSalesForSameColor
« Noir »	2017	393885	393885
« Noir »	2018	1818835	1424950
« Noir »	2019	3981638	2162803
« Noir »	2020	2644054	-1337584
« Bleu »	2019	994448	994448
« Bleu »	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
« NA »	2019	207822	207822
« NA »	2020	227295	19473
« Rouge »	2017	2961198	2961198
« Rouge »	2018	3686935	725737
« Rouge »	2019	900175	-2786760
« Rouge »	2020	176022	-724153
« Silver »	2017	326399	326399
« Silver »	2018	750026	423627
« Silver »	2019	2165176	1415150
« Silver »	2020	1871788	-293388
« Blanc »	2019	2517	2517
« Blanc »	2020	2589	72
« Jaune »	2018	163071	163071
« Jaune »	2019	2072083	1909012
« Jaune »	2020	2621602	549519

Utilisation de MATCHBY

Vous avez peut-être remarqué que nous n’avons pas spécifié MATCHBY du tout. Dans ce cas, il n’est pas nécessaire. Les colonnes dans ORDERBY et PARTITIONBY (pour autant qu’elles ont été spécifiées dans les exemples ci-dessus) sont suffisantes pour identifier de manière unique chaque ligne. Étant donné que nous n’avons pas spécifié MATCHBY, les colonnes spécifiées et ORDERBYPARTITIONBY sont utilisées pour identifier chaque ligne de manière unique afin qu’elles puissent être comparées pour permettre de donner OFFSET un résultat significatif. Si les colonnes dans ORDERBY et PARTITIONBY ne peuvent pas identifier chaque ligne de manière unique, des colonnes supplémentaires peuvent être ajoutées à la ORDERBY clause si ces colonnes supplémentaires permettent à chaque ligne d’être identifiée de manière unique. Si cela n’est pas possible, une erreur est retournée. Dans ce dernier cas, la spécification MATCHBY peut aider à résoudre l’erreur.

Si MATCHBY est spécifié, les colonnes dans MATCHBY et PARTITIONBY sont utilisées pour identifier de manière unique chaque ligne. Si cela n’est pas possible, une erreur est retournée. Même s’il MATCHBY n’est pas nécessaire, envisagez de spécifier MATCHBY explicitement pour éviter toute confusion.

En suivant les exemples ci-dessus, voici la dernière expression :

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Si nous voulons être explicites sur la façon dont les lignes doivent être identifiées de manière unique, nous pouvons spécifier MATCHBY comme indiqué dans l’expression équivalente suivante :

FinalResultWithExplicitMATCHBYOnColorAndCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([Color], [CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

Étant donné que MATCHBY est spécifié, les colonnes indiquées dans MATCHBY ainsi que celles dans PARTITIONBY sont utilisées pour identifier de manière unique les lignes. Étant donné que Color est spécifié dans les deux MATCHBY et PARTITIONBY, l’expression suivante est équivalente à l’expression précédente :

FinalResultWithExplicitMATCHBYOnCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

Étant donné que la spécification MATCHBY n’est pas nécessaire dans les exemples que nous avons examinés jusqu’à présent, examinons un exemple légèrement différent qui nécessite MATCHBY. Dans ce cas, nous avons une liste de lignes de commande. Chaque ligne représente une ligne de commande pour une commande. Une commande peut avoir plusieurs lignes de commande et la ligne de commande 1 apparaît sur de nombreuses commandes. En outre, pour chaque ligne de commande, nous avons une ProductKey et un SalesAmount. Un exemple de colonnes pertinentes sur la table ressemble à ceci :

Numéro de commande de vente	SalesOrderLineNumber	Clé de produit	SalesAmount
SO51900	1	528	4.99
SO51948	1	528	5,99
SO52043	1	528	4,99
SO52045	1	528	4.99
SO52094	1	528	4.99
SO52175	1	528	4.99
SO52190	1	528	4,99
SO52232	1	528	4,99
SO52234	1	528	4,99 €
SO52234	2	529	3.99

Notez que SalesOrderNumber et SalesOrderLineNumber sont tous les deux nécessaires pour identifier de manière unique les lignes.

Pour chaque commande, nous voulons retourner le montant des ventes précédentes du même produit (représenté par productKey) commandé par salesAmount dans l’ordre décroissant. L’expression suivante ne fonctionne pas, car il existe potentiellement plusieurs lignes dans vRelation, car elle est passée dans OFFSET:

ThisExpressionFailsBecauseMATCHBYIsMissing = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

Cette expression retourne une erreur : «OFFSET Le paramètre Relation peut avoir des lignes dupliquées, ce qui n’est pas autorisé ».

Pour que cette expression fonctionne, MATCHBY elle doit être spécifiée et doit inclure toutes les colonnes qui définissent de façon unique une ligne. MATCHBY est requis ici, car la relation, FactInternetSales, ne contient aucune clé explicite ni colonnes uniques. Toutefois, les colonnes SalesOrderNumber et SalesOrderLineNumber forment ensemble une clé composite, où leur existence est unique dans la relation et peut donc identifier chaque ligne de manière unique. Il ne suffit pas de spécifier SalesOrderNumber ou SalesOrderLineNumber, car les deux colonnes contiennent des valeurs répétées. L’expression suivante résout le problème :

ThisExpressionWorksBecauseOfMATCHBY = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] ),
                    MATCHBY ( FactInternetSales[SalesOrderNumber], 
                                FactInternetSales[SalesOrderLineNumber] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

Et cette expression retourne en effet les résultats que nous recherchons.

SalesOrderNumber	SalesOrderLineNumber	Clé de produit	Montant des ventes	Montant des ventes précédents
SO51900	1	528	5,99
SO51948	1	528	4.99	5,99
SO52043	1	528	4,99	4,99
SO52045	1	528	4.99	4.99
SO52094	1	528	4,99	4,99
SO52175	1	528	4.99	4.99
SO52190	1	528	4.99	4,99
SO52232	1	528	4,99	4,99
SO52234	1	528	4,99 €	4.99
SO52234	2	529	3.99

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER

Partager via

Présentation des fonctions ORDERBY, PARTITIONBYet MATCHBY

Scénario

Utilisation de OFFSET

Ajout de ORDERBY

Ajout de PARTITIONBY

Utilisation de MATCHBY

Contenu connexe

Commentaires

Ressources supplémentaires