DAX e La Funzione FILTER

Potrebbe interessarti:

Se vuoi imparare a preparare, modellare, visualizzare e analizzare i dati aziendali in modo efficace, dai un’occhiata al corso Introduzione a Power BI, e al Corso Avanzato Power BI DAX.

Dimmi di Più

Sintassi di FILTER

In DAX, la funzione FILTER ritorna una nuova tabella filtrata a partire da un’altra tabella o espressione tabellare, mantenendo solo le righe che soddisfano una determinata condizione logica. È una delle funzioni tabellari più importanti e viene utilizzata in moltissimi scenari, spesso all’interno di CALCULATE, CALCULATETABLE e degli iteratori (SUMX, AVERAGEX, ecc.). Ha la seguente sintassi:

= FILTER(Table, FilterExpression)

Parametro	Descrizione
Table	La tabella da filtrare (può essere una tabella virtuale)
FilterExpression	Un’espressione booleana (True / False) che deve essere valutata per ogni riga della tabella. Se la condizione restituisce `TRUE`, la riga viene mantenuta.

Ad esempio:

// count the number of red wines in the Wines table
= COUNTROWS(
      FILTER(                  // filter the input table
          Wines,               //   table
          Wines[TYPE] = "red"  //   boolean filter
      )
  )

La tabella ritornata da FILTER non modifica le tabelle del modello, genera una tabella temporanea in memoria che viene usata per il calcolo della misura.

FILTER Per Ridurre le Righe di Una Tabella

Quando la funzione FILTER viene utilizzata come argomento tabellare per funzioni come COUNTROWS o SUMX, essa crea una tabella virtuale filtrata, contenente solo le righe che soddisfano la condizione specificata. Questa tabella ridotta viene poi passata come primo argomento alla funzione chiamante, che la utilizzerà per il proprio calcolo.

Ad esempio, possiamo calcolare il numero di vendite in cui il valore della colonna CASES SOLD è superiore a 300:

// count the sales having CASES SOLD > 300
No. of High Volume Sales =
    COUNTROWS(
        FILTER(
            Winesales,                   // Table to filter
            Winesales[CASES SOLD] > 300  // Filter condition
        )
    )

In questo caso, FILTER produce una versione ridotta della tabella Winesales, contenente solo le righe con più di 300 casse vendute. COUNTROWS si limita poi a contare il numero di righe presenti nella tabella filtrata.

La funzione FILTER può essere utilizzata anche in combinazione con gli iteratori, come SUMX. In questo scenario, la tabella filtrata diventa il set di righe su cui SUMX effettuerà l’iterazione. In altre parole, SUMX non scorrerà più tutte le righe della tabella originale, ma soltanto quelle che soddisfano la condizione definita in FILTER. Ad esempio:

// compute the Total Cost of each sale having 300+ CASES SOLD
High Volume Total Sales > 300 =
    SUMX(
        // table expression that returns all the Winesales records
        // having more than 300 CASES SOLD
        FILTER(
            Winesales,
            Winesales[CASES SOLD] > 300
        ),

        // row-by-row expression:
        // compute Quantity * Price
        Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
    )

Nell’esempio di cui sopra:

FILTER crea una tabella virtuale contenente solo le vendite ad alto volume (oltre 300 casse).
SUMX esegue l’espressione riga per riga solo su queste vendite filtrate.
l’espressione calcola il costo totale per ogni riga come Quantity * Price, utilizzando la funzione RELATED per recuperare il prezzo della cassa dalla tabella Wines.

Attenzione: la funzione FILTER è a tutti gli effetti un iteratore. Quando viene utilizzata su tabelle di grandi dimensioni — come una Fact Table che può contenere milioni di righe — FILTER è costretta a scansionare ogni singola riga per valutare la condizione logica specificata. Questo processo può risultare particolarmente costoso in termini di calcolo e può quindi generare problemi di prestazioni, soprattutto se la funzione viene richiamata all’interno di misure molto utilizzate o visualizzate su grandi volumi di dati.

Per questo motivo è buona norma usare FILTER solo quando è realmente necessario (ad esempio per condizioni complesse non esprimibili direttamente in CALCULATE) e preferire filtri più semplici e ottimizzati quando possibile.

Uso della funzione DAX FILTER — Figura 1: Uso della funzione FILTER

FILTER Come Argomento di CALCULATE

Quando la funzione FILTER viene utilizzata come argomento di filtro all’interno di CALCULATE, essa produce una tabella virtuale in memoria, contenente solo le righe che soddisfano la condizione logica specificata. Questa tabella viene poi usata da CALCULATE per modificare il Filter Context prima di eseguire l’espressione. Ad esempio:

Sales for Red or French (1) =
    CALCULATE (
        // expression to calculate (in this case, a Measure)
        [Total Sales],

        // filter argument:
        // FILTER creates a virtual table that contains
        // only the wines where TYPE = "red" OR WINE COUNTRY = "France"
        FILTER(
            Wines,                             // Table
            Wines[TYPE] = "red" ||             // Filter Expression
              Wines[WINE COUNTRY] = "France"
        )
    )

In questo esempio, FILTER costruisce una tabella composta solo dai vini rossi o provenienti dalla Francia, e CALCULATE applica questo filtro al contesto prima di calcolare [Total Sales].

Evoluzione della Sintassi (post Marzo 2021)

Prima di Marzo 2021, era obbligatorio utilizzare FILTER dentro CALCULATE in due scenari:

quando la condizione di filtro faceva riferimento a più colonne della stessa tabella;
quando la condizione di filtro includeva una Measure.

Con gli aggiornamenti al linguaggio DAX, ora è possibile utilizzare una sintassi semplificata — ma solo per il caso (1):

Sales for Red or French (2) =
    CALCULATE (
        // expression to calculate
        [No. Of Sales],

        // SIMPLIFIED filter expression (cannot reference a Measure)
        Wines[TYPE] = "red" || Wines[WINE COUNTRY] = "France"
    )

IMPORTANTE: la versione semplificata senza FILTER può produrre risultati sbagliati quando nel report è già presente un filtro sulla colonna TYPE, oppure è presente un filtro sulla colonna WINE COUNTRY. In questi casi, il comportamento dei filtri in OR potrebbe non essere quello atteso, perché la sintassi semplificata applica i filtri in modo diretto al Filter Context “esterno”, sovrascrivendo eventuali filtri già presenti sulle stesse colonne. Per questo motivo, è fortemente consigliato utilizzare FILTER:

quando il filtro coinvolge più colonne,
quando il filtro deve includere una Measure,
in tutti i casi in cui è necessario controllare in modo esplicito e prevedibile la logica di filtraggio.

La funzione FILTER crea sempre una tabella indipendente dal contesto, quindi garantisce un comportamento più comprensibile.

In questo esempio, l’espressione del filtro include più di una colonna della stessa tabella:

// Measure
No. of Sales = COUNTROWS(Winesales)

// Measure:
// High profit wines are where wines
// have a price that is three times the cost price
No. of Sales of High profit Wines =
    CALCULATE (
        // expression to calculate
        [No. of Sales],

        // filter expression that includes MORE than one column from the same table
        FILTER(
            Wines,
            Wines[PRICE PER CASE] >= Wines[COST PRICE] * 3
        )
    )
)

In quest’alreo esempio usiamo la Misura [Total Sales] all’interno della Filter expression:

// Measure
Total Sales = SUMX(
    Winesales,
    Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
)

// Measure
Sales Greater than 20K =
    CALCULATE(
        [No. Of Sales],            // Expression (Measure)
        FILTER(                    // Filter
            Winesales,             //   Table
            [Total Sales] > 20000  //   Filter Expression, including a Measure
        )
    )

Un’altro esempio ancora:

// Measure
No. Of Sales = COUNTROWS(Winesales)

// Measure
Total Sales = SUMX(
    Winesales,
    Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
)

// Measure
Sales Greater than Avg =
    CALCULATE(
        // expression to calculate (Measure)
        [No. of Sales],

        // filter expression
        FILTER(
            // table to filter
            Winesales,

            // nested filter expression (using a Measure):
            // consider only sales having Total Cost > Total Cost Average
            [Total Sales] > AVERAGEX(
                Winesales,
                Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
            )
        )
    )

Altro esempio di utilizzo della funzione DAX FILTER — Figura 2: Altro esempio di utilizzo della funzione FILTER

Filtri Colonna VS Filtri Tabellari

All’interno di CALCULATE è possibile modificare il Filter Context in due modi distinti: applicando filtri di colonna, oppure applicando filtri tabellari. Queste due tipologie non sono equivalenti e producono effetti differenti sia in termini di prestazioni, sia in termini di risultato. I filtri di colonna sono quelli espressi nella forma:

Table[Column] = Value

Essi sono “leggeri” da elaborare, perché il motore di DAX può convertirli direttamente in un filtro semplice e ottimizzato. I filtri tabellari, invece, sono quelli ottenuti tramite funzioni che restituiscono una tabella (come FILTER, ALL, VALUES, ecc.). In questi casi, DAX deve costruire una tabella virtuale contenente le righe che soddisfano la condizione, e soltanto dopo può applicare tale tabella come filtro al contesto.

Poiché generare una tabella virtuale richiede più passaggi e spesso comporta la scansione dell’intera tabella di origine, i filtri tabellari sono normalmente più costosi da elaborare, e possono incidere sulle prestazioni complessive della misura. Inoltre, l’utilizzo di un filtro di colonna rispetto a un filtro tabellare può produrre risultati diversi, perché le due modalità seguono logiche di filtraggio differenti.

Per questo motivo è importante scegliere consapevolmente quale tipo di filtro utilizzare all’interno di CALCULATE, soprattutto in scenari complessi o quando si desidera controllare con precisione il comportamento del Filter Context.

I Filtri Tabellari Sono Meno Efficienti

Consideriamo la Misura Total Sales, definita come segue:

// Measure
Total Sales = SUMX(
    Winesales,
    Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
)

Ora confrontiamo due misure che utilizzano CALCULATE per applicare un filtro alla stessa espressione. La prima utilizza un column filter, cioè una condizione espressa direttamente sulla colonna:

// Measure
Cases GT 300 #1 =
    CALCULATE(
        [Total Sales],               // expression
        Winesales[CASES SOLD] > 300  // column filter (faster)
    )

Questo tipo di filtro è gestito direttamente dallo Storage Engine, che è ottimizzato per la velocità e funziona su dati compressi. Il filtro colonnare viene interpretato e applicato direttamente dallo Storage Engine (SE). Lo Storage Engine lavora su dati compressi ed è altamente ottimizzato per eseguire operazioni di filtraggio semplici, risultando quindi molto rapido ed efficiente. Il filtro viene convertito internamente in un predicato semplice e non richiede la creazione di tabelle intermedie.

La seconda misura applica lo stesso criterio, ma utilizza la funzione FILTER come argomento di filtro:

// Measure
Cases GT 300 #2 =
    CALCULATE(
        [Total Sales],               // expression
        FILTER(                      // table filter (slower)
            Winesales,
            Winesales[CASES SOLD] > 300
        )
    )

In questo caso, la presenza di FILTER obbliga il motore DAX a:

scansionare la tabella Winesales riga per riga,
creare una tabella virtuale in memoria contenente solo le righe che soddisfano la condizione,
aggiungere la tabella risultante al Filter Context.

Questa operazione viene gestita principalmente dal Formula Engine (FE), che è più flessibile ma anche molto più lento rispetto allo Storage Engine. Inoltre, la necessità di materializzare una tabella intermedia aumenta il consumo di memoria. Su tabelle piccole o medie, la differenza di performance può essere trascurabile; su tabelle di grandi dimensioni (tipicamente, Fact Table con milioni di righe), l’utilizzo di FILTER può causare rallentamenti significativi.

Quando possibile, è sempre preferibile utilizzare column filter all’interno di CALCULATE, perché:

sono più veloci;
sfruttano al massimo l’ottimizzazione dello Storage Engine;
non richiedono la creazione di tabelle temporanee;
riducono il carico sul Formula Engine.

In breve, usiamo FILTER solo quando è necessario (filtri complessi, filtri con più colonne, condizioni basate su Measure). Altrimenti, scegliamo sempre i column filter: sono più veloci ed efficienti.

Terzo esempio dell'uso di FILTER — Figura 3: Terzo esempio dell’uso di FILTER

I Filtri Tabellari Danno Risultati Differenti

Consideriamo le due misure seguenti: la prima utilizza un column filter, mentre la seconda utilizza un table filter attraverso la funzione FILTER:

Bordeaux Wines #1 =
    CALCULATE(
        SUM(Winesales[CASES SOLD]),  // expression
        Wines[WINE] = "Bordeaux"     // column filter: overrides the current Filter Context
    )

Bordeaux Wines #2 =
    CALCULATE(
        SUM(Winesales[CASES SOLD]),   // expression
        FILTER(                       // table filter: respects the current Filter Context
            Wines,
            Wines[WINE] = "Bordeaux"
        )
    )

A prima vista potremmo aspettarci che entrambe le misure restituiscano lo stesso valore. In realtà non è così: se inseriamo queste misure in un visual tabellare che mostra la colonna Wines[WINE], otterremo risultati diversi:

Figura 4: i filtri tabellari danno risultati differenti

Osservando il comportamento nel visual, Bordeaux Wines #1 restituisce sempre il valore corrispondente al vino Bordeaux, anche nelle righe che mostrano altri vini. Di contro, Bordeaux Wines #2 restituisce un valore solo per Bordeaux e BLANK per tutti gli altri vini. Perché accade questo?

Nel primo caso, Il filtro Wines[WINE] = "Bordeaux" utilizzato in modo diretto dentro CALCULATE sovrascrive sempre il Filter Context corrente. Non importa quale vino sia visualizzato nella riga della tabella: il filtro imposto nella funzione CALCULATE ha la precedenza, e forza il contesto su Bordeaux.

Bordeaux Wines #1 =
    CALCULATE(
        SUM(Winesales[CASES SOLD]),  // expression
        Wines[WINE] = "Bordeaux"     // column filter: overrides the current Filter Context
    )

Quindi, quando la tabella mostra “Grenache”: il filtro diventa Wines[WINE] = "Bordeaux". Quando mostra “Chardonnay”, accade lo stesso, e così via per tutti gli altri vini. Il risultato è che ogni riga della tabella mostra il totale delle vendite di “Bordeaux”, indipendentemente dal vino visualizzato.

Nel secondo caso, il filtro applicato tramite FILTER rispetta il Filter Context corrente (non lo sostituisce):

Bordeaux Wines #2 =
  CALCULATE(
      SUM(Winesales[CASES SOLD]),   // expression
      FILTER(                       // table filter: respects the current Filter Context
          Wines,
          Wines[WINE] = "Bordeaux"
      )
  )

Quando nel visual viene mostrato “Grenache”, il Filter Context contiene:

Wines[WINE] = "Grenache"

All’interno della misura, FILTER(Wines, Wines[WINE] = "Bordeaux") viene combinato in AND con il contesto già attivo. Il risultato è:

(Wines[WINE] = "Grenache") AND (Wines[WINE] = "Bordeaux")

Questa condizione è ovviamente impossibile, quindi la tabella filtrata risultante è vuota, e SUM(Winesales[CASES SOLD]) restituisce il valore BLANK. Solo quando la riga corrente mostra “Bordeaux”, la condizione è compatibile con il contesto e la misura restituisce un valore.

Se desideriamo che il filtro interno a CALCULATE non sia influenzato dal vino visualizzato nel Visual, dobbiamo rimuovere il Filter Context dalla tabella Wines prima di applicare FILTER. Per farlo, possiamo usare la funzione ALL come modificatore della tabella Wines, in questo modo:

Bordeaux Wines #3 =
    CALCULATE(
        SUM (Winesales[CASES SOLD]),  // expression
        FILTER(                       // table filter: respects the current Filter Context
            ALL(Wines),               //   but ALL(Wines) clear the current FC on that table
            Wines[WINE] = "Bordeaux"
        )
    )

Riassumendo

La funzione FILTER opera solo sulle righe che sono già presenti nel Filter Context corrente. Di conseguenza, la tabella che restituisce è in genere un sottoinsieme della tabella originale, poiché la condizione di FILTER viene valutata in AND con i filtri già attivi. Questo significa che FILTER non può ignorare il contesto corrente, a meno che non venga usata insieme a funzioni che lo rimuovono esplicitamente (come ALL, ALLEXCEPT, ecc.).

Al contrario, i Column Filter utilizzati direttamente all’interno di CALCULATE (ad esempio: Wines[WINE] = "Bordeaux") hanno la capacità di sostituire i filtri correnti sulla stessa colonna. Se esiste già un filtro attivo su Wines[WINE], il column filter inserito in CALCULATE lo sovrascrive, imponendo il nuovo valore — indipendentemente dal Filter Context preesistente.