Contrassegnare una tabella come tabella data in DAX

Una delle opzioni più oscure e di difficile comprensione in Tabular è chiamata Contrassegna come tabella data. L’opzione riguarda la time-intelligence in DAX ed è, dunque, molto rilevante sia in Power Pivot per Excel che in Analysis Services Tabular che in Power BI. In questo articolo verrà spiegato passo-passo tutto ciò che serve per gestire in piena consapevolezza questa opzione e le sue ripercussioni sul DAX.

Il primo modello che sarà considerato è visibile in figura 1.

Si noti che la relazione tra Calendar e Sales si basa su una colonna di tipo data, ‘Calendar[Date], e, lo si specifica qui, che l’opzione Contrassegna come tabella data non è stata usata.

Sviluppo

Si considerino una misura che calcola l’importo del ricavi, Sales, e un’altra che ne calcola la versione cumulata sull’anno, Sales_DATESYTD, che usa DATESYTD, la classica funzione DAX di time intelligence per il calcolo del valore cumulato di un’espressione in un anno (figura 2).

Le due misure funzionano alla perfezione. I calcoli, in figura 2, sono suddivisi per anno e mese, rispettivamente le colonne ‘Calendar'[CalendarYear] e ‘Calendar'[CalendarYearMonth]. Il codice delle misure è il seguente:

Sales =

Sales_DATESYTD =

La misura Sales_DATESYTD fa riferimento ad una colonna calcolata, ‘Calendar'[Past Today Future] che serve ad identificare le date future per escluderle dal calcolo cumulato in modo elegante, evitando chiamate a IF. Il concetto di data futura è stato qui legato alla massima data di ordine che rappresenta il presente (Today), ecco il codice della colonna:

Past Today Future =

Si provi, adesso, a creare la stessa misura di ricavi cumulati nell’anno usando il DAX in esplicito, ecco il codice proposto:

Sales_YTD_Manuale =

La misura, in effetti, funziona (figura 3).

Tuttavia, non dovrebbe affatto funzionare. Esaminando il codice e stando attenti al filter context, si nota in figura 3 che i filtri presenti all’avvio del calcolo sono, in ogni riga di un singolo mese, per esempio marzo 2003 (200303), due: un valore per ‘Calendar'[CalendarYear] pari a 2003 e uno per ‘Calendar'[CalendarYearMonth] pari a 200303. La misura Sales_YTD_Manuale lavora, tramite CALCULATE, soltanto sul (cross)filtro presente sulla data, sostituendone i valori con quelli di tutte le date a partire dal primo gennaio dell’anno della massima data visibile fino alla massima data visibile, nel caso di marzo 2003, rispettivamente, 1/1/2003 e 31/3/2003. Tuttavia, CALCULATE non ha rimosso i filtri da anno e mese, dunque il risultato atteso era di vedere gli stesi numeri della misura Sales.

Perché non è così?

La risposta sta nel fatto che la relazione uno a molti tra la tabella Sales e la tabella Calendar è basata su una colonna di tipo data. Quando questo avviene e, inoltre, viene sovrascritto un filtro su questa colonna (si ricorda che deve essere di tipo data ed essere la colonna alla base della relazione), Tabular aggiunge in automatico, una chiamata a REMOVEFILTERS che rimuove tutti gli altri filtri eventualmente presenti sulle colonne dalla tabella lato 1. La stessa cosa avviene se la colonna, coinvolta nella relazione e su cui viene sovrascritto il filtro, è di tipo data/ora.

Per mostrare in modo plastico tale comportamento, è utile provocarne uno veramente singolare. Si esamini la misura Sales_Filtro_Date, eccone il codice:

Sales_Filtro_Date =

Cosa ci si aspetta da questo codice? Non dovrebbe produrre valori diversi da quelli prodotti dalla misura Sales, visto che sostituisce il filtro sulla data con un altro in cui la data deve essere compresa tra la minima e la massima data delle vendite dell’intero dataset, un filtro che non dovrebbe avere alcun effetto sui numeri. Invece, otteniamo un risultato sorprendente (figura 4):

Cosa è successo? Ebbene, la chiamata a REMOVEFILTERS, aggiunta in automatico, ha eliminato il filtro da ‘Calendar'[CalendarYear] e ‘Calendar'[CalendarYearMonth], lasciando soltanto il filtro introdotto dalla misura sulla colonna ‘Calendar'[Date] che comprende tutte le date delle vendite. Dunque, in figura 4, osserviamo il totale dei ricavi dell’intero dataset in ogni intervallo di tempo.

Che succede se l’operazione di modifica del filtro viene fatta quando la colonna di collegamento non è di tipo Data? Per verificarlo, basta creare una misura simile a Sales_Filtro_Date ma che lavori su una colonna di tipo diverso da data.

Ecco il codice della misura Sales_Filtro_ProductKey che modifica il filtro sulla colonna ‘Product'[ProductKey] che non è di tipo data e che è coinvolta nella relazione tra Product e Sales (si veda la figura 1):

Sales_Filtro_ProductKey =

Osserviamo la misura in azione e notiamo che, questa volta, ha il comportamento atteso: nessuna differenza rispetto alla misura Sales, del resto la REMOVEFILTERS non può essere stata aggiunta, visto che la colonna su cui è basata la relazione e su cui viene modificato il filtro non è di tipo data (Figura 5).

Adesso è il momento di tornare all’oggetto di questo articolo: a cosa serve l’opzione Contrassegna come tabella data? Per capirlo, passiamo ad un modello in cui la relazione tra Calendar e Sales è stabilita mediante una colonna, ‘Calendar'[DateKey], di tipo diverso da data – testo, in particolare – e, ancora una volta, l’opzione Contrassegna come tabella data non è stata usata (figura 6).

In figura 7 sono mostrate le stesse misure della figura 4, ma questa volta sono tutte indistinguibili da Sales, come era da aspettarsi. Si ricorda che il valore al Totale, per le misure Sales_DATESYTD e Sales_YTD_Manuale, è stato nascosto volutamente, come era evidente già in figura 4.

Infatti, essendo la colonna, di tipo data, su cui viene sovrascritto il filtro, ‘Calendar'[Date], non coinvolta nella relazione, non viene aggiunta nessuna chiamata a REMOVEFILTERS.

In questo caso, quindi, come fare ad ottenere il risultato desiderato? Bisogna aggiungere REMOVEFILTERS al codice, come si sarebbe sempre dovuto fare, in assenza del comportamento descritto. Tuttavia, è un’operazione piuttosto tediosa e, inoltre, perché non cercare alternative, se ce ne sono?

Per prima cosa, qui a seguire un tentativo scritto coinvolgendo ‘Calendar'[DateKey] nel codice. ‘Calendar'[DateKey] è, in effetti, la colonna usata nella relazione, tuttavia essa non è di tipo data e, di conseguenza, a seguito della sovrascrittura del filtro su di essa, il codice crea valori, ancora una volta, indistinguibili da quelli prodotti dalla misura Sales (figura 8).

SalesYTD_Manuale_DATEKEY =

Per ottenere i calcoli desiderati, basta aggiungere una chiamata a REMOVEFILTERS. Ecco il codice funzionante, che usa la colonna coinvolta nella relazione nel sovrascrivere il filtro, colonna che non è di tipo data:

SalesYTD_Manuale_DATEKEY_REMOVEFILTERS =

I valori calcolati sono osservabili in figura 9, adesso è stato ottenuto quanto desiderato.

Finalmente, è il momento di andare al cuore dell’articolo: l’opzione Contrassegna come tabella data. Questa opzione, attivabile su qualunque tabella abbia almeno una colonna chiave primaria di tipo data o data/ora, serve ad ottenere lo stesso comportamento che si ottiene se si sovrascrive il filtro su una colonna di tipo data o data ora, coinvolta in una relazione, anche quado la colonna coinvolta nella relazione è di tipo diverso da data, in altri termini proprio la situazione descritta dalla figura 6 in poi. Ciò eviterà di dovere usare REMOVEFILTERS. Attenzione: resta il fatto che la colonna su cui viene sovrascritto il filtro deve essere di tipo data affinché venga aggiunta la chiamata a REMOVEFILTERS, però non è più necessario che questa colonna di tipo data sia anche la colonna coinvolta nella relazione.

In altre parole, anche dopo avere attivato l’opzione Contrassegna come tabella data, la misura SalesYTD_Manuale_DATEKEY continuerà a mostrare gli stessi valori di Sales. Anche la misura SalesYTD_Manuale_DATEKEY_REMOVEFILTERS, una volta attivata l’opzione, non modificherà il suo comportamento, per la stessa ragione.

Le misure che cambieranno comportamento, dopo avere attivato l’opzione, sono Sales_DATESYTD, Sales_YTD_Manuale e Sales_Filtro_Date che mostreranno, rispettivamente, i valori cumulati della misura Sales nell’anno, per le prime due, e il valore di Sales per l’intero dataset per l’ultima, cioè queste tre misure si comporteranno come in figura 4.

Per attivare l’opzione Contrassegna come tabella data, tipicamente su una tabella di tipo calendario, si procede cliccando sulla vista dati, selezionando poi la tabella di interesse e cliccando sull’opzione apposita (figura 10).

Infine, si deve indicare una colonna di tipo data che sia chiave primaria della tabella (figura 11).

Ecco il comportamento delle misure in figura 9, una volta attivata l’opzione (figura 12).

Conclusioni

L’opzione Contrassegna come tabella data serve ad estendere un comportamento standard di Tabular: quando la relazione uno a molti tra due tabelle è basata su una colonna di tipo data o data/ora e, inoltre, viene sovrascritto un filtro su questa colonna viene aggiunta in automatico, una chiamata a REMOVEFILTERS che rimuove tutti gli altri filtri eventualmente presenti sulle colonne dalla tabella lato 1 della relazione. L’opzione estende questo comportamento al caso in cui la relazione sia basata su una colonna di tipo diverso da data. Resta il fatto, tuttavia, che la colonna su cui viene sovrascritto il filtro deve essere di tipo data affinché venga aggiunta la chiamata a REMOVEFILTERS, però, una volta attivata l’opzione Contrassegna come tabella data non è più necessario che questa colonna di tipo data sia anche la colonna coinvolta nella relazione. È una best practice attivare questa opzione, sempre.

Si ricordi, un’ultima volta, che l’opzione è inefficace nel caso in cui il filtro venga sovrascritto su una colonna di tipo diverso da data, in quel caso la chiamata a REMOVEFILTERS deve comunque essere aggiunta al codice.

Nota a lato: in ogni progetto, è bene deselezionare l’opzione di data/ora automatica (settaggio opzionale di Power BI Desktop, i dettagli sono disponibili nel video associato a questo articolo) e verificare che le colonne di date siano di tipo data e non data/ora. A meno che il tipo di dato data/ora non sia di effettivo interesse, è bene non avere l’orario. Nel caso l’orario fosse di interesse, è meglio creare due colonne separate, una solo con la data (di tipo data) e una soltanto con l’orario (di tipo ora). Attenzione: creare una relazione tra una colonna di tipo data e una di tipo data/ora può generare malfunzionamenti difficili da riconoscere.

Autore del Post