Power Pivot (5) – Exemple de functii DAX. Functia RELATED
Incepand din 11 iulie 2016, vom publica in fiecare zi de luni si de joi cate un articol din seria de zece cursuri gratuite dedicate Power Pivot pentru Excel 2013.
ENJOY THE POWER! 🙂
Se da un model de date format din mai multe tabele relationate astfel:
Exemplul 1
In tabela facturi, vrem sa introducem o noua coloana, denumita Status factura, in care sa scrie platit, respectiv neplatit. Informatiile referitoare la toate facturile neplatite se afla in tabela FACT_RESTANTE.
Vom utiliza o functie de tip DAX, pentru a introduce o coloana noua in PowerPivot.
Functia RELATED este o functie speciala de tip Filter, cu urmatoarea sintaxa:
=RELATED(),
unde column este coloana din tabela relationata din care vrem sa “aducem” in tabela facturi informatia.
In modulul de vizualizare Power Pivot, in tabela facturi, pozitionati cursorul pe primul rand gol din ultima coloana, acolo unde scrie Add a Column.
Inscrieti formula:
=RELATED(FACT_RESTANTE[InvoiceID])
Gata! Observati ca, acolo unde este gasit ID-ul de factura in tabela FACT_RESTANTE, se inscrie pur si simplu InvoiceID, in rest apar celule goale.
Coloana calculata va fi denumita automat CalculatedColumn1 (in exemplul nostru denumirea este CalculatedColumn3 pentru ca au mai fost inserate anterior alte coloane calculate). Pentru a o redenumi, pozitionati cursorul pe eticheta de coloana, dati click dreapta si alegeti Rename Column.
Observati de asemenea ca toate coloanele calculate au un design usor diferit (culoare fond celula) si ca ele nu exista in tabelele originale, din care a fost construit modelul de date. Aceste coloane vor aparea atunci cand vreti sa realizati un Pivot Table de tip Power Pivot.
Numarul de factura (InvoiceID) este chiar un numar, asa ca putem utiliza rezultatul functiei RELATED ca argument al functiei IF, astfel ca formula devine:
=IF(RELATED(FACT_RESTANTE[InvoiceID])>0,”neplatit”,”platit”)
Exemplul 2
In tabela contracte, vrem sa calculam si valoarea contractelor in EUR, tabelul initial continand doar informatii referitoare la: anul contractului, moneda contractului si valoarea contractului (in moneda contractului).
Tabela contracte si tabela curs_bnr_anual sunt relationate prin coloana YearID.
In modulul de vizualizare Power Pivot (Remember! Cum se relizeaza si cum se acceseaza un model de date), accesati tabela denumita contracte.
Pentru a calcula valoarea (consolidata) in euro a fiecarui contract, trebuie sa stim: care este moneda contractului si care este cursul mediu anual aferent anului contractului.
Introducem o noua coloana calculata (in coloana Add a column din tabela contracte).
Pentru inceput scriem pur si simplu:
= RELATED(curs_bnr_anual[Exchange rate[Year]])
Cu alte cuvinte, avand drept cheie de relationare coloana YearID, „aducem” cursul mediu anual de schimb din tabela curs_bnr_anual.
Pentru a obtine valoarea consolidata in EUR (exemplul dat contine doar doua tipuri de monede ale contractului, LEI, respectiv EUR), utilizam rezultatul functiei RELATED ca argument in functia IF.
=IF([Contract currency]=”EUR”,[Contract value [contract currency]]],[Contract value [contract currency]]]/RELATED(curs_bnr_anual[Exchange rate[Year]]]))
Cu alte cuvinte, daca moneda contractului, adica [Contract currency] este “EUR”, valoarea consolidata va fi aceeasi cu valoarea contractului in moneda contractului, adica valoarea de pe acelasi rand, din coloana [Contract value [contract currency]], altfel va fi valoarea contractului in moneda contractului impartita la cursul mediu anual aferent anului contractului (adica chiar rezultatul functiei Related).
=IF([Contract currency]=”EUR”,[Contract value [contract currency]]],[Contract value [contract currency]]]/RELATED(curs_bnr_anual[Exchange rate[Year]]]))
Gata!
Pentru a formata valorile, este suficient sa pozitionati cursorul oriunde in noua coloana calculata si sa alegeti o varianta de afisare din meniul Home al Power Pivot. Puteti da si o denumire noii coloane, asa cum am explicat in Exemplul 1.
Observatii:
- Functia RELATED poate fi utilizata ca instrument de analiza de tip Power BI, doar cu conditia ca intre tabelul curent si tabelul din care extrage informatiile functia RELATED sa fi fost anterior definita cel putin o relatie de tip one-to-many.
- Functia RELATED efectueaza in fapt o cautare (la fel ca functiile clasice de tip LOOKUP), dupa coloana de legatura.
- In exemplul de mai sus, cauta anul contractului (YearID) de pe randul curent in tabela curs_bnr_anual si intoarce rezultatul din coloana Exchange_rate[Year].
