Livello: intermedio
In passato ho scritto diversi articoli contenenti riferimenti incorporati a query DAX e funzioni di tabelle DAX. Oggi sto scrivendo un articolo che introduce il linguaggio di query DAX da zero in modo che tu possa imparare come usarlo e come ti aiuterà a diventare una superstar di Power BI.
Che cos'è un linguaggio di query??
Un linguaggio di query è un linguaggio di programmazione che consente di inviare una richiesta a un database e quindi ricevere dati sotto forma di tabella. Se hai solo poche centinaia di righe di dati, questo non è molto utile. e, In secondo luogo, ha milioni di righe di dati, diventa molto utile.
La differenza più importante tra DAX quando utilizzato in misure e colonne calcolate e DAX come linguaggio di query è che il linguaggio di query restituisce sempre una tabella. La tabella risultante sarà composta da una o più colonne, nessuna o più righe e dati all'interno di nessuna o più celle della tabella.
Perché ho bisogno di un linguaggio di query??
Quando crei un modello di dati in Power BI o PowerPivot per Excel, si stanno fondamentalmente caricando dati tabulari in un database di reportistica dedicato con l'esplicito scopo di analisi e / o rapporti. Una volta caricato, tu (l'autore della relazione) in genere trascorri ore di sforzi per costruire il modello di dati in modo che possa essere utilizzato per aggiungere valore alla tua attività / lavoro. Finalmente, una volta fatto tutto questo, il modo più comune per utilizzare il nuovo modello di dati è creare report e tabelle pivot che presentino i dati a un utente in un modo facile da capire.
Prossimo, mostra un esempio di report Power BI interattivo eseguito da un modello di dati Power BI.
Ed ecco una tabella pivot in esecuzione su un modello di dati Excel PowerPivot.
Entrambe le visualizzazioni di cui sopra sono riepiloghi dei dati sottostanti e in entrambi i casi in realtà, non può vedere nessuno dei dati sottostanti stessi, solo il riassunto. Gli strumenti di visualizzazione di cui sopra sono eccellenti ed estremamente utili. tuttavia, generalmente non sono gli strumenti migliori se vuoi vedere i dati sottostanti stessi o se vuoi estrarre set di dati molto grandi per qualsiasi motivo.
Una volta che hai investito tutto questo sforzo nella costruzione del tuo modello di dati, vorrà usarlo al meglio. PowerPivot e Power BI possono gestire molti milioni di righe di dati (a differenza del tradizionale Excel) e, così, il “modo antico” solo guardare il grafico sottostante non è molto pratico in questo nuovo mondo felice.
Mostrami i dati sottostanti!
Se prendi la tabella pivot che ho mostrato sopra, la maggior parte degli utenti di Excel saprebbe di poter fare doppio clic su qualsiasi cella di valore (diciamo la 31,6% rappresenta la vendita di mountain bike per persone tra 30 e 40 anni). Quando fai doppio clic su una tabella pivot tradizionale come questa, succede qualcosa di magico: una copia dei dati sottostanti viene estratta dalla fonte e magicamente appare in un nuovo foglio in Excel. Una volta che hai finito di guardare la copia dei dati, puoi eliminare il nuovo foglio e tornerai al punto di partenza: la vita è bella.
Quando fai doppio clic su una tabella pivot tradizionale come questa, stai effettivamente inviando una query al database sottostante e la risposta alla query è un datatable.
Ma funziona con PowerPivot??
Decisamente, si e no a seconda di cosa stai cercando. L'esperienza è diversa se si dispone di un modello di dati Excel PowerPivot o si è connessi a un'origine dati SSAS esterna. Nella mia esperienza, i risultati sono incoerenti nella migliore delle ipotesi e, Nel peggiore dei casi, potrebbero non fornire ciò che ti aspetti. Ma la buona notizia è che con DAX come linguaggio di query, puoi facilmente scrivere qualsiasi query tu voglia ed estrarre esattamente il sottoinsieme di dati di cui hai bisogno. Non devi più fare doppio clic sulla tabella pivot per interrogare i tuoi dati.
È ora di imparare DAX come linguaggio di query
Bene, basta parlare, è ora di prendere sul serio DAX come linguaggio di query. Hai investito tempo e fatica nella creazione del tuo modello di dati in Power BI o Power Pivot e ora, giustamente, vuoi estrarre un sottoinsieme dei tuoi dati in modo da poterlo dare un'occhiata. Cominciamo.
Prima seleziona lo strumento appropriato
Ci sono 3 approcci generali che puoi adottare per estrarre una tabella di dati dal tuo favoloso modello di dati.
- Utilizza uno strumento dedicato come DAX Studio o SQL Server Management Studio. Ho scritto un articolo che descrive come installare e utilizzare DAX Studio qui.
- Scrivi uno “nuova tabella” e Power BI Desktop. ho scritto a articolo su come utilizzare questa funzione qui.
- Usare un “trucco da tavola” Ho imparato da Marco Russo ad estrarre una tabella direttamente in Excel. Tratterò questo trucco più avanti in questa serie di articoli..
Userò DAX Studio in questo articolo del blog, ma puoi usare Power BI "Nuova tabella" se vuoi andare avanti e non lo hai (o vuoi) DAX Studio. Infatti, se vuoi imparare queste cose, allora dovrei comunque usare entrambi gli approcci. Nella mia esperienza, non puoi imparare nuovi concetti semplicemente leggendo. tuttavia, si noti che l'uso di Power BI ed Excel come strumento di query dovrebbe essere considerato come un'estrazione temporanea dei dati. Una volta che hai guardato i dati che vuoi, Normalmente lascerei cadere la tabella in modo da non finire per memorizzare dati ridondanti (puoi sempre estrarli di nuovo se ne hai bisogno).
Iniziamo con la dichiarazione di valutazione
Tutte le query DAX devono iniziare con l'istruzione EVALUATE. tuttavia, c'è un'eccezione. Quando usi il pulsante “Nuova tabella” di Power BI per scrivere una query, ometti semplicemente questa istruzione EVALUATE. Questo è sicuramente un altro esempio di “Sintassi Zucchero” dove gli sviluppatori proteggono l'utente da alcune delle sintassi più confuse. Se stai seguendo e testando le mie domande di seguito in Power BI, ometti semplicemente l'istruzione EVALUATE quando scrivi le query. Cosa c'è di più, se vuoi spostare la tua query DAX Studio in una tabella in Power BI, puoi rimuovere l'istruzione EVALUATE in quel momento.
Connessione DAX Studio al database
Quando eseguo DAX Studio, Mi viene chiesto di connettermi al mio database. In questo caso, Ho un'istanza di Power BI Desktop con un modello di dati Adventure Works in esecuzione sul mio PC e DAX Studio mi offre la possibilità di connettermi a quello. Puoi anche connetterti a modelli di dati Excel PowerPivot e server SSAS tabulari.
Per una descrizione più completa dell'interfaccia utente di DAX Studio, leggi l'altro mio post che ho menzionato sopra. Altrimenti, entriamo nel merito.
Restituire una tabella esistente
La query DAX più semplice che è possibile digitare consiste semplicemente nel restituire una copia completa di una tabella esistente.
EVALUATE
Customers
Quando eseguo questa query in DAX Studio (visualizzato come 1 prossimo) e premere F5 per l'esecuzione, Viene restituita l'intera tabella (visualizzato come 2). Un totale di 18,484 righe di dati (visualizzato come 3).
Questo di per sé non è troppo utile in quanto probabilmente hai già questa tabella in qualche sistema sorgente..
Elenco dei valori univoci
In DAX esistono molte funzioni che restituiscono tabelle anziché valori scalari. Ho trattato questo argomento in modo approfondito la scorsa settimana in modo da poterlo leggere qui se lo desideri..
Permettetemi ora di dare un'occhiata alla funzione ALL.
EVALUATE
ALL(Prodotti[Categoria])
Quando eseguo questa funzione tabella come query, Posso davvero “guarda” i risultati della consultazione. In questo caso, La query restituisce una tabella a colonna singola costituita da 4 righe, tutte le categorie di prodotti unici.
Prossimo, vediamo VALORI.
EVALUATE
VALUES(Prodotti[Categoria])
In questo caso, La funzione Tabella valori restituisce esattamente lo stesso risultato.
Ora diamo un'occhiata a DISTINCT.
Evaluate
DISTINCT(Prodotti[Categoria])
E la stessa cosa di nuovo qui.
In questo caso, tutte e tre le funzioni tabella restituiscono la stessa tabella. In realtà, fanno tutti cose diverse in diversi scenari di filtraggio. Questa è una delle cose migliori delle query DAX e cioè che puoi “ispezionare” Cosa sta succedendo con queste funzioni di tabella in diversi scenari. Ne parleremo più avanti.
Una tabella a due colonne
La funzione ALL può prendere 1 o più colonne come input (o un tavolo). Nel seguente esempio, Ho usato ALL per materializzare una tabella di tutte le combinazioni di categorie di prodotti e sottocategorie di prodotti. Ci sono 37 combinazioni uniche in totale.
Una tabella filtrata
La maggior parte delle persone che conoscono DAX conosceranno la funzione FILTER. La funzione filtro restituisce una copia filtrata di una tabella. ad esempio, vedi esempio qui sotto:
EVALUATE
FILTER(Prodotti, [Vendite totali] > 200000)
Questa è una query molto più interessante perché restituisce una tabella contenente tutti i prodotti che hanno venduto più di $ 200,000 nelle vendite tutto il tempo. Ci sono 49 di questi prodotti.
Modificare il contesto del filtro
Quando si digita una misura DAX o una colonna calcolata, Esiste un solo modo per modificare il contesto del filtro, ovvero utilizzando la funzione CALCOLA. CALCULATE restituisce un valore scalare dopo l'applicazione dei filtri. CALCULATETABLE è un fratello di CALCULATE. Come probabilmente puoi intuire, CALCULATETABLE restituisce una tabella dopo l'applicazione dei filtri.
Questa query successiva restituisce esattamente lo stesso risultato dell'ultima query.
EVALUATE
CALCULATETABLE(Prodotti, FILTRO(Prodotti, [Vendite totali] > 200000))
La differenza principale con l'utilizzo di CALCULATETABLE rispetto al solo utilizzo di FILTER è che CALCULATETABLE può restituire una tabella diversa alla funzione FILTER.
EVALUATE
CALCULATETABLE(Saldi, FILTRO ( Prodotti, [Vendite totali] > 200000))
La query precedente utilizza la stessa funzione FILTER di prima, ma invece di restituire una tabella contenente i prodotti che hanno venduto più di $ 200,000, restituisce tutte le transazioni di vendita effettive che hanno contribuito alla qualifica di tali prodotti (vedi i risultati sotto).
Combina funzioni tabella
Ora che vedi come funzionano alcune delle funzioni di tabella standard in una query, vediamo come combinarli in un'unica query.
EVALUATE
CALCULATETABLE(
VALORI(Prodotti[Sottocategoria]),
FILTRO(Prodotti, [Vendite totali] > 50000)
)
Questa query applica prima un filtro a tutti i prodotti che hanno venduto più di $ 50,000 (linea 4 prossimo) e quindi restituisce un elenco di sottocategorie di prodotti univoci che contengono questi prodotti.
Una cosa interessante dei risultati della query precedente è che il “Caschi” sono nella lista (l'unica sottocategoria che non è per le biciclette). Per vedere quali sono i prodotti esatti dove è stato venduto, si tratta semplicemente di scrivere un'altra query come segue.
EVALUATE
CALCULATETABLE(
VALORI(Prodotti[Nome del prodotto]),
FILTRO(Prodotti, [Vendite totali] > 50000),
Prodotti[Sottocategoria]="Caschi"
)
Nota come sono stato in grado di riutilizzare la struttura della query originale per restituire l'elenco dei nomi dei prodotti con solo una piccola modifica alla query.
Ce ne saranno altri la prossima settimana
questo e 'un grande argomento, troppo grande per un singolo post sul blog. Questa settimana ho introdotto l'argomento delle query DAX, Ho mostrato come è possibile scrivere query per restituire tabelle e come combinare le funzioni della tabella per interrogare i dati. La prossima settimana introdurrò alcune nuove funzioni di query speciali che espandono ulteriormente le capacità.
