Informazioni sulle funzioni ORDERBY, PARTITIONBYe MATCHBY

Le ORDERBY, PARTITIONBY e MATCHBY in DAX sono funzioni speciali che possono essere usate solo insieme a DAX alle funzioni Window: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

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

Scenario

Si inizierà con un esempio che non usa affatto le funzioni Window. Di seguito è riportata una tabella che restituisce le vendite totali, per colore, per anno di calendario. Esistono diversi modi per definire questa tabella, ma poiché si è interessati a comprendere cosa accade in DAX, si userà 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 calcolare la SUM colonna SalesAmount nella tabella FactInternetSales, in base alla colonna Color della tabella DimProduct e alla colonna CalendarYear della tabella DimDate. Ecco il risultato:

Colore	AnnoCalendario	VenditeAnnoCorrente
"Nero"	2017	393885
Nero	2018	1818835
Nero	2019	3981638
Nero	2020	2644054
"Blue"	2019	994448
"Blue"	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
NA	2019	207822
NA	2020	227295
Rosso	2017	2961198
Rosso	2018	3686935
Rosso	2019	900175
Rosso	2020	176022
Argento	2017	326399
Argento	2018	750026
Argento	2019	2165176
Argento	2020	1871788
Bianco	2019	2517
"Bianco"	2020	2589
"Giallo"	2018	163071
Giallo	2019	2072083
Giallo	2020	2621602

Si supponga ora di provare a risolvere la questione aziendale di calcolare la differenza nelle vendite, anno dopo anno per ogni colore. In effetti, abbiamo bisogno di un modo per trovare le vendite per lo stesso colore nell'anno precedente e sottraerlo dalle vendite nell'anno corrente, 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 e restituire il valore richiesto.

Uso di OFFSET

OFFSET è perfetto per il confronto tipico con i tipi precedenti di calcoli necessari per rispondere alla domanda aziendale descritta in precedenza, in quanto ci consente di fare un movimento relativo. Il primo tentativo 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]
        )
    )

Molto succede con questa espressione. Abbiamo utilizzato ADDCOLUMNS per espandere la tabella precedente aggiungendo una colonna chiamata PreviousColorSales. Il contenuto di tale colonna viene impostato su CurrentYearSales, ovvero SUMFactInternetSales[SalesAmount], per il colore precedente (recuperato tramite OFFSET).

Il risultato è:

Colore	Anno del Calendario	VenditeAnnoCorrente	VenditeColorePrecedente
"Nero"	2017	393885
Nero	2018	1818835	393885
Nero	2019	3981638	1818835
Nero	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
Rosso	2017	2961198	227295
Rosso	2018	3686935	2961198
Rosso	2019	900175	3686935
Rosso	2020	176022	900175
Argento	2017	326399	176022
Argento	2018	750026	326399
Argento	2019	2165176	750026
Argento	2020	1871788	2165176
"White"	2019	2517	1871788
"White"	2020	2589	2517
"Yellow"	2018	163071	2589
Giallo	2019	2072083	163071
Giallo	2020	2621602	2072083

Questo è un passo più vicino al nostro obiettivo, ma se 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 usa ORDERBY per ordinare la tabella in base a Color e CalendarYear in ordine crescente, che determina ciò che viene considerato la riga precedente restituita.

Il motivo per cui questi due risultati sono equivalenti è che ORDERBY contiene automaticamente tutte le colonne della relazione che non si trovano in PARTITIONBY. Poiché PARTITIONBY non è stato specificato, ORDERBY è impostato su Color, CalendarYear e CurrentYearSales. Tuttavia, poiché le coppie Color e CalendarYear nella relazione sono univoche, l'aggiunta di CurrentYearSales non modifica il risultato. Infatti, anche se fosse necessario specificare solo Color in ORDERBY, i risultati sono gli stessi poiché CalendarYear verrebbe aggiunto automaticamente. Ciò è dovuto al fatto che la funzione aggiungerà tutte le colonne necessarie a ORDERBY per garantire che ogni riga possa essere identificata in modo univoco dalle colonne ORDERBY e 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]
        )
    )

Aggiunta di PARTITIONBY

Ora, per ottenere quasi il risultato che vogliamo, possiamo usare PARTITIONBY, come illustrato nell'espressione della 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 qui è facoltativo perché ORDERBY contiene automaticamente tutte le colonne della relazione che non sono specificate in PARTITIONBY. L'espressione seguente restituisce quindi gli stessi risultati perché ORDERBY è impostata su CalendarYear e 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

Mentre ORDERBY è impostato su CalendarYear e CurrentYearSales automaticamente, non viene fornita alcuna garanzia in base all'ordine in cui verranno aggiunti. Se CurrentYearSales viene aggiunto prima di CalendarYear, l'ordine risultante non è in linea con quello previsto. Essere espliciti quando si specificano ORDERBY e PARTITIONBY per evitare confusione e risultati imprevisti.

Entrambe le espressioni restituiscono il risultato cercato:

Colore	Anno del Calendario	VenditeAnnoCorrente	Vendite dell'anno precedente per lo stesso colore
Nero	2017	393885
Nero	2018	1818835	393885
Nero	2019	3981638	1818835
Nero	2020	2644054	3981638
"Blue"	2019	994448
"Blue"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
NA	2020	227295	207822
Rosso	2017	2961198
"Rosso"	2018	3686935	2961198
Rosso	2019	900175	3686935
"Rosso"	2020	176022	900175
Argento	2017	326399
Argento	2018	750026	326399
Argento	2019	2165176	750026
Argento	2020	1871788	2165176
Bianco	2019	2517
Bianco	2020	2589	2517
Giallo	2018	163071
"Giallo"	2019	2072083	163071
Giallo	2020	2621602	2072083

Come illustrato in questa tabella, la colonna PreviousYearSalesForSameColor mostra le vendite per l'anno precedente per lo stesso colore. Nel caso di [Red, 2020], restituisce le vendite di [Red, 2019], e così via. Se non è presente alcun anno precedente, ad esempio nel caso di [Rosso, 2017], non viene restituito alcun valore.

È possibile considerare PARTITIONBY come un modo per dividere la tabella in parti in cui eseguire il OFFSET calcolo. 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:

Prima di tutto, la chiamata a PARTITIONBY si traduce nella tabella che viene divisa in parti, una per ogni colore. Questo è rappresentato dalle caselle blu chiaro nell'immagine della tabella. Assicurarsi quindi ORDERBY 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 essa e restituisce tale valore nella colonna PreviousYearSalesForSameColor. Poiché per ogni prima riga di ogni parte non esiste alcuna riga precedente nella stessa parte, il risultato della riga per la colonna PreviousYearSalesForSameColor è vuoto.

Per ottenere il risultato finale, è sufficiente sottrarre CurrentYearSales dalle vendite dell'anno precedente per lo stesso colore restituito dalla chiamata a OFFSET. Poiché non siamo interessati a visualizzare le vendite dell'anno precedente per lo stesso colore, ma solo nelle vendite dell'anno corrente e nella differenza di anno in anno. 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]
        )
    )

Di seguito è riportato il risultato di tale espressione:

Colore	Anno Calendario	VenditeAnnoCorrente	YoYSalesForSameColor
Nero	2017	393885	393885
Nero	2018	1818835	1424950
Nero	2019	3981638	2162803
Nero	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
Rosso	2017	2961198	2961198
Rosso	2018	3686935	725737
Rosso	2019	900175	-2786760
Rosso	2020	176022	-724153
Argento	2017	326399	326399
Argento	2018	750026	423627
Argento	2019	2165176	1415150
Argento	2020	1871788	-293388
Bianco	2019	2517	2517
Bianco	2020	2589	72
"Giallo"	2018	163071	163071
Giallo	2019	2072083	1909012
Giallo	2020	2621602	549519

Uso di MATCHBY

Potresti aver notato che non è stato specificato MATCHBY affatto. In questo caso, non è necessario. Le colonne in ORDERBY e PARTITIONBY (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 ORDERBY e PARTITIONBY vengono usate per identificare in modo univoco ogni riga in modo che possano essere confrontate per consentire OFFSET di ottenere un risultato significativo. Se le colonne in ORDERBY e PARTITIONBY non possono identificare in modo univoco ogni riga, è possibile aggiungere altre colonne alla ORDERBY clausola se tali colonne aggiuntive consentono di identificare in modo univoco ogni riga. Se non è possibile, viene restituito un errore. In questo ultimo caso, specificare MATCHBY può essere utile per risolvere l'errore.

Se MATCHBY viene specificato, le colonne in MATCHBY e PARTITIONBY vengono utilizzate per identificare in modo univoco ogni riga. Se non è possibile, viene restituito un errore. Anche se MATCHBY non è necessario, è consigliabile specificare MATCHBY in modo esplicito per evitare confusione.

Continuando dagli esempi precedenti, ecco l'ultima espressione:

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

Se 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 sia in MATCHBY e PARTITIONBY, l'espressione seguente equivale all'espressione precedente:

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 linee d'ordine. Ogni riga rappresenta una riga di ordine per un ordine. Un ordine può avere più righe di ordine e la riga di ordine 1 viene visualizzata in molti ordini. Inoltre, per ogni riga di ordine abbiamo un ProductKey e un SalesAmount. Un esempio delle colonne pertinenti nella tabella ha un aspetto simile al seguente:

Numero Ordine di Vendita	NumeroRigaOrdineVendita	Chiave prodotto	ImportoVendite
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 e SalesOrderLineNumber sono entrambi necessari per identificare in modo univoco le righe.

Per ogni ordine, si vuole restituire l'importo di vendita precedente dello stesso prodotto (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 errore: "OFFSETIl parametro Relation potrebbe avere righe duplicate, che non sono consentite".

Per consentire il funzionamento di questa espressione, MATCHBY è necessario specificare e includere tutte le colonne che definiscono in modo univoco una riga. MATCHBY è obbligatorio perché la relazione FactInternetSales non contiene chiavi esplicite o colonne univoche. Tuttavia, le colonne SalesOrderNumber e SalesOrderLineNumber formano insieme una chiave composta, in cui la loro esistenza insieme è univoca nella relazione e può quindi identificare in modo univoco ogni riga. Non è sufficiente specificare solo SalesOrderNumber o SalesOrderLineNumber, poiché entrambe le colonne contengono valori ripetuti. 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]
            )
    )

E questa espressione restituisce effettivamente i risultati seguenti:

Numero Ordine di Vendita	NumeroRigaOrdineVendita	Chiave del prodotto	ImportoVendite	Importo delle vendite precedenti
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

Contenuto correlato

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER