Models DAX a Power Pivot: fórmules, context i bones pràctiques

Si treballes amb dades a Excel i PowerPivot, tard o d'hora et creuaràs amb DAX. Encara que el nom “Expressions d'anàlisi de dades” soni tècnic i una mica intimidant, la realitat és que, entenent bé els fonaments, es converteix en una eina molt còmoda i potent per modelar informació.

A Power Pivot, DAX és el cor dels models tabulars: serveix per crear columnes calculades i mesures que alimenten taules dinàmiques i gràfics, permet jugar amb el context de filtres, treballar amb dates, manejar errors, fer comparacions a el temps i molt més. A veure amb calma com encaixa tot això dins dels models DAX a Power Pivot i com treure-li partit sense tornar-te boig.

Què és DAX i per què és clau en els models de Power Pivot

DAX (Data Analysis Expressions) és, sobretot, un llenguatge de fórmules dissenyat per a models de dades, no un llenguatge de programació clàssic. La seva funció és definir càlculs personalitzats que s'apliquen sobre taules i columnes del model de Power Pivot.

Tot i que visualment pot recordar les fórmules d'Excel, DAX està pensat per treballar amb dades relacionals i amb agregacions dinàmiques. Això significa que s'integra de meravella amb taules connectades per relacions, taules dinàmiques i models complexos on el context de filtre canvia segons els camps que facis servir en files, columnes o segmentacions.

Dins d'un model a Power Pivot, pots fer servir DAX per dos grans tipus d'objectes: columnes calculades i mides. Tots dos conviuen dins del mateix model, però cadascú té un propòsit diferent i s'avalua de manera diferent, cosa que convé tenir molt clara per no barrejar conceptes.

A més, DAX inclou funcions de text, de data i hora, lògiques, matemàtiques, de filtre i “intel·ligència de temps”, així com funcions que tornen taules completes com a resultat. Aquestes últimes són una de les grans diferències respecte a Excel, on les fórmules no tornen taules com a tal, sinó com a molt matrius dins de rangs de cel·les.

Descripció general de les fórmules DAX i la barra de fórmules

Les fórmules DAX segueixen una estructura molt semblant a la de l'Excel: comencen pel signe igual (=) ia continuació s'escriu l'expressió o funció amb els arguments. Podeu combinar operadors, funcions, referències a columnes i taules, etc., igual que faries en construir una fórmula complexa en un full de càlcul.

Tot i això, hi ha matisos importants. DAX mai treballa amb referències del tipus A1:C10; al seu lloc es fa referència a columnes o taules completes, per exemple 'Vendes' o 'Calendari'. Això té molt de sentit en un model tabular, però obliga a canviar el xip si vens del món purament “cel·la a cel·la” d'Excel.

Power Pivot disposa d'una barra de fórmules similar a la d'Excel que facilita força la creació d'expressions. Disposa d'Autocompletar per a funcions, taules i columnes: conforme comences a escriure el nom d'una taula o columna, apareix una llista desplegable amb opcions vàlides, cosa que redueix errors de sintaxi i estalvia temps.

Per escriure noms de taula, només cal començar a teclejar i deixar que Autocompletar suggereixi els noms coincidents. Per a columnes, pots obrir un claudàtor i seleccionar la columna de la taula actual, o bé escriure el nom de la taula seguida de claudàtor i triar a la llista.

Això sí, a diferència d'Excel, Power Pivot no tanca automàticament els parèntesis ni els aparella per tu. És la teva responsabilitat deixar les funcions ben formades, amb el nombre correcte d'arguments i els parèntesis complets, perquè altrament la fórmula no es podrà guardar ni fer servir.

On es fan servir les fórmules DAX: columnes calculades i mesures

En un model de Power Pivot pots escriure fórmules DAX a columnes calculades i amb mesures (també anomenades camps calculats en el context de les taules dinàmiques). Tot i que comparteixen el mateix llenguatge, es comporten de maneres molt diferents.

Columnes calculades a Power Pivot

Una columna calculada és un camp nou que afegeixes a una taula existent del model. En lloc d'importar aquest valor des de l'origen de dades, definiu una fórmula DAX que s'avalua fila per fila. El resultat s'emmagatzema a la columna per a cada fila de la taula.

Les columnes calculades s'apliquen de manera uniforme a totes les files: no pots tenir una fórmula diferent per a cada fila, com sí que pots fer en Excel arrossegant i canviant parts a mà. A Power Pivot, l'expressió que definiu s'avalua automàticament per a tota la columna i es recalcula quan s'actualitzen les dades o es força un recàlcul del model.

Aquest tipus de columnes es pot basar en altres columnes calculades o en mides, encara que es recomana no reutilitzar el mateix nom per a mida i columna per evitar confusions en referenciar. És molt bona idea fer servir sempre la referència de columna completa (Taula) per no trucar sense voler a una mesura que s'anomeni igual.

Les columnes calculades són ideals quan necessites atributs addicionals que vulguis fer servir en files, columnes, filtres o segmentacions de taules dinàmiques, o com a claus per a relacions. Pensa, per exemple, en una columna “Marge” calculada com – que després puguis fer servir per agrupar o filtrar.

Mesures o camps calculats

Les mesures, per la seva banda, són càlculs que s'avaluen en el context d'una taula dinàmica o visualització. No s'emmagatzemen per fila, sinó que es recalculen al vol per a cada combinació de filtres, files i columnes que hagueu activat a l'informe.

Una mesura típica podria ser una cosa tan senzilla com:

TotalVendes = SUM(Vendes)

Aquesta mesura, col·locada a l'àrea de Valors de la taula dinàmica, s'avalua per a cada cel·la segons el context (per exemple, per any, per producte, per regió…). El mateix càlcul torna resultats diferents depenent dels filtres aplicats i del disseny de la taula dinàmica.

Les mesures no fan res fins que no es fan servir en un informe. S'emmagatzemen amb el model de dades i apareixen a la llista de camps de les taules dinàmiques perquè qualsevol usuari del llibre les pugui utilitzar. Són fonamentals per a càlculs agregats flexibles, com ara ràtios, percentatges de contribució, acumulats, comparacions entre períodes, etc.

Diferències clau entre funcions DAX i funcions d'Excel

Tot i que moltes funcions de DAX s'assemblen a les d'Excel en nom i comportament general, no són intercanviables sense més ni més. Hi ha diferències importants que afecten com es construeixen les fórmules en un model de Power Pivot.

En primer lloc, DAX no treballa amb cel·les soltes ni rangs; sempre s'usen columnes o taules senceres com a referència. Això us obliga a pensar més en termes de conjunts de dades que en elements individuals, cosa que encaixa millor amb l'enfocament d'un model de dades relacional.

A l'àmbit de dates, DAX torna valors de tipus datetime real, mentre que Excel sol representar les dates com a números de sèrie. Aquesta diferència és transparent en la majoria dels casos, però és important tenir-la al cap quan es combinen models o s'importen dades des d'altres sistemes.

Un altre punt clau és que moltes de les funcions noves de DAX tornen taules completes (per exemple, FILTER, ALL, VALUES, etc.) o bé accepten taules com a arguments. Excel, en canvi, no té un concepte de funció “que torni una taula” en el mateix sentit, encara que hi hagi fórmules matricials.

Finalment, a DAX s'assumeix que tots els valors d'una columna comparteixen el mateix tipus de dades. Si es barregen tipus, el motor de dades forçarà una conversió de tota la columna al tipus que millor encaixi per a tots els registres, cosa que de vegades pot generar sorpreses si no es revisen bé els orígens.

Tipus de dades a DAX i el tipus taula

Quan importeu informació en un model de Power Pivot, les dades es converteixen a un dels tipus de dades admeses pel motor (números, text, booleans, dates i hores, moneda, etc.). Aquest tipus de dades determina quines operacions són vàlides i com s'avaluaran les fórmules.

Una novetat important davant de l'Excel clàssic és el tipus de dades taula. Moltes funcions DAX accepten una taula completa com a argument i tornen una altra taula com a resultat. Per exemple, FILTER rep una taula i una condició, i torna una taula amb només les files que compleixen la condició.

Combinant funcions que tornen taules amb funcions d'agregació com SUMX, AVERAGEX o MINX, es poden construir càlculs molt sofisticats que treballen sobre subconjunts de dades definits de forma dinàmica. Això dóna lloc a agregacions personalitzades que s'adapten als filtres actius a cada moment.

Relacions, context i el model relacional a Power Pivot

La finestra de Power Pivot és el lloc on es construeix el model de dades relacional. Allí pots importar diverses taules i crear relacions entre elles (per exemple, Vendes amb Productes, Vendes amb Calendari, Vendes amb Clients, etc.). Aquestes relacions són la base perquè les fórmules DAX puguin saltar duna taula a una altra.

Quan les taules estan relacionades, pots escriure fórmules que afegeixin valors d'una taula relacionada i els utilitzeu a la taula des de la qual esteu escrivint l'expressió. També podeu controlar quines files participen en un càlcul aplicant filtres sobre columnes concretes.

És important tenir en compte que totes les files d'una taula de Power Pivot han de tenir el mateix número de columnes, i cada columna ha de mantenir un tipus de dades consistent en totes les seves files. Si les claus de relació tenen valors no coincidents (espais en blanc, valors orfes, etc.), les fórmules de cerca i les taules dinàmiques poden tornar resultats inesperats.

Un altre concepte fonamental és el context. A DAX es parla sobretot de context de fila i de context de filtre. El context de fila és la fila actual sobre la qual s'avalua una columna calculada o un iterador; el context de filtre és el conjunt de filtres actius (de la taula dinàmica, segmentacions, relacions, funcions com CALCULATE, etc.).

En jugar amb funcions com CALCULATE, ALL, ALLEXCEPT o FILTER, pots modificar el context de filtre per canviar com s'avalua una mesura. Això permet, per exemple, calcular el percentatge de vendes d'un producte respecte del total o comparar el rendiment d'una divisió amb el total de la companyia sense filtres.

Actualització de dades i recàlcul de fórmules DAX

En un model que utilitzi fórmules complexes o grans volums de dades, entendre com funciona lactualització és clau. Cal distingir entre refrescar les dades i recalcular les fórmules, que són processos relacionats però independents.

L'actualització de dades consisteix a portar al llibre nous registres des dels orígens externs (bases de dades, arxius, serveis en línia, etc.). Pots llançar aquesta actualització manualment quan ho necessitis, o programar-la si el llibre està publicat a Punt compartit o un altre entorn compatible. Moltes vegades aquest procés es realitza usant Power Query a Excel per preparar i transformar les dades abans de carregar-les al model.

El recàlcul, en canvi, és el procés pel qual es tornen a avaluar les fórmules DAX per reflectir canvis en les dades o en les expressions pròpies. Per a les columnes calculades, si canvies la fórmula, cal recalcular tota la columna de cop. Per a les mesures, el recàlcul es produeix quan es modifica el context (filtres, camps de files/columnes de taula dinàmica) o s'actualitzen manualment les taules dinàmiques.

Aquests recàlculs poden tenir impacte en el rendiment, sobretot si es fan servir moltes columnes calculades complexes o funcions iteratives intensives en taules grans. Per això, una bona pràctica és moure la major part de la lògica a mides en lloc de columnes, sempre que sigui possible.

Detecció i correcció d'errades en fórmules DAX

En escriure fórmules DAX és habitual trobar tres tipus d'errors: errors sintàctics, errors semàntics i errors de càlcul. Cadascú té la seva casuística i la seva manera de corregir-se.

Els errors de sintaxi són els més senzills: parèntesis que falten, comes mal col·locades, noms de funcions mal escrits, etc. L'ajuda d'Autocompletar i la referència de funcions DAX t'estalvien moltes d'aquestes ensopegades.

Els errors semàntics i de càlcul apareixen quan, encara que la sintaxi sigui correcta, la fórmula fa una cosa que no té sentit en el context del model. Per exemple, referenciar una taula o una columna inexistent, passar un nombre incorrecte d'arguments a una funció, barrejar tipus incompatibles o dependre d'una columna amb errors previs.

En aquests casos, DAX sol marcar tota la columna calculada com a errònia, no només una fila concreta, perquè la columna es considera una unitat. Si una columna només té metadades però encara no s'ha processat (no té dades carregades), apareixerà en gris i les fórmules que en depenguin no es podran avaluar correctament.

Un cas especial són els valors NaN (no és un nombre), que poden aparèixer, per exemple, en dividir 0 entre 0. Si una columna conté NaN, en ordenar o classificar aquests valors es poden obtenir resultats estranys, perquè NaN no es pot comparar de manera habitual amb altres números. En aquests casos, convé fer servir IF o altres funcions lògiques per substituir els NaN per 0 o un altre valor numèric manejable.

Compatibilitat amb models tabulars i mode DirectQuery

Les fórmules DAX que crees a Power Pivot són, en general, compatibles amb els models tabulars de SQL Server Analysis Services. Això vol dir que pots migrar el teu model a un servidor tabular i continuar aprofitant la lògica que ja has construït.

Això no obstant, quan s'implementa un model tabular en mode DirectQuery, poden aparèixer limitacions: algunes funcions DAX no estan suportades directament sobre determinades bases de dades relacionals o poden tornar resultats lleugerament diferents a causa de com es deleguen les consultes.

En aquests escenaris, és important revisar la documentació específica del motor tabular i validar les mesures crítiques per confirmar que continuen funcionant com s'espera després d'activar DirectQuery.

Escenaris pràctics: càlculs complexos amb CALCULATE i filtres

Un dels punts forts de DAX és la capacitat dexecutar càlculs complexos que depenen d'agregacions personalitzades i filtres dinàmics. Les funcions CALCULATE i CALCULATETABLE són l‟eix central d‟aquest tipus d‟escenaris.

CALCULAT permet redefinir el context de filtre sobre el qual s'avalua una expressió. Per exemple, pots demanar “la suma de vendes filtrada a un determinat any, encara que a la taula dinàmica s'estiguin mostrant altres anys”, o “el total sense aplicar certs filtres de producte”.

En qualsevol lloc on una funció DAX accepti una taula com a argument, podeu passar una versió filtrada d'aquesta taula, bé usant FILTER, bé especificant condicions dins de CALCULATE. Això fa possible construir mides que s'adaptin a milers de combinacions de condicions sense necessitat de crear columnes intermèdies.

També és possible eliminar de forma selectiva filtres existents usant funcions com ALL o ALLEXCEPT. Per exemple, per calcular la contribució d'un revenedor concret respecte al total de revenedors, podeu fer que una mesura divideixi el valor en el context actual pel valor en el context “ALL” (sense filtres per revenedor).

En altres casos, necessitareu utilitzar valors d'un bucle extern, és a dir, fer referència a la fila o context d'iteració anterior. Aquí entren funcions com EARLIER, que permeten fins a dos nivells de bucles imbricats i són molt útils per crear rànquings, acumulats per grup o càlculs que depenen d'un context de fila previ.

Treball amb text, dates i claus a DAX

DAX també ofereix moltes eines per manipular text i dates. Això és crític quan els orígens de dades porten dates en formats rars, claus compostes o camps de text que cal convertir a valors de temps.

A Power Pivot no s'admeten directament claus compostes en les relacions. Si la teva font fa servir diverses columnes com a clau, en molts casos hauràs de crear una columna calculada que concatene aquestes parts en una sola clau i fer-la servir com a camp de relació.

Quan les dates vénen en formats no reconeguts pel motor (per exemple, una data en format regional estrany o un enter tipus 01032009 importat com a text), podeu construir fórmules de l'estil:

=DATA(RIGHT(,4), LEFT(,2), MID(,3,2))

Amb aquest tipus d'expressió, recomposes una data vàlida de SQL Server a partir de fragments extrets de la cadena, el que et permet després utilitzar funcions d'intel·ligència de temps sense problemes.

també pots canviar tipus de dades mitjançant fórmules: multiplicar per 1,0 per convertir dates o cadenes numèriques en números, o concatenar amb una cadena buida per transformar un número o data en text. A més, hi ha funcions específiques per controlar el tipus tornat (truncar decimals, forçar sencers, etc.).

Valors condicionals i control d'errades en columnes i mides

Igual que a Excel, DAX inclou funcions per tornar resultats en funció de condicions i per controlar errors de forma elegant. Per exemple, pots etiquetar revenedors com “Preferits” o “Valor” segons el volum anual de vendes mitjançant expressions IF imbricades.

En una columna calculada, però, no et pots permetre que algunes files tinguin errors i d'altres no: si una fila produeix un error, la columna sencera queda marcada com a errònia. Això exigeix ​​ser més estricte amb el control derrors que en un full de càlcul convencional.

Per evitar que una simple divisió per zero o un valor en blanc tiri a baix tota la columna, és recomanable embolicar les operacions sensibles en comprovacions prèvies utilitzant IF i funcions d'informació, tornant sempre un valor vàlid encara que la combinació de dades sigui estranya.

Quan estiguis construint el model, pot ser útil deixar que apareguin els errors al principi per localitzar-los i corregir-los. Però un cop el publiquis per a altres usuaris, convé assegurar que les fórmules estan blindades i que mai no aflora un missatge d'error a les taules dinàmiques o visualitzacions.

Intel·ligència de temps: acumulats, comparacions i períodes personalitzats

Les funcions d'intel·ligència de temps són un dels grans atractius de DAX. Permeten treballar amb intervals de dates, calcular acumulats, comparar períodes i generar finestres de temps personalitzades amb relativa facilitat, sempre que tinguis una taula de calendari ben configurada.

Es poden crear mesures de vendes acumulades per dia, mes, trimestre o any, calcular saldos d'obertura i tancament per a cada període, o comparar les vendes d'un any amb les de l'any anterior, el trimestre contra el trimestre, etc., utilitzant funcions específiques de temps.

A més, pots recuperar conjunts personalitzats de dates, com "els primers 15 dies després del començament d'una promoció" o "el mateix període de l'any anterior", i després passar aquest conjunt a una funció que afegiu les dades sobre aquesta finestra de dates concreta.

Funcions com PARALLELPERIOD i altres relacionades amb períodes paral·lels faciliten la comparació entre intervals desplaçats en el temps, per exemple, per analitzar si una campanya ha millorat resultats respecte del mateix tram temporal d'un altre any.

Classificació i comparació de valors: top N i rànquings dinàmics

Quan necessites mostrar només els elements més rellevants (per exemple, els 10 productes més venuts), tens dues vies principals: fer servir les funcions de filtratge d'Excel sobre la taula dinàmica o construir una classificació dinàmica amb DAX.

Excel ofereix filtres de tipus “Els 10 millors” a les taules dinàmiques, molt fàcils de configurar per mostrar només els elements superiors o inferiors segons un camp numèric. Podeu filtrar per nombre d'elements, per percentatge acumulat o per suma de valors.

El problema daquest enfocament és que el filtre és purament de presentació: si canvien les dades subjacents, necessites actualitzar manualment la taula dinàmica perquè el filtre es reflecteixi correctament. I, a més, no pots reutilitzar aquesta classificació com a part daltres fórmules DAX.

L'alternativa és crear-ne una columna calculada o mesura que assigni un rànquing a cada element utilitzant DAX. Aquesta opció és més costosa computacionalment, però té avantatges: la classificació es recalcula de forma dinàmica i es pot fer servir en segmentacions de dades, permetent que l'usuari seleccioni si vol veure el top 5, top 10, top 50, etc.

Això sí, en models amb milions de files, els rànquings dinàmics poden ser pesats i cal avaluar si compensa el cost en rendiment davant del benefici funcional que aporten.

Bones pràctiques en dissenyar models DAX a Power Pivot

Perquè un model DAX a Power Pivot sigui mantenible i rendeixi bé, no n'hi ha prou que les fórmules “funcionin”. Convé seguir una sèrie de bones pràctiques que marquen molt la diferència en projectes reals.

Una recomanació recurrent és prioritzar mesures davant de columnes calculades quan el càlcul sigui realment una agregació dinàmica i no un atribut fix. Les columnes calculades ocupen memòria i es recalculen de cop, mentre que les mesures s'avaluen només quan cal.

També és molt útil utilitzar variables a DAX (VAR) per simplificar fórmules complexes, evitar repetir el mateix càlcul diverses vegades i millorar la llegibilitat. Això ajuda tant al rendiment com a la comprensió del model quan una altra persona ho revisa.

Per últim, uns noms clars i una mínima documentació interna marquen la diferència. Posa noms descriptius a mides i columnes, evitant abreviatures fosques, i documenta les fórmules més importants. Això redueix la corba daprenentatge per a nous usuaris i testalvia mals de cap quan tornis al model mesos després.

Dominar DAX dins de Power Pivot no és qüestió de memoritzar totes les funcions, sinó d'entendre com interactuen les fórmules amb el model relacional, el context de filtres i l'actualització de dades. Amb una base sòlida en columnes calculades, mesures, funcions de temps, maneig derrors i bones pràctiques de disseny, els teus models tabulars esdevenen més flexibles, molt més fàcils d'analitzar i, sobretot, capaços de respondre preguntes de negoci complexes només arrossegant uns camps a una taula dinàmica.