Come mostrare i valori di una misura soltanto in assenza di valori BLANK( ) in tutti i periodi

Ancora una volta, in questo articolo riportiamo un caso pratico di applicazione del DAX, facendo riferimento ad un reale caso di business.

Si supponga di volere mostrare le quantità vendute (o i valori di qualunque altra misura), cliente per cliente (o prodotto per prodotto, territorio per territorio e così via), soltanto per i clienti che hanno quantità vendute non vuote in tutti gli anni considerati (o mesi, trimestri e così via), nascondendo cioè i dati di tutti i clienti che hanno almeno un anno in cui tale quantità è BLANK().

Useremo Power Pivot per Excel per risolvere questo quesito, sapendo, tuttavia, che per passare a Power BI Desktop bastano pochi click.

Si faccia riferimento alla figura 1 per il modello dati in analisi.

In figura 2, sono visibili le prime righe della tabella Dati. Nelle figure 3 e 4, rispettivamente, sono mostrate le tabelle Anno e Azienda.

Come visibile nelle figure sopra riportate, ci sono 4 aziende clienti (A, B, C e D) e nove anni, dal 2013 al 2021 estremi inclusi. Le transazioni incluse nella tabella dei fatti Dati descrivono quantità comprate dalle aziende clienti con un anno di riferimento per ognuna. Complessivamente, tutte le transazioni avvengono negli anni dal 2013 al 2021 estremi inclusi. Non tutti i clienti, tuttavia, hanno transazioni in ognuno degli anni, come è visibile in figura 5, in cui in una Power Pivot accompagnata da uno slicer mostriamo, azienda cliente per azienda cliente e anno per anno, le quantità vendute. Il codice della misura QTY è mostrato a seguire.

QTY = SUM ( Dati[Qtà] )

Sviluppo

In figura 5 si nota che soltanto le aziende clienti A e C hanno valori della misura QTY non nulli in ognuno dei nove anni.

La sfida è identificare queste aziende clienti e nascondere i valori della misura QTY per tutte le altre, in ognuno degli anni. Il risultato desiderato è mostrato in figura 6 in cui, alla già presente misura QTY, è stata aggiunta la misura QTY tutti gli anni.

Come creare la misura QTY tutti gli anni? Come sempre, prima di scrivere del codice DAX, si ragioni su che tabelle o valori scalari è necessario avere per procedere.

Sarà necessario:

1 – disporre di un valore scalare che rappresenti il totale numero di anni (nove, nel nostro caso) indipendentemente dal filter context in essere;

2 – disporre, per ognuna delle aziende clienti, del numero di anni in cui ci sia almeno una transazione per la specifica azienda cliente considerata.

Avendo quanto listato nei punti 1 e 2, si potrà, azienda cliente per azienda cliente, aggregare con una somma il risultato della misura QTY solo quando il valore calcolato al punto 1 coincida con quello calcolato al punto 2 per l’azienda cliente in esame. In caso contrario, la misura QTY non sarà invocata.

Ecco il codice che implementa in DAX quanto progettato mentalmente:

QTY tutti gli anni =

Si notino i seguenti punti al riguardo della misura QTY tutti gli anni:

3 – l’uso di COUNTROWS ( ALL ( Anno[Anno] ) ) può risultare criptico. Perché non usare COUNTROWS ( ALL ( Anno ) ) ? In effetti, la tabella Anno è una dimensione con una sola colonna che è anche chiave primaria. Il codice usato, tuttavia, vuole ricordare che è sempre bene selezionare il numero minimo di colonne necessarie per il calcolo in esame. Dunque, si è preferito lasciare questo codice, in modo da inquadrare come procedere per listare gli anni nel modo più efficiente possibile nel caso di uso di una vera e propria tabella Calendario o comunque di una tabella temporale con granularità più fine di Anno;

4 – l’uso di CALCULATETABLE è necessario per fare scattare la context transition e filtrare, così, la tabella Dati per il valore correntemente iterato di VALUES ( Azienda [Azienda] ) aggiungendo tale filtro a quelli già presenti nel filter context

5 – la misura ha un funzionamento non necessariamente utile se si selezionano soltanto alcuni anni attraverso lo slicer, si osservi la figura 7. Pur avendo tutte le imprese dati nei tre anni selezionati, soltanto quelle che hanno dati in tutti i nove anni (ancora una volta, A e C) mostrano dati;

6 – la misura QTY tutti gli anni funziona bene al totale soltanto se il calcolo che viene aggregato azienda per azienda e anno per anno è additivo come nel caso in esame (QTY è una misura additiva che aggrega tramite SUM). Infatti, la misura QTY tutti gli anni usa una SUMX – e non funzionerebbe bene, per fare un esempio, nel caso di un calcolo aggregato, azienda per azienda e anno per anno, facente uso di DISTINCTCOUNT che non è additivo per definizione e risulterebbe in un valore errato. Un progetto ben fatto, è bene ribadirlo, deve considerare l’eventuale non additività.

Nel caso la community fosse interessata a risolvere i punti 5 e 6, invitiamo i lettori a comunicarlo attraverso i commenti e verranno prodotti altri articoli con le relative soluzioni.

Conclusioni

Il codice che risolve lo scenario proposto non è particolarmente complesso. Tuttavia, per produrlo, bisogna avere chiaro cosa si vuole fare e conoscere i fondamenti del DAX come i contesti di valutazione, la context transition e gli iteratori (di cui SUMX è una autorevole rappresentante). Ancora una volta, ribadiamo che per lavorare bene col DAX è necessario prima disegnare a mano o con la mente le tabelle da iterare – e i valori da calcolare in eventuali colonne aggiuntive – ed elencare i valori scalari necessari. Una volta chiaro il piano, si tratta di trovare le funzioni DAX che risolvono lo specifico passaggio tecnico che traduce il progetto in realtà. No, non è finita: bisogna anche avere dimestichezza con le funzioni ed il loro uso pratico ed è per questo che suggeriamo di crescere col DAX mediante una continua oscillazione tra teoria e pratica.

Autore del Post