Tabelle espanse in LuckyTemplates

In questo tutorial, daremo un'occhiata a uno scenario molto specifico che sono sicuro che tutti voi avete già incontrato lavorando con DAX: la tabella espansa . Le tabelle espanse possono rovinare i tuoi calcoli se non sai come funzionano effettivamente. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Per questo esempio viene utilizzato il database Contoso, che contiene la tabella delle vendite , la tabella dei clienti , la tabella delle date , la tabella dei prodotti , la tabella delle categorie dei prodotti e la tabella delle sottocategorie dei prodotti .

Sul lato sinistro, abbiamo una relazione uno-a-molti tra i prodotti, la categoria, la sottocategoria e le tabelle di vendita. Abbiamo anche una relazione uno-a-molti tra cliente e vendite, nonché date e vendite.

Sommario

• Esempio 1
• Esempio #2
• Vendite rosse n. 1
• Vendite rosse n. 2
• Vendite rosse #3
• Verifica delle tabelle espanse
• Definizione di tabelle espanse
• Affettare la colonna delle categorie e utilizzare le tabelle espanse
• Spiegare le vendite rosse 2
• Spiegare le vendite rosse 3
• Utilizzo di DAX Studio per verificare le tabelle espanse
• Conclusione

Esempio 1

Supponiamo di voler creare un report da questa colonna di categoria nella tabella delle categorie e utilizzare una misura che calcola quanti clienti ci sono nella tabella dei clienti. Vogliamo suddividere il numero per il tavolo del cliente in base alla categoria.

Se inserisco un filtro nella tabella delle categorie, quel filtro andrà alla tabella delle sottocategorie, quindi raggiungerà la tabella dei prodotti e infine raggiungerà e filtrerà la tabella delle vendite. Ma quel filtro non sarà in grado di filtrare la tabella del cliente a meno che non attiviamo il filtro bidirezionale.

Torniamo al rapporto per correggere questo calcolo. Possiamo racchiudere questo calcolo all'interno della funzione CALCULATE e quindi scrivere Sales. Possiamo vedere che questa volta non stiamo ripetendo lo stesso numero per ogni cella dell'immagine.

Per verificare se questo calcolo è corretto, possiamo inserire la chiave del cliente dalla tabella delle vendite per aggregarla, quindi eseguire un conteggio (distinto) su di essa.

Puoi vedere che stiamo restituendo lo stesso valore per ogni riga. Utilizzando la tabella delle vendite all'interno della funzione CALCULATE, siamo stati in grado di correggere il calcolo.

Esempio #2

Passiamo al prossimo esempio. Per questo, vogliamo identificare l'importo delle vendite per i prodotti rossi nel 2007, 2008 o 2009. A seconda della selezione dell'affettatrice, voglio anche tornare indietro di un anno.

Ad esempio, se seleziono 2009, voglio riportare le vendite in rosso per il 2008. Se seleziono 2008, voglio anche segnalare le vendite in rosso per il 2007.

Vendite rosse n. 1

Il calcolo delle nostre vendite totali è fondamentalmente una somma dei dati di vendita. Nel contesto della riga, moltiplichiamo la quantità per il prezzo netto.

Creiamo una nuova misura e chiamiamola Red Sales. Scriveremo CALCULATE, quindi Total Sales. FILTRIAMO tabella Sales per mostrare che il colore dei prodotti è uguale al rosso.

Quindi scriveremo sopra le date nella colonna della data.

Quando portiamo quel calcolo in una visuale della carta, otteniamo uno spazio vuoto.

Vendite rosse n. 2

Cosa sta succedendo qui? Proviamo a riscrivere quel calcolo e vediamo se siamo in grado di ottenere un risultato. Creeremo una nuova misura e la chiameremo Red Sales 2 e utilizzeremo la prima parte del calcolo per Red Sales 1.

Inizieremo un altro CALCULATE sopra il primo CALCULATE.

Riportiamo questa misura nella matrice e vediamo i risultati. Se selezioniamo 2008, otteniamo 51.947. Se selezioniamo 2009, otteniamo 24.343. Infine, se selezioniamo 2010, otteniamo 39.724.

Vendite rosse #3

C'è un altro modo per scrivere questo calcolo. Scriveremo una nuova misura e la chiameremo Red Sales 3, quindi utilizzeremo la funzione CALCULATE.

Calcoleremo le vendite totali e scriveremo che per i prodotti il ​​colore è uguale al rosso. Quindi utilizzare la funzione SAMEPERIODLASTYEAR sulle date.

Se trasciniamo la misura n. 3 nell'immagine della scheda, puoi vedere che questi due valori della scheda restituiscono lo stesso valore, il che è corretto.

Ma c'è qualcosa che non va nel nostro primo calcolo, perché restituiamo uno spazio vuoto e non il valore corretto.

Vediamo cosa sta realmente accadendo qui. Ora che abbiamo visto un paio di calcoli su tabelle espanse, capiamo la teoria dietro di loro.

Prima di capire cos'è una tabella espansa, devi capire che tutte le tabelle che abbiamo qui sono chiamate tabelle di base .

Quindi, quando queste tabelle diventano tabelle espanse? Dopo aver creato una relazione molti-a-uno tra una tabella e un'altra tabella, la tabella di base diventa la tabella espansa.

Verifica delle tabelle espanse

Ma come possiamo verificare che si stia effettivamente verificando un'espansione della tabella? Bene, puoi usare la parola chiave correlata su qualsiasi tabella. Se sei in grado di accedere alla colonna da un lato, saprai che può verificarsi un'espansione della tabella.

Andiamo alla tabella delle vendite e creiamo una nuova colonna calcolata.

Supponiamo di voler recuperare il colore del prodotto dalla tabella dei prodotti per questo particolare codice prodotto. Useremo RELATED, che fornirà solo l'elenco delle colonne in IntelliSense che possono essere effettivamente espanse dalla tabella delle vendite.

Possiamo vedere che la tabella dei clienti e la tabella delle vendite hanno una relazione molti-a-uno. Possiamo anche vedere l'elenco delle colonne della tabella dei clienti, delle colonne della tabella delle date e delle colonne dei prodotti.

Quando selezioniamo Products[Color], possiamo creare una nuova colonna nella tabella delle vendite con l'aiuto della parola chiave RELATED. RELATED ci dà accesso solo alle colonne della tabella in cui la tabella di base può effettivamente espandersi.

Se cambiamo la natura di quella relazione da molti a uno a molti a molti, quel calcolo smetterà di funzionare.

Cambiamo la natura di questa relazione in molti a molti. Possiamo vedere in fondo che otteniamo un simbolo di avviso.

Quando torniamo alla tabella delle vendite, vediamo il messaggio di errore che dice che la colonna Products[Color] non esiste o non ha una relazione con nessuna tabella.

Nel contesto attuale, l'avviso che riceviamo non è molto intuitivo. Fondamentalmente, significa che la tabella delle vendite non può espandersi nella tabella dei prodotti perché non siamo in grado di accedere a un solo valore per quella particolare riga.

E poiché stiamo usando la relazione molti-a-molti, l'espansione della tabella non avviene e RELATED non funziona.

Torniamo alla visualizzazione del diagramma e correggiamo questo calcolo. Cambieremo la natura della relazione in molti a uno e attiveremo quella relazione per far funzionare il calcolo.

Definizione di tabelle espanse

Prima di iniziare a guardare i calcoli che abbiamo già fatto nel report, ribadiamo la definizione della tabella espansa.

Se disponi di un modello di dati con uno schema a stella , la tabella dei fatti si espanderà a tutte le tabelle nel modello di dati se esiste una relazione molti-a-uno tra le dimensioni e la tabella dei fatti.

Se disponi di uno schema a fiocco di neve , la tabella delle sottocategorie dei prodotti e la tabella delle categorie si espanderanno nella tabella di base, che in questo caso è la tabella dei prodotti. La tabella delle vendite è la tabella di base, che si espande a tutte le altre tabelle.

Dietro le quinte, la tabella delle vendite avrà tutte le colonne in un'unica tabella. Nota che l'espansione della tabella è solo un concetto logico, quindi non si espanderà e non aumenterà le dimensioni del tuo modello di dati.

La tabella espansa entra in gioco solo quando si fa riferimento a una tabella (che è la tabella di base) e quando si ha una relazione molti-a-uno con altre tabelle.

Affettare la colonna delle categorie e utilizzare le tabelle espanse

Proviamo a correggere i calcoli che abbiamo già fatto nella visualizzazione report. In questo esempio, stiamo tagliando in base alla colonna della categoria dalla tabella delle categorie di prodotti e stiamo cercando di contare quanti clienti ci sono.

Quindi cosa stiamo facendo davvero qui? Utilizziamo la tabella delle vendite espansa come riferimento. Una volta che il contesto del filtro contiene un valore dalla tabella delle categorie, quel filtro raggiungerà la tabella delle vendite dai prodotti della sottocategoria e direttamente alle vendite.

Poiché le vendite sono una tabella espansa e stiamo utilizzando tale riferimento all'interno della funzione CALCULATE, la tabella delle vendite conterrà anche la colonna della tabella dei clienti. Quando applichiamo un filtro sulla tabella delle vendite, indirettamente, filtriamo anche la tabella dei clienti.

Torniamo ai nostri calcoli di vendite rosse e cerchiamo di capire cosa sta realmente accadendo. Inizieremo con il calcolo in bianco. Se selezioniamo quella misura, puoi vedere che stiamo scrivendo un codice annidato. Abbiamo CALCULATE, quindi Total Sales, FILTER sulle vendite e SAMEPERIODLASTYEAR.

Rompiamo quel calcolo passo dopo passo. Innanzitutto, dobbiamo identificare il contesto del filtro esterno che esiste al di fuori del calcolo.

Abbiamo selezionato 2008 nell'affettatrice del numero dell'anno di calendario.

Da questo contesto di filtro, verrà valutata la tabella delle vendite. La tabella delle vendite conterrà solo la riga per il numero dell'anno solare 2008 e il colore del prodotto sarà rosso. Abbiamo due filtri qui: uno che viene creato dal filter context e uno che viene creato dal filter .

SAMEPERIODLASTYEAR viene valutato nel contesto del filtro in cui l'anno è 2008. Riceverà un elenco di date nel 2008 e sposterà quelle date nel 2008 al 2007. La tabella che verrà restituita da questo conterrà solo le date del 2007

Una volta completate queste due operazioni, la funzione CALCULATE prepara il contesto del filtro e applica questi due filtri nel contesto del filtro. Quando lo applichiamo, abbiamo un contesto di filtro in cui il colore del prodotto è uguale al rosso e, nella colonna dell'anno, abbiamo un filtro sul 2007 e 2008.

Quindi, per questo modello di dati, non esiste una singola transazione esistente in due anni diversi. Quando CALCULATE tenta di unire questi due filtri in una condizione and , dirà che l'anno dovrebbe essere il 2008 e il 2007 e che il colore del prodotto dovrebbe essere rosso.

Quando filtriamo la tabella delle vendite con il contesto del filtro dell'anno 2008, filtriamo indirettamente anche la tabella delle date. La stalla delle vendite ha una relazione molti-a-uno con la stalla della data.

Creiamo un nuovo calcolo per identificare quante righe ci sono nella tabella delle date per le date correlate. Scriveremo CALCULATE, quindi della tabella delle date. Quindi FILTREREMO TUTTA la tabella delle vendite e diremo che le date CORRELATE nel numero dell'anno solare dovrebbero essere uguali a 2008.

All'interno di CALCULATE, non abbiamo fatto riferimento ad alcun filtro sulla tabella delle date. Stiamo semplicemente controllando che le date nel numero dell'anno solare siano a partire dal 2008. Idealmente, questo filtro non dovrebbe essere in grado di filtrare la tabella delle date. Stiamo anche usando la funzione , che ignorerà il contesto del filtro che verrà dall'affettatrice.

Creiamo una nuova scheda per questo calcolo. puoi vedere che stiamo restituendo 348 righe.

Allora com'è possibile che da una tabella di date di 2500 righe, stiamo restituendo solo 348 righe? Se utilizzeremo una tabella espansa, filtreremo indirettamente anche l'altra tabella, che è connessa tramite una relazione molti-a-uno.

Sebbene non abbiamo alcun filtro sull'anno in corso, stiamo ancora limitando il numero di righe visibili per la tabella delle dimensioni che abbiamo da un lato.

Quando restituiamo la tabella delle vendite, restituiamo anche una versione filtrata della tabella delle date, della tabella dei clienti, della tabella dei prodotti, della tabella delle categorie di prodotti e della tabella delle sottocategorie dei prodotti.

Spiegare le vendite rosse 2

Passiamo al calcolo successivo, che è Red Sales 2. Inizieremo con l'esterno CALCULATE perché se annidiamo questa funzione in qualsiasi scenario, l'esterno CALCULATE dovrebbe preparare il contesto del filtro per l'interno CALCULATE.

Nell'affettatrice, selezioniamo l'anno solare 2008. Quando la funzione SAMEPERIODLASTYEAR ottiene la data per il 2008, sposterà quelle date nel 2007 e sarà il contesto FILTER per il calcolo interno. Questo CALCOLO interno valuterà l'anno 2007.

La funzione FILTER filtrerà la tabella delle vendite e la tabella delle vendite sarà limitata solo per la riga in cui l'anno è 2007. Una volta che abbiamo le righe per il 2007, verificheremo che i prodotti correlati siano uguali al rosso.

A differenza del primo calcolo, non restituiamo due livelli diversi. Questi due diversi livelli non vengono applicati al contesto FILTER, quindi la tabella restituita dalla funzione FILTER conterrà tutte le righe della tabella delle vendite.

Una volta applicato questo filtro, otterremo l'importo delle vendite per l'anno 2007, nonché i prodotti uguali al rosso, mentre nel primo calcolo restituiamo l'anno 2008 e 2007.

Questa volta abbiamo preparato il contesto del filtro per la tabella delle vendite utilizzando la funzione SAMEPERIODLASTYEAR e nidificando la funzione CALCULATE all'interno di un'altra CALCULATE.

Spiegare le vendite rosse 3

Per il terzo esempio, abbiamo una funzione CALCULATE molto semplice con due istruzioni: una è il colore del prodotto è uguale al rosso, e poi SAMEPERIODLASTYEAR sulle date.

La funzione SAMEPERIODLASTYEAR verrà valutata in un contesto di filtro, che conterrà solo le date del 2007. Il colore dei prodotti applicherà un contesto di filtro rosso sulla tabella dei prodotti, che a sua volta filtrerà la tabella delle vendite e conterrà solo le righe per i prodotti rossi.

Quando questi due valori vengono applicati nel contesto del filtro, filtreranno la tabella delle vendite ma non la tabella delle vendite espansa. puoi vedere che da nessuna parte nel codice abbiamo fatto riferimento alla tabella delle vendite espansa.

Questo è lo scenario ideale da utilizzare se si sta tentando di fare riferimento alla tabella espansa. Quando si utilizza la tabella espansa, i calcoli possono diventare davvero contorti e talvolta non è possibile identificare il motivo per cui i calcoli restituiscono un risultato errato.

Se non si comprende il concetto di tabelle espanse, è possibile sviluppare questo particolare codice come misura del modello e quindi distribuirlo in produzione. Altri utenti potrebbero iniziare a creare altre misure su questa misura e potrebbero non essere in grado di capire perché i calcoli non funzionano perché semplicemente non possiedono quel livello di comprensione del linguaggio DAX.

Se stai lavorando con DAX, prova sempre a mettere il filtro su una singola colonna perché quando usi una singola colonna, il concetto di tabelle espanse non viene applicato. Ad esempio, quando utilizzi PRODUCTS [Color] = “Red” , il filtro raggiunge la tabella delle vendite, ma tale filtro non può raggiungere la tabella del cliente perché non stiamo utilizzando la tabella delle vendite espansa.

Quando si utilizza una singola colonna, non si fa riferimento al concetto di tabelle espanse. Stiamo semplicemente applicando un filtro di base sulla tabella delle vendite, che non si propaga alle altre tabelle.

Utilizzo di DAX Studio per verificare le tabelle espanse

Prima di concludere, utilizzeremo DAX Studio per verificare il nostro codice per Red Sales 3. Andiamo alla scheda Visualizza, facciamo clic su Performance Analyzer, quindi avviamo la registrazione. Aggiorneremo l'oggetto visivo e selezioneremo una scheda vuota.

Dagli strumenti esterni, lanceremo DAX Studio

Possiamo vedere che nella schermata dei risultati in basso, otteniamo uno spazio vuoto.

Attiveremo i tempi del server per comprendere le query che vengono generate dietro le quinte. Andremo alla scheda Server Timings e la espanderemo per vedere tutto.

Stiamo eseguendo un JOIN sul tavolo delle vendite sul lato sinistro. Stiamo anche eseguendo un JOIN sulla chiave del prodotto dalla tabella del prodotto.

Nella clausola WHERE, si dice che le date nel numero dell'anno solare dovrebbero essere uguali al 2008 e che il colore del prodotto dovrebbe essere rosso.

Quindi abbiamo un'altra condizione che dice Dates[Date], quindi seguita da un intervallo di date. Quindi quali sono queste date? Idealmente, queste date devono appartenere al 2008, ma risalgono al 2007 quando cambiamo la formattazione di questi numeri in Excel.

Questo è il motivo per cui restituiamo un valore vuoto. Stiamo dicendo che l'anno solare dovrebbe essere il 2008 e applicando una condizione AND che le date dovrebbero essere nel 2007. Questo 2007 è dovuto alla funzione SAMEPERIODLASTYEAR che abbiamo utilizzato nel calcolo.

Cambiamo la misura e facciamo riferimento a Red Sales 2. Una volta eseguito il codice, puoi vedere che stiamo ottenendo le date dalla tabella delle date, quindi stiamo filtrando la colonna delle date per il numero dell'anno solare 2008.

Questa volta, siamo in grado di restituire un risultato perché non stiamo applicando un filtro sulla clausola WHERE e stiamo dicendo che le date nel numero dell'anno solare dovrebbero essere uguali a 2008.

Dove trovare la trappola DAX nascosta
Contare i clienti nel tempo utilizzando DISTINCTCOUNT in LuckyTemplates
Analisi dei nuovi clienti utilizzando DAX avanzato

Conclusione

Si spera che questo tutorial sia stato utile per capire cos'è una tabella espansa e come può rovinare i tuoi calcoli. Le tabelle espanse non sono molto intuitive e nemmeno facili da capire.

Per ulteriori informazioni sulle tabelle espanse, non dimenticare di iscriverti al canale TV LuckyTemplates. Abbiamo un'enorme quantità di contenuti che escono continuamente da me e da una serie di creatori di contenuti, tutti dedicati a migliorare il modo in cui utilizzi LuckyTemplates e Power Platform.