Filtrare dati nelle formule
È possibile creare filtri nelle formule per limitare i valori dei dati di origine utilizzati nei calcoli. A tale scopo, è possibile specificare una tabella come input per la formula e quindi definire un'espressione di filtro. L'espressione di filtro fornita viene utilizzata per eseguire una query sui dati e restituire solo un subset dei dati di origine. Il filtro viene applicato dinamicamente ogni volta che si aggiornano i risultati della formula, a seconda del contesto corrente dei dati. In questa sezione viene descritto come creare filtri nelle formule DAX (Data Analysis Expressions).
Creazione di un filtro in una tabella utilizzata in una formula
È possibile applicare filtri nelle formule in cui viene utilizzata una tabella come input. Anziché immettere un nome di tabella, è possibile utilizzare la funzione FILTER per definire un subset di righe dalla tabella specificata. Il subset viene passato a un'altra funzione per operazioni quali aggregazioni personalizzate.
Si supponga, ad esempio, di disporre di una tabella di dati contenente informazioni sugli ordini dei rivenditori e che si desideri calcolare le quantità vendute da ogni rivenditore. Tuttavia, si desidera mostrare solo l'importo delle vendite di quei rivenditori che hanno venduto più unità di prodotti dal valore più elevato. Nella formula seguente, basata sulla cartella di lavoro di esempio di DAX, viene illustrato un esempio di come sia possibile creare questo calcolo utilizzando un filtro:
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
Nella prima parte della formula viene specificata una delle funzioni di aggregazione di PowerPivot, in cui viene utilizzata una tabella come argomento. SUMX consente di calcolare una somma in una tabella.
La seconda parte della formula, FILTER(table, expression), consente di indicare a SUMX quali dati utilizzare. Per SUMX è richiesta una tabella o un'espressione tramite cui viene restituita una tabella. In questo caso, anziché utilizzare tutti i dati in una tabella, si utilizza la funzione FILTER per specificare quali righe della tabella vengono utilizzate.
L'espressione di filtro è composta da due parti: la prima parte indica il nome della tabella a cui applicare il filtro. La seconda parte definisce un'espressione da utilizzare come condizione di filtro. In questo caso, si filtrano i rivenditori che hanno venduto più di 5 unità e i prodotti il cui costo supera i $ 100. L'operatore, &&, è un operatore AND logico che indica che entrambe le parti della condizione devono essere vere affinché la riga appartenga al subset filtrato.
La terza parte della formula indica alla funzione SUMX i valori da sommare. In questo caso si utilizza solo l'importo delle vendite.
Funzioni quali FILTER, che restituiscono una tabella, non restituiscono mai la tabella o le righe direttamente alla cartella di lavoro di PowerPivot, ma vengono sempre incorporate in un'altra funzione. Per ulteriori informazioni su FILTER e su altre funzioni utilizzate per l'applicazione di filtri, inclusi ulteriori esempi, vedere Funzioni filtro (DAX).
[!NOTA]
L'espressione di filtro è influenzata dal contesto nel quale viene utilizzata. Se ad esempio si utilizza un filtro in una misura e la misura viene utilizzata in una tabella pivot o in un grafico pivot, è possibile che il subset di dati restituito sia interessato da filtri o filtri dei dati aggiuntivi che l'utente ha applicato nella tabella pivot. Per ulteriori informazioni sul contesto, vedere Contesto nelle formule DAX.
Filtri per la rimozione di duplicati
Oltre ad applicare dei filtri per valori specifici, è possibile restituire un set univoco di valori da un'altra tabella o colonna. Questa operazione può essere utile se si desidera contare il numero di valori univoci in una colonna o utilizzare un elenco di valori univoci per altre operazioni. In DAX sono disponibili due funzioni per la restituzione di valori distinti: Funzione DISTINCT e Funzione VALUES.
La funzione DISTINCT esamina una singola colonna specificata come argomento della funzione e restituisce una nuova colonna contenente solo i valori distinti.
La funzione VALUES restituisce anche un elenco di valori univoci, compreso il membro sconosciuto. Questo è utile quando si utilizzano valori di due tabelle unite in join da una relazione e un valore non è presente in una tabella mentre lo è nell'altra. Per ulteriori informazioni sul membro sconosciuto, vedere Contesto nelle formule DAX.
Entrambe queste funzioni restituiscono un'intera colonna di valori; pertanto, vengono utilizzate le funzioni per ottenere un elenco di valori che viene quindi passato a un'altra funzione. Ad esempio, è possibile utilizzare la formula seguente per ottenere un elenco dei prodotti distinti venduti da un determinato rivenditore, tramite il codice Product Key univoco, quindi contare i prodotti in tale elenco tramite la funzione COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Influenza del contesto sui filtri
Quando si aggiunge una formula DAX a una tabella pivot o a un grafico pivot, i risultati della formula possono essere influenzati dal contesto. Se si utilizza una tabella di PowerPivot, il contesto è costituito dalla riga corrente e dai relativi valori. Se si utilizza una tabella pivot o un grafico pivot, il contesto indica il set o il subset di dati definiti da operazioni quali sezionamento o applicazione di filtri. Il contesto dipende anche dalla struttura di una tabella pivot o di un grafico pivot. Se, ad esempio, si crea una tabella pivot in cui sono raggruppate le vendite per regione e per anno, in tale tabella vengono visualizzati solo i dati relativi a tali regioni e anni. Qualsiasi misura aggiunta alla tabella pivot viene pertanto calcolata nel contesto delle intestazioni di colonna e riga, oltre ad eventuali filtri nella formula della misura.
Per ulteriori informazioni, vedere Contesto nelle formule DAX.
Rimozione di filtri
Quando si utilizzano formule complesse, potrebbe essere necessario sapere esattamente quali sono i filtri correnti o modificare il contesto di filtro come parte della formula. DAX fornisce numerose funzioni che consentono di rimuovere i filtri e di controllare quali colonne vengono mantenute come parte del contesto di filtro corrente. In questa sezione vengono fornite informazioni generali sull'influenza di queste funzioni sui risultati di una formula.
Override di tutti i filtri tramite la funzione ALL
È possibile utilizzare la funzione ALL per eseguire l'override di qualsiasi filtro applicato in precedenza e restituire tutte le righe della tabella alla funzione utilizzata per eseguire l'aggregazione o un'altra operazione. Se si utilizzano una o più colonne anziché una tabella come argomento di ALL, la funzione ALL restituisce tutte le righe, ignorando eventuali filtri di contesto.
[!NOTA]
Se si ha familiarità con la terminologia relativa ai database relazionali, è possibile pensare a ALL come a una funzione che genera il left outer join naturale di tutte le tabelle.
Si supponga, ad esempio, di disporre di una tabella Sales e di una tabella Products e che si desideri creare una formula per calcolare la somma delle vendite del prodotto corrente divisa per le vendite di tutti i prodotti. È necessario prendere in considerazione il fatto che, se la formula viene utilizzata in una misura, l'utente della tabella pivot potrebbe avere utilizzato un filtro dei dati per applicare dei filtri a un determinato prodotto, con il nome del prodotto nelle righe. Per ottenere il valore reale del denominatore indipendentemente da filtri o filtri dei dati, è pertanto necessario aggiungere la funzione ALL per eseguire l'override di qualsiasi filtro. La formula seguente è un esempio di come utilizzare ALL per eseguire l'override degli effetti di filtri precedenti:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
La prima parte della formula, SUM (Sales[Amount]), consente di calcolare il numeratore.
Per la somma viene preso in considerazione il contesto corrente, pertanto se si aggiunge la formula in una colonna calcolata, viene applicato il contesto della riga e se si aggiunge la formula in una tabella pivot come misura, vengono applicati eventuali filtri applicati nella tabella pivot (contesto di filtro).
La seconda parte della formula consente di calcolare il denominatore. La funzione ALL esegue l'override di qualsiasi filtro che potrebbe essere applicato alla tabella Products.
Per ulteriori informazioni, inclusi esempi dettagliati, vedere Funzione ALL.
Override di filtri specifici tramite la funzione ALLEXCEPT
Anche la funzione ALLEXCEPT esegue l'override di filtri esistenti, tuttavia è possibile specificare che alcuni dei filtri esistenti devono essere mantenuti. Le colonne nominate come argomenti della funzione ALLEXCEPT specificano quali colonne continueranno a essere filtrate. Se si desidera eseguire l'override dei filtri dalla maggior parte delle colonne ma non da tutte, la funzione ALLEXCEPT risulta più appropriata di ALL. La funzione ALLEXCEPT è particolarmente utile quando si creano tabelle pivot in cui potrebbero essere applicati filtri in numerose colonne diverse e si desidera controllare i valori utilizzati nella formula. Per ulteriori informazioni, incluso un esempio dettagliato dell'utilizzo di ALLEXCEPT in una tabella pivot, vedere Funzione ALLEXCEPT.