Informazioni sulle funzioni ORDERBY, PARTITIONBY e MATCHBY

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

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

Scenario

Iniziamo con un esempio che non usa affatto le funzioni finestra. Di seguito è riportata una tabella che restituisce le vendite totali, per colore e per anno di calendario. Esistono vari modi per definire questa tabella, ma poiché l'obiettivo è 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 )
    )

Questa espressione di tabella calcolata usa SUMMARIZECOLUMNS per calcolare la somma della colonna SalesAmount nella tabella FactInternetSales, in base alla colonna Color della tabella DimProduct e in base alla 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

Supponiamo di voler rispondere alla domanda aziendale di calcolare la differenza nelle vendite, anno per anno, per ogni colore. In effetti è necessario un modo per trovare le vendite per lo stesso colore nell'anno precedente e sottrare il valore 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 valore, è possibile sottrarlo dalle vendite correnti e restituire il valore richiesto.

Utilizzo di OFFSET

OFFSET è perfetto per i tipici calcoli confronta con i precedenti necessari per rispondere alla domanda aziendale descritta in precedenza, in quanto ci consente di eseguire 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]
        )
    )

In questa espressione accade molto. È stato usato ADDCOLUMNS per espandere la tabella precedente con una colonna denominata PreviousColorSales. Il contenuto di tale colonna viene impostato su CurrentYearSales, ovvero SUM(FactInternetSales[SalesAmount]), per il colore precedente (recuperato tramite 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

Siamo un passo più vicini al nostro obiettivo, ma se guardiamo attentamente non corrisponde esattamente a ciò che cerchiamo. 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, il che determina cosa 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 sono 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 specificando solo Color in ORDERBY, i risultati sarebbero gli stessi poiché CalendarYear verrebbe aggiunto automaticamente. Ciò è dovuto al fatto che la funzione aggiungerà il numero di 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 desiderato possiamo utilizzare 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 contiene automaticamente tutte le colonne della relazione non specificate in PARTITIONBY. L'espressione seguente restituisce quindi gli stessi risultati, perché ORDERBY è impostato automaticamente su CalendarYear e 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]
        )
    )

Nota

Mentre ORDERBY è impostato automaticamente su CalendarYear e CurrentYearSales, non esiste alcuna garanzia in merito all'ordine in cui verranno sommati. Se CurrentYearSales viene aggiunto prima di CalendarYear, l'ordine risultante non è in linea con quello previsto. Specificare ORDERBY e PARTITIONBY in modo esplicito per evitare confusione e 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 per l'anno precedente per lo stesso colore. Per [Red, 2020] restituisce le vendite per [Red, 2019] e così via. Se non è presente un anno precedente, ad esempio nel caso di [Red, 2017], non viene restituito alcun valore.

È possibile considerare PARTITIONBY come un modo per dividere 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, viene calcolato il valore OFFSET, ordinato in base a CalendarYear.

Visivamente, ciò che accade è questo:

Prima di tutto, la chiamata a PARTITIONBY risulta nella suddivisione della tabella in parti, una per ogni colore. Questo è rappresentato dalle caselle blu chiaro nell'immagine della tabella. Quindi, ORDERBY fa in modo che ogni parte sia ordinata in base a CalendarYear (rappresentato dalle frecce arancioni). Infine, all'interno di ogni parte ordinata, OFFSET trova la riga precedente 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 il valore CurrentYearSales dalle vendite dell'anno precedente per lo stesso colore, restituito dalla chiamata a OFFSET. Poiché non ci interessa visualizzare le vendite dell'anno precedente per lo stesso colore, ma solo le vendite dell'anno corrente e la 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]
        )
    )

Ed ecco il risultato dell'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

Uso di MATCHBY

Probabilmente si è notato che MATCHBY non è stato specificato. In questo caso, non è necessario. Le colonne in ORDERBY e PARTITIONBY (per quanto specificato 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 sia possibile confrontarle per consentire a OFFSET di fornire un risultato significativo. Se le colonne in ORDERBY e PARTITIONBY non possono identificare in modo univoco ogni riga, è possibile aggiungere altre colonne alla clausola ORDERBY se tali colonne extra consentono di identificare in modo univoco ogni riga. Se questo non è possibile, viene restituito un errore. In questo ultimo caso, specificare MATCHBY può consentire di risolvere l'errore.

Se si specifica MATCHBY, le colonne in MATCHBY e PARTITIONBY vengono utilizzate per identificare in modo univoco ogni riga. Se questo non è possibile, viene restituito un errore. Anche se MATCHBY non è obbligatorio, è consigliabile specificare in modo esplicito MATCHBY 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 indicare esplicitamente come identificare in modo univoco le 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é è 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 che in 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é specificare MATCHBY non è necessario 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ù righe di ordine e la riga ordine 1 compare in molti ordini. Inoltre, per ogni riga di ordine abbiamo un valore ProductKey e un valore SalesAmount. Un esempio delle colonne pertinenti nella tabella può essere 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 e SalesOrderLineNumber sono entrambi necessari per identificare in modo univoco le righe.

Per ogni ordine, si vuole restituire l'importo delle vendite precedenti dello stesso prodotto (rappresentato da ProductKey) ordinato in base a SalesAmount in ordine decrescente. L'espressione seguente non funzionerà perché sono presenti potenzialmente più righe in vRelation nel momento in cui viene passato a 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: "Il parametro Relation di OFFSET può contenere righe duplicate. Questo non è consentito".

Per far funzionare questa espressione, è necessario specificare MATCHBY e includere tutte le colonne che definiscono in modo univoco una riga. Qui MATCHBY è necessario, perché la relazione FactInternetSales non contiene chiavi esplicite o colonne univoche. Tuttavia, le colonne SalesOrderNumber e SalesOrderLineNumber insieme formano una chiave composita, in cui la loro esistenza contemporanea è unica nella relazione e pertanto può identificare in modo univoco ogni riga. Semplicemente specificare SalesOrderNumber o SalesOrderLineNumber non è sufficiente, perché 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 desiderati:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Previous Sales Amount
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