Informazioni sulle funzioni ORDERBY, PARTITIONBY, andMATCHBY

Le funzioni ORDERBY, PARTITIONBY, andMATCHBY in DAX sono funzioni speciali che possono essere usate solo insieme alle funzioni di DAXWindow: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Comprendere ORDERBY, PARTITIONBY, andMATCHBY è fondamentale per usare correttamente le funzioni di Window. Gli esempi forniti qui usano OFFSET, ma sono applicabili in modo analogo alle altre funzioni Window.

Scenario

Iniziamo con un esempio che non usa funzioni Window in all. Di seguito è riportata una tabella che restituisce le vendite totali, per colore, per calendaryear. Esistono diversi modi per define questa tabella, ma poiché siamo interessati a comprendere cosa accade in DAX, useremo una tabella calcolata. Ecco l'espressione di tabella:

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

Si noterà che questa espressione di tabella calcolata usa SUMMARIZECOLUMNS per calculate la SUM della colonna SalesAmount nella tabella FactInternetSales, dalla colonna Color della tabella DimProduct, and la colonna CalendarYear della tabella DimDate. Ecco il risultato:

Colore	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

Now, immaginiamo di risolvere il problema aziendale di calcolare la differenza di vendite, year-over-year per ogni colore. In effetti, è necessario un modo per find vendite per lo stesso colore nel previousyearand sottrarre ciò dalle vendite nel yearcorrente , nel contesto. Ad esempio, per la combinazione [Red, 2019] stiamo cercando le vendite per [Red, 2018]. Una volta ottenuto questo risultato, è possibile sottrarlo dalle vendite correnti and restituire il valuenecessario.

Uso di OFFSET

OFFSET è perfetto per i calcoli di tipo e previous necessari per rispondere alla domanda aziendale descritta sopra, poiché ci consente di effettuare un confronto relativo. Il nostro tentativo first potrebbe essere:

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

In questa espressione accade molto. Abbiamo usato ADDCOLUMNS per expand la tabella di prima con una colonna chiamata PreviousColorSales. Il contenuto di tale colonna è impostato su CurrentYearSales, che corrisponde a SUM(FactInternetSales[SalesAmount]), per il colore previous (ottenuto utilizzando OFFSET).

Il risultato è:

Colore	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

Questo è un passo più vicino al nostro obiettivo, ma if guardiamo attentamente non corrisponde esattamente a ciò che siamo dopo. Ad esempio, per [Silver, 2017] PreviousColorSales è impostato su [Red, 2020].

Aggiunta di ORDERBY

La definizione precedente equivale a:

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

In questo caso, la chiamata a OFFSET utilizza ORDERBY per ordinare la tabella per Colore and AnnoCalendario in ordine crescente, il che determina quale riga previous viene considerata quella restituita.

Il motivo per cui questi due risultati sono equivalenti è perché ORDERBY esegue automaticamente containsall sulle colonne della relazione che non si trovano in PARTITIONBY. Poiché PARTITIONBY non è stato specificato, ORDERBY è impostato su Color, CalendarYear, and CurrentYearSales. Tuttavia, poiché le coppie Color and CalendarYear nella relazione sono univoche, l'aggiunta di CurrentYearSales non modifica il risultato. In fact, evenif era necessario specificare solo Color in ORDERBY, i risultati sono gli stessi poiché CalendarYear verrebbe aggiunto automaticamente. Ciò è dovuto al fatto che la funzione aggiungerà il numero di colonne necessarie per ORDERBY per garantire che ogni riga possa essere identificata in modo univoco dalle colonne ORDERBYandPARTITIONBY:

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

Aggiunta di PARTITIONBY

Now, per quasi ottenere il risultato che cerchiamo possiamo usare PARTITIONBY, come illustrato nell'espressione di tabella calcolata seguente:

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

Si noti che specificare ORDERBY è facoltativo perché ORDERBY automaticamente containsall le colonne della relazione non specificate in PARTITIONBY. L'espressione seguente restituisce quindi gli stessi risultati perché ORDERBY è impostato su CalendarYear and CurrentYearSales automaticamente:

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

Nota

Nonostante ORDERBY sia impostato automaticamente su CalendarYear and CurrentYearSales, non viene fornita alcuna garanzia sull'ordine in cui verranno aggiunti. If CurrentYearSales viene aggiunto prima di CalendarYear, l'ordine risultante non è in linea con quello previsto. Essere espliciti quando si specifica ORDERBYandPARTITIONBY per evitare confusione and risultati imprevisti.

Entrambe le espressioni restituiscono il risultato cercato:

Colore	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

Come illustrato in questa tabella, la colonna PreviousYearSalesForSameColor mostra le vendite del previouse delyear per lo stesso colore. Per [Red, 2020], restituisce le vendite per [Red, 2019], and e così via. If non sono presenti previousyear, ad esempio nel caso di [Rosso, 2017], non viene restituito alcun value.

È possibile considerare PARTITIONBY come un modo per divide la tabella in parti in cui eseguire il calcolo OFFSET. Nell'esempio precedente la tabella è suddivisa in tante parti quanti sono i colori, una per ogni colore. Quindi, all'interno di ogni parte, il OFFSET viene calcolato e ordinato in base a "CalendarYear".

Visivamente, ciò che accade è questo:

First, la chiamata a PARTITIONBY comporta la suddivisione della tabella in parti, una per ogni colore. Questo è rappresentato dalle caselle blu chiaro nell'immagine della tabella. Next, ORDERBY assicura che ogni parte sia ordinata in base a CalendarYear (rappresentata dalle frecce arancioni). Infine, all'interno di ogni parte ordinata, per ogni riga, OFFSET trova la riga sopra di esso e and riporta tale value nella colonna PreviousYearSalesForSameColor. Poiché per ogni riga first in ogni parte non è presente alcuna riga previous nella stessa parte, il risultato in tale riga per la colonna PreviousYearSalesForSameColor è vuoto.

Per ottenere il risultato finale, è sufficiente sottrarre CurrentYearSales dalle vendite previousyear per lo stesso colore restituito dalla chiamata a OFFSET. Poiché siamo not interessati a visualizzare le vendite di previousyear per lo stesso colore, ma solo nelle vendite year correnti and il year rispetto alla differenza year. Ecco l'espressione di tabella calcolata 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]
        )
    )

And di seguito è riportato il risultato di tale espressione:

Colore	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

Utilizzo di MATCHBY

Potresti aver notato che non abbiamo specificato MATCHBY in all. In questo caso, non è necessario. Le colonne in ORDERBYandPARTITIONBY (per quanto sono state specificate negli esempi precedenti) sono sufficienti per identificare in modo univoco ogni riga. Poiché non è stato specificato MATCHBY, le colonne specificate in ORDERBYandPARTITIONBY vengono usate per identificare in modo univoco ogni riga in modo che possano essere confrontate per consentire OFFSET di ottenere un risultato significativo. If le colonne in ORDERBYandPARTITIONBY non possono identificare in modo univoco ogni riga, è possibile aggiungere colonne aggiuntive alla clausola ORDERBYif tali colonne aggiuntive consentono di identificare in modo univoco ogni riga. If possibile not, viene restituito un error. In questo caso last, la specifica di MATCHBY può aiutare a risolvere il problema error.

If MATCHBY viene specificato, le colonne in MATCHBYandPARTITIONBY vengono utilizzate per identificare in modo univoco ogni riga. If è not possibile, viene restituito un error. Even if MATCHBY non è necessario, è consigliabile specificare in modo esplicito MATCHBY per evitare confusione.

Continuando dagli esempi precedenti, ecco l'espressione last:

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

If si vuole essere espliciti sulla modalità di identificazione univoca delle righe, è possibile specificare MATCHBY come illustrato nell'espressione equivalente seguente:

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

Poiché viene specificato MATCHBY, entrambe le colonne specificate in MATCHBY e in PARTITIONBY vengono usate per identificare in modo univoco le righe. Poiché Color è specificato in tutti i MATCHBY,andePARTITIONBY, l'espressione seguente equivale all'espressione previous:

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

Poiché la specifica di MATCHBY non è necessaria negli esempi esaminati finora, si esaminerà un esempio leggermente diverso che richiede MATCHBY. In questo caso, è presente un elenco di righe di ordini. Ogni riga rappresenta una riga di ordine per un ordine. Un ordine può avere più voci d'ordine and voce d'ordine 1 appare in molti ordini. Inoltre, per ogni riga di ordine è presente un ProductKey and e un importo di vendita. Un sample delle colonne pertinenti nella tabella ha un aspetto simile al seguente:

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

Si noti che SalesOrderNumber and SalesOrderLineNumber sono entrambi necessari per identificare in modo univoco le righe.

Per ogni ordine, si vuole restituire l'previous importo delle vendite dello stesso product (rappresentato da ProductKey) ordinato da SalesAmount in ordine decrescente. L'espressione seguente non funzionerà perché sono presenti potenzialmente più righe in vRelation man mano che viene passata in OFFSET:

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

Questa espressione restituisce un error: "OFFSETparametro Relation può avere righe duplicate, il che non è consentito".

Per far funzionare questa espressione, è necessario specificare MATCHBYand devono includere all colonne che define una riga in modo univoco. MATCHBY è necessario perché la relazione FactInternetSales non contiene chiavi esplicite or colonne univoche. Tuttavia, le colonne SalesOrderNumber and e SalesOrderLineNumber formano insieme una chiave composta , la cui esistenza univoca nella relazione and permette quindi di identificare in modo univoco ogni riga. Specificare solo SalesOrderNumber or e SalesOrderLineNumber non è sufficiente, poiché entrambe le colonne contengono valuesripetute. L'espressione seguente risolve il problema:

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

And questa espressione restituisce effettivamente i risultati seguenti:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Previous Importo delle vendite
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

Related contenuto

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER