Šiame skyriuje pateikiami saitai su pavyzdžiais, parodančiais, kaip naudoti DAX formules toliau nurodytuose scenarijuose.
- Sudėtingų skaičiavimų atlikimas
- Darbas su tekstu ir datomis
- Sąlyginės reikšmės ir tikrinimas, ar yra klaidų
- Laiko informacijos naudojimas
- Reikšmių reitingavimas ir palyginimas
Šiame straipsnyje:
Darbo pradžia
Apsilankykite DAX išteklių centro "wiki" puslapyje , kuriame galite rasti įvairios informacijos apie DAX, įskaitant tinklaraščius, pavyzdžius, technines knygas ir vaizdo įrašus, kuriuos pateikė žinomi specialistai ir "„Microsoft“".
Scenarijai: sudėtingų skaičiavimų atlikimas
DAX formulės gali atlikti sudėtingus skaičiavimus, kurie apima pasirinktinius agregavimus, filtravimą ir sąlyginių reikšmių naudojimą. Šiame skyriuje pateikiami pavyzdžiai, kaip pradėti naudoti pasirinktinius skaičiavimus.
"PivotTable" pasirinktinių skaičiavimų kūrimas
CALCULATE ir CALCULATETABLE yra efektyvios, lanksčios funkcijos, naudingos apskaičiuotiesiems laukams apibrėžti. Šios funkcijos leidžia keisti kontekstą, kuriame bus skaičiuojama. Taip pat galite tinkinti atliekamo agregavimo ar matematinės operacijos tipą. Pavyzdžių žr. šiose temose.
Filtro taikymas formulei
Daugelyje vietų, kur DAX funkcija naudoja lentelę kaip argumentą, paprastai galima pereiti į filtruotą lentelę arba vietoj lentelės pavadinimo naudojant funkciją FILTER, arba kaip vieną iš funkcijos argumentų nurodant filtro išraišką. Toliau pateiktose temose pateikiami pavyzdžiai, kaip kurti filtrus ir kaip filtrai veikia formulių rezultatus. Daugiau informacijos rasite Duomenų filtravimas DAX formulėse.
Funkcija FILTER leidžia nurodyti filtro kriterijus naudojant reiškinį, o kitos funkcijos yra specialiai sukurtos filtruoti tuščias reikšmes.
Pasirinktinai pašalinkite filtrus, kad sukurtumėte dinaminį santykį
Formulėse sukūrę dinaminius filtrus, galite lengvai atsakyti į tokius klausimus:
- Koks dabartinio produkto pardavimo įtaka bendram metų pardavimui?
- Kiek šis padalinys prisidėjo prie bendro pelno per visus veiklos metus, palyginti su kitais padaliniais?
"PivotTable" naudojamoms formulėms gali turėti įtakos "PivotTable" kontekstas, bet galite pasirinktinai pakeisti kontekstą įtraukdami arba pašalindami filtrus. Pavyzdys temoje VISI rodo, kaip tai padaryti. Norėdami sužinoti konkretaus perpardavėjo pardavimų santykį su visų perpardavėjų pardavimais, galite sukurti matą, kuris apskaičiuoja dabartinio konteksto reikšmę, padalytą iš konteksto ALL reikšmės.
Temoje ALLEXCEPT pateikiamas pavyzdys, kaip pasirinktinai išvalyti formulės filtrus. Abiejuose pavyzdžiuose paaiškinta, kaip keičiasi rezultatai atsižvelgiant į "PivotTable" dizainą.
Jei reikia kitų santykių ir procentų skaičiavimo pavyzdžių, žr. šias temas:
Reikšmės iš išorinio ciklo naudojimas
Be to, kad skaičiavimuose DAX gali naudoti reikšmes iš dabartinio konteksto, DAX gali naudoti reikšmę iš ankstesnio ciklo kuriant susijusių skaičiavimų rinkinį. Šioje temoje pateikiami patarimai, kaip sukurti formulę, kuri nurodo reikšmę iš išorinio ciklo. Funkcija EARLIER palaiko ne daugiau kaip dviejų lygių įdėtuosius ciklus.
Norėdami sužinoti daugiau apie eilutės kontekstą ir susijusias lenteles bei kaip naudoti šią sąvoką formulėse, žr. DAX formulių kontekstas.
Scenarijai: darbas su tekstu ir datomis
Šiame skyriuje pateikiami saitai į DAX nuorodų temas, kuriose pateikiami įprastinių scenarijų, pvz., darbo su tekstu, datos ir laiko reikšmių išskleidimo ir kūrimo arba reikšmių kūrimo pagal tam tikrą sąlygą pavyzdžiai.
Pagrindinio stulpelio kūrimas susiejant
"PowerPivot" neleidžia sudėtinių raktų; Dėl to, jei duomenų šaltinyje yra sudėtinių raktų, gali tekti juos sujungti į vieną rakto stulpelį. Šioje temoje pateikiamas vienas pavyzdys, kaip sukurti apskaičiuojamąjį stulpelį, pagrįstą sudėtiniu raktu.
Compose a date based on date parts extracted from a text date
"PowerPivot" naudoja "„SQL Server“" datos/laiko duomenų tipą veiksmams su datomis, todėl jei išoriniuose duomenyse yra datų, kurios yra suformatuotos skirtingai, pavyzdžiui, jei datos parašytos regioniniu datos formatu, kurio neatpažįsta "Power Pivot" duomenų modulis, arba jei jūsų duomenys naudoja sveikųjų skaičių pakaitinius raktus, gali tekti naudoti DAX formulę, kad gautumėte datos dalis ir tada sudėliotumėte dalis į galiojančią datą. laiko atvaizdavimas.
Pavyzdžiui, jei turite stulpelį datų, kurios buvo pateiktos kaip sveikasis skaičius, o tada importuotos kaip teksto eilutė, galite konvertuoti eilutę į datos / laiko reikšmę naudodami šią formulę:
=DATE(RIGHT([Reikšmė1],4),LEFT([Reikšmė1],2),MID([Reikšmė1],2))
| Reikšmė1 | Rezultatas |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Toliau nurodytose temose pateikiama daugiau informacijos apie funkcijas, naudojamas datoms išgauti ir sudaryti.
Pasirinktinio datos ar skaičių formato nustatymas
Jei jūsų duomenyse yra datų ar skaičių, kurie nėra pateikti vienu iš standartinių Windows teksto formatų, galite apibrėžti pasirinktinį formatą, kad užtikrintumėte, jog reikšmės bus tvarkomos tinkamai. Šie formatai naudojami konvertuojant reikšmes į eilutes arba iš eilučių. Toliau nurodytose temose taip pat pateikiamas išsamus iš anksto nustatytų formatų, galimų dirbti su datomis ir skaičiais, sąrašas.
- Iš anksto apibrėžtieji funkcijos FORMAT skaitiniai formatai
- Pasirinktiniai skaitiniai formatai, skirti funkcijai FORMAT
- Iš anksto apibrėžtieji funkcijos FORMAT formatai Data ir Laikas
- Funkcijos FORMAT pasirinktiniai datos ir laiko formatai
Duomenų tipų keitimas naudojant formulę
Naudojant "Power Pivot", išvesties duomenų tipą lemia šaltinio stulpeliai, todėl negalima aiškiai nurodyti rezultato duomenų tipo, nes optimalų duomenų tipą nustato "Power Pivot". Tačiau galite naudoti numanomus duomenų tipo konvertavimus, kuriuos atlieka "Power Pivot", kad galėtumėte valdyti išvesties duomenų tipą.
- Norėdami konvertuoti datą arba skaičių eilutę į skaičių, padauginkite iš 1,0. Pavyzdžiui, ši formulė apskaičiuoja esamą datą atėmus 3 dienas ir tada pateikia atitinkamą sveikojo skaičiaus reikšmę.
=(TODAY()-3)*1.0 - Norėdami konvertuoti datos, skaičių arba valiutos reikšmę į eilutę, sujunkite reikšmę naudodami tuščią eilutę. Pavyzdžiui, toliau pateikta formulė grąžina šiandienos datą kaip eilutę.
=""& TODAY()
Toliau nurodytos funkcijos taip pat gali būti naudojamos siekiant užtikrinti, kad būtų grąžintas konkretus duomenų tipas:
Realiųjų skaičių konvertavimas į sveikuosius skaičius
- Funkcija ROUND
- Funkcija CEILING
-
Funkcija FLOOR
Realiųjų skaičių, sveikųjų skaičių arba datų konvertavimas į eilutes - Funkcija FIXED
-
Funkcija FORMAT
Eilučių konvertavimas į realiuosius skaičius arba datas - Funkcija VALUE
- Funkcija DATEVALUE
- Funkcija TIMEVALUE
Scenarijus: Sąlyginės reikšmės ir tikrinimas, ar yra klaidų
Kaip ir "Excel", DAX turi funkcijų, kurios leidžia patikrinti duomenų reikšmes ir grąžinti skirtingas reikšmes pagal sąlygą. Pavyzdžiui, galite sukurti apskaičiuojamąjį stulpelį, kuriame perpardavėjai bus pažymėti kaip Pageidaujami arba Vertė , priklausomai nuo metinio pardavimo kiekio. Reikšmes tikrinančios funkcijos taip pat naudingos tikrinant reikšmių diapazoną arba tipą, kad netikėtos duomenų klaidos nesugadintų skaičiavimų.
Reikšmės kūrimas pagal sąlygą
Norėdami išbandyti reikšmes ir sąlygiškai generuoti naujas reikšmes, galite naudoti įdėtąsias IF sąlygas. Toliau pateiktose temose pateikiami keli paprasti sąlyginio apdorojimo ir sąlyginių reikšmių pavyzdžiai:
Tikrinimas, ar formulėje yra klaidų
Skirtingai nei "Excel", vienoje apskaičiuoto stulpelio eilutėje negali būti galiojančių reikšmių, o kitoje – neleistinų reikšmių. Tai yra, jei yra klaida bet kurioje "PowerPivot" stulpelio dalyje, visas stulpelis pažymimas su klaida, todėl visada turite ištaisyti formulės klaidas, kurių reikšmės neleistinos.
Pavyzdžiui, jei sukuriate formulę, kuri dalija iš nulio, galite gauti begalybės rezultatą arba klaidą. Kai kurios formulės taip pat neveiks, jei funkcija aptiks tuščią reikšmę, kai tikisi skaitinės reikšmės. Kuriant duomenų modelį geriausia leisti klaidoms atsirasti, kad galėtumėte spustelėti pranešimą ir išspręsti problemą. Tačiau publikuodami darbaknyges turėtumėte įtraukti klaidų tvarkymą, kad netikėtos reikšmės nesukeltų nepavykusių skaičiavimų.
Norėdami išvengti klaidų skaičiavimo stulpelyje, naudokite loginių ir informacinių funkcijų derinį, kad patikrintumėte, ar yra klaidų, ir visada grąžintumėte galiojančias reikšmes. Toliau nurodytose temose pateikiami keli paprasti pavyzdžiai, kaip tai padaryti DAX:
Scenarijai: laiko informacijos naudojimas
DAX laiko informacijos funkcijos apima funkcijas, padedančias iš duomenų nustatyti datas arba datų intervalus. Tada galėsite naudoti tas datas arba datų diapazonus, kad apskaičiuotumėte panašių laikotarpių reikšmes. Laiko informacijos funkcijos taip pat apima funkcijas, kurios veikia su standartiniais datos intervalais, kad būtų galima palyginti mėnesių, metų ar ketvirčių reikšmes. Taip pat galite sukurti formulę, kuri lygintų nurodyto laikotarpio pirmos ir paskutinės datos reikšmes.
Visų laiko informacijos funkcijų sąrašą žr. Laiko informacijos funkcijos (DAX). Patarimų, kaip efektyviai naudoti datas ir laikus atliekant "Power Pivot" analizę, rasite "Power Pivot" datos.
Apskaičiuokite kaupiamąjį pardavimą
Toliau pateiktose temose pateikiami pavyzdžiai, kaip apskaičiuoti pabaigos ir pradžios balansus. Pavyzdžiai leidžia sukurti priskaičiuojamus balansus skirtingais intervalais, pvz., dienomis, mėnesiais, ketvirčiais ar metais.
- Funkcija CLOSINGBALANCEMONTH, funkcija CLOSINGBALANCEQUARTER, funkcija CLOSINGBALANCEYEAR
- Funkcija OPENINGBALANCEMONTH, funkcija OPENINGBALANCEQUARTER, funkcija OPENINGBALANCEYEAR
Reikšmių palyginimas per tam tikrą laiką
Toliau pateiktose temose pateikiami skirtingų laikotarpių sumų palyginimo pavyzdžiai. DAX palaikomi numatytieji laikotarpiai yra mėnesiai, ketvirčiai ir metai.
- Funkcija PREVIOUSMONTH, Funkcija PREVIOUSQUARTER,Funkcija PREVIOUSYEAR
- Funkcija TOTALMTD, funkcija TOTALQTD, funkcija TOTALYTD
- Funkcija PARALLELPERIOD
Pasirinktinio datos diapazono reikšmės apskaičiavimas
Žr. pateiktas temas, kuriose rasite pavyzdžių, kaip gauti pasirinktines datų sekas, pvz., pirmąsias 15 dienų nuo pardavimo akcijos pradžios.
Jei naudojate laiko informacijos funkcijas pasirinktiniam datų rinkiniui gauti, galite naudoti tą datų rinkinį kaip įvestį į funkciją, kuri atlieka skaičiavimus, kad sukurtumėte pasirinktines laikotarpio agreguotas reikšmes. Žr. šioje temoje pateiktą pavyzdį, kaip tai padaryti:
-
Pastaba
Jei nereikia nurodyti pasirinktinio datos diapazono, bet dirbate su standartiniais apskaitos vienetais, pvz., mėnesiais, ketvirčiais ar metais, rekomenduojame atlikti skaičiavimus naudojant tam skirtas laiko informacijos funkcijas, pvz., TOTALQTD, TOTALMTD, TOTALQTD ir t. t.
Scenarijai: reikšmių reitingavimas ir palyginimas
Norėdami rodyti tik viršutinį n stulpelio arba "PivotTable" elementų skaičių, turite kelias parinktis:
- Galite naudoti "Excel" funkcijas ir sukurti viršutinį filtrą. Taip pat "PivotTable" galite pasirinkti keletą populiariausių arba mažiausių reikšmių. Pirmoje šio skyriaus dalyje aprašoma, kaip filtruoti 10 pagrindinių "PivotTable" elementų. Daugiau informacijos ieškokite "Excel" dokumentacijoje.
- Galite sukurti formulę, kuri dinamiškai reitinguoja reikšmes, tada filtruoti pagal rango reikšmes arba naudoti rango reikšmę kaip sluoksniavimo priemonę. Antroje šio skyriaus dalyje aprašoma, kaip sukurti šią formulę ir tada naudoti tą rangą duomenų filtre.
Kiekvienas metodas turi privalumų ir trūkumų.
- "Excel" viršutinį filtrą paprasta naudoti, bet jis skirtas tik rodymui. Pasikeitus "PivotTable" duomenims, turite atnaujinti "PivotTable" neautomatiškai, kad matytumėte pakeitimus. Jei jums reikia dinamiškai dirbti su reitingais, galite naudoti DAX ir sukurti formulę, lyginančią reikšmes su kitomis stulpelio reikšmėmis.
- DAX formulė yra galingesnė; be to, įtraukę rango reikšmę į duomenų filtrą, galite tiesiog spustelėti sluoksniavimo priemonę ir pakeisti rodomų didžiausių reikšmių skaičių. Tačiau skaičiavimai yra brangūs, todėl šis metodas gali būti netinkamas lentelėms su daug eilučių.
"PivotTable" tik dešimties svarbiausių elementų rodymas
Didžiausių arba mažiausių reikšmių rodymas "PivotTable"
|
|---|
Dinaminis elementų rikiavimas naudojant formulę
Šioje temoje pateikiamas pavyzdys, kaip naudoti DAX norint sukurti reitingavimą, saugomą apskaičiuotame stulpelyje. Kadangi DAX formulės skaičiuojamos dinamiškai, visada galite būti tikri, kad reitingas yra teisingas, net jei esami duomenys pasikeitė. Be to, kadangi formulė naudojama apskaičiuotame stulpelyje, galite naudoti rangą duomenų filtre ir tada pasirinkti 5 didžiausias, 10 didžiausių ar net 100 didžiausių reikšmių.