Compréhension des fonctions ORDERBY, PARTITIONBY et MATCHBY

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

Il est essentiel de comprendre ORDERBY, PARTITIONBY et MATCHBY pour utiliser correctement les fonctions Window. Si les exemples présentés ici utilisent la fonction OFFSET, ils peuvent s’appliquer de la même façon aux autres fonctions Window.

Scénario

Commençons par un exemple qui n’utilise pas du tout de fonctions Window. Vous trouverez ci-dessous une table qui retourne le total des ventes par couleur et par année civile. Il existe plusieurs façons de définir cette table, mais comme notre objectif est ici de comprendre ce qu’il se passe dans DAX, nous 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 somme (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 :

Color	CalendarYear	CurrentYearSales
"Black"	2017	393885
"Black"	2018	1818835
"Black"	2019	3981638
"Black"	2020	2644054
« Blue »	2019	994448
« Blue »	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Red"	2017	2961198
"Red"	2018	3686935
"Red"	2019	900175
"Red"	2020	176022
"Silver"	2017	326399
"Silver"	2018	750026
"Silver"	2019	2165176
"Silver"	2020	1871788
"White"	2019	2517
"White"	2020	2589
"Yellow"	2018	163071
"Yellow"	2019	2072083
"Yellow"	2020	2621602

Imaginons maintenant que nous cherchons à répondre à la question commerciale suivante : quelle a été la différence des ventes pour chaque couleur d’une année sur l’autre ? 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 avons ce chiffre, nous pouvons le soustraire des ventes actuelles et retourner la valeur souhaitée.

Utilisation de OFFSET

La fonction OFFSET est parfaite pour les calculs de comparaison types pour répondre à la question commerciale décrite ci-dessus, car elle nous permet d’effectuer un mouvement relatif. Notre première tentative pourrait être la suivante :

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

Il y a beaucoup à dire sur cette expression. Nous avons utilisé ADDCOLUMNS pour développer la table précédente 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 :

Color	CalendarYear	CurrentYearSales	PreviousColorSales
"Black"	2017	393885
"Black"	2018	1818835	393885
"Black"	2019	3981638	1818835
"Black"	2020	2644054	3981638
« Blue »	2019	994448	2644054
« Blue »	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Red"	2017	2961198	227295
"Red"	2018	3686935	2961198
"Red"	2019	900175	3686935
"Red"	2020	176022	900175
"Silver"	2017	326399	176022
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
"White"	2019	2517	1871788
"White"	2020	2589	2517
"Yellow"	2018	163071	2589
"Yellow"	2019	2072083	163071
"Yellow"	2020	2621602	2072083

C’est un pas de plus vers notre objectif, mais à y regarder de près, ce n’est pas tout à fait ce que nous voulons. Par exemple, pour [Silver, 2017], previousColorSales est défini sur [Red, 2020].

Ajout de ORDERBY

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

Si ces deux résultats sont équivalents, c’est parce que ORDERBY contient automatiquement toutes les colonnes de la relation qui ne se trouvent pas dans PARTITIONBY. Comme PARTITIONBY n’a pas été spécifié, ORDERBY est défini sur Color, CalendarYear et CurrentYearSales. Or, sachant que les paires Color et CalendarYear sont uniques dans la relation, l’ajout de CurrentYearSales ne change pas le résultat. En fait, même si nous devions spécifier uniquement Color dans ORDERBY, les résultats seraient les mêmes, car CalendarYear serait automatiquement ajouté. Cela est dû au fait que la fonction ajoute autant de colonnes que nécessaire à ORDERBY afin 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 obtenir pratiquement le résultat recherché, nous pouvons utiliser PARTITIONBY, comme dans l’expression de table calculée 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 de ORDERBY est ici facultative, 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 est défini automatiquement sur CalendarYear et CurrentYearSales :

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

Notes

Même si ORDERBY est automatiquement défini sur CalendarYear et CurrentYearSales, rien ne permet de dire dans quel l’ordre elles seront ajoutées. Si la colonne CurrentYearSales est ajoutée avant CalendarYear, l’ordre obtenu ne sera pas celui attendu. Pour éviter toute confusion et des résultats inattendus, spécifiez ORDERBY et PARTITIONBY de manière explicite.

Les deux expressions retournent le résultat qui nous voulions :

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Black"	2017	393885
"Black"	2018	1818835	393885
"Black"	2019	3981638	1818835
"Black"	2020	2644054	3981638
« Blue »	2019	994448
« Blue »	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Red"	2017	2961198
"Red"	2018	3686935	2961198
"Red"	2019	900175	3686935
"Red"	2020	176022	900175
"Silver"	2017	326399
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
"White"	2019	2517
"White"	2020	2589	2517
"Yellow"	2018	163071
"Yellow"	2019	2072083	163071
"Yellow"	2020	2621602	2072083

Comme vous pouvez le voir dans cette table, 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, comme c’est le cas pour [Red, 2017], aucune valeur n’est retournée.

Vous pouvez voir PARTITIONBY comme un moyen de diviser la table en différentes parties dans lesquelles exécuter le calcul de OFFSET. Dans l’exemple ci-dessus, la table est divisée en autant de parties qu’il y a de couleurs, une pour chaque couleur. Ensuite, dans chaque partie, OFFSET est calculé, avec un tri en fonction de CalendarYear.

Visuellement, voici ce qu’il se passe :

Tout d’abord, l’appel à PARTITIONBY a pour effet de diviser la table en différentes parties, une pour chaque couleur. Cela est représenté par les zones bleu clair dans l’image de la table. Ensuite, ORDERBY fait en sorte que chaque partie soit triée en fonction de la colonne CalendarYear (ce qui est représenté par les flèches orange). Enfin, dans chaque partie triée, OFFSET recherche la ligne précédente et retourne cette valeur dans la colonne PreviousYearSalesForSameColor. Comme aucune ligne ne précède la première de ligne de chaque partie, la colonne PreviousYearSalesForSameColor est vide pour cette ligne.

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. Comme nous ne sommes pas intéressés par l’affichage des ventes de l’année précédente pour la même couleur, mais par seulement les ventes de l’année en cours et la différence d’une année sur l’autre, 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]
        )
    )

Et voici le résultat de cette expression :

Color	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Black"	2017	393885	393885
"Black"	2018	1818835	1424950
"Black"	2019	3981638	2162803
"Black"	2020	2644054	-1337584
« Blue »	2019	994448	994448
« Blue »	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Red"	2017	2961198	2961198
"Red"	2018	3686935	725737
"Red"	2019	900175	-2786760
"Red"	2020	176022	-724153
"Silver"	2017	326399	326399
"Silver"	2018	750026	423627
"Silver"	2019	2165176	1415150
"Silver"	2020	1871788	-293388
"White"	2019	2517	2517
"White"	2020	2589	72
"Yellow"	2018	163071	163071
"Yellow"	2019	2072083	1909012
"Yellow"	2020	2621602	549519

Utilisation de MATCHBY

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

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

Dans la continuité des 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 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]
        )
    )

MATCHBY étant spécifié, les colonnes spécifiées à la fois dans MATCHBY et PARTITIONBY sont utilisées pour identifier les lignes de manière unique. Comme Color est spécifié à la fois dans 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]
        )
    )

Sachant qu’il n’était pas nécessaire de spécifier MATCHBY dans les exemples que nous avons vus jusqu’à présent, intéressons-nous à présent à 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 être constituée de plusieurs lignes de commande et la ligne de commande 1 apparaît sur de nombreuses commandes. Par ailleurs, pour chaque ligne de commande, nous avons un élément ProductKey (Clé de produit) et un élément SalesAmount (Montant de la vente). Voici un aperçu des colonnes importantes de la table :

SalesOrderNumber	SalesOrderLineNumber	ProductKey	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 deux nécessaires pour identifier les lignes de manière unique.

Pour chaque commande, nous voulons obtenir le montant de ventes précédent du même produit (représenté par ProductKey) commandé à hauteur de SalesAmount par ordre décroissant. L’expression suivante ne fonctionnera pas, car il peut exister plusieurs lignes dans vRelation au moment où ce dernier est passé à 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 indiquant que le paramètre Relation de OFFSET comportement potentiellement des lignes en double, ce qui n’est pas autorisé.

Pour que cette expression fonctionne, MATCHBY doit être spécifié et inclure toutes les colonnes qui définissent une ligne de manière unique. MATCHBY est nécessaire ici, car la relation, FactInternetSales, ne contient pas de clés explicites ni de colonnes uniques. Cependant, les colonnes SalesOrderNumber et SalesOrderLineNumber forment ensemble une clé composite, où leur existence conjointe est unique dans la relation et peut donc identifier chaque ligne de manière unique. Le simple fait de spécifier SalesOrderNumber ou SalesOrderLineNumber ne suffit pas, car les deux colonnes contiennent des valeurs qui se répètent. 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 qui nous intéressent :

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Montant de ventes précédent
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

Contenu connexe

ORDERBY
PARTITIONBY
MATCHBY
INDEX
DÉCALAGE
WINDOW
RANK
ROWNUMBER