DAX (Data Analysis Expressions) -kieli Power Pivotissa

Tietoja DAX-kaavoista

DAX-kaavat muistuttavat hyvin paljon Excel-kaavoja. Kaava luodaan kirjoittamalla ensin yhtäläisyysmerkki, sitten funktion nimi tai lauseke ja lopuksi vaaditut arvot tai argumentit. Excelin tavoin myös DAX tukee useita erilaisia funktioita, joita voidaan käyttää merkkijonoja käsiteltäessä, päivämääriin ja kellonaikoihin perustuvia laskutoimituksia suoritettaessa tai ehdollisia arvoja luotaessa.

DAX-kaavat ovat kuitenkin erilaisia seuraavissa tärkeissä tapauksissa:

Jos haluat mukauttaa laskutoimituksia rivikohtaisesti, voit tiettyjen DAX-funktioiden avulla suorittaa kontekstin mukaan vaihtelevia laskutoimituksia käyttämällä nykyistä rivin arvoa tai liittyvää arvoa.
DAX sisältää funktioita, jotka palauttavat tuloksena yksittäisen arvon sijasta taulukon. Näitä funktioita voi käyttää syötteiden luomiseen muita funktioita varten.
Aikatietofunktiot DAX sallii laskutoimitukset käyttämällä päivämääräalueita ja vertaa tuloksia rinnakkaisilla kausilla.

DAX-kaavojen käyttötavat

Voit luoda Power Pivotissa kaavoja joko laskettuihin sarakkeisiin tai laskettuihin kenttiin.

Lasketut sarakkeet

Laskettu sarake on sarake, joka lisätään aiemmin luotuun Power Pivot -taulukkoon. Arvoja ei liitetä eikä tuoda sarakkeeseen, vaan sen sijaan luodaan sarakkeen arvot määrittävä DAX-kaava. Jos sisällytät Power Pivot -taulukon pivot-taulukkoon (tai pivot-kaavioon), voit käyttää laskettua saraketta minkä tahansa muun tietosarakkeen tavoin.

Lasketuissa sarakkeissa olevat kaavat muistuttavat paljon Excelissä luotavia kaavoja. Toisin kuin Excelissä, et kuitenkaan voi luoda eri kaavaa taulukon eri riveille, vaan DAX-kaava pätee automaattisesti koko sarakkeeseen.

Kun sarake sisältää kaavan, kullekin riville lasketaan arvo. Sarakkeen tulokset lasketaan, kun olet luonut kaavan. Sarakearvot lasketaan uudelleen vain, jos pohjana olevat tiedot päivitetään tai arvot lasketaan uudelleen manuaalisesti.

Voit luoda laskettuja sarakkeita, jotka perustuvat mittoihin ja muihin laskettuihin sarakkeisiin. Vältä kuitenkin käyttämästä samaa nimeä lasketulle sarakkeelle ja mitalle, sillä se voi johtaa harhaanjohtaviin tuloksiin. Viitattaessa sarakkeeseen on hyvä käyttää täydellistä sarakeviittausta, jottei mittaa käytetä vahingossa.

Lisätietoja on artikkelissa Lasketut sarakkeet Power Pivotissa.

Mitat

Mittayksikkö on kaava, joka luodaan erityisesti käytettäväksi pivot-taulukossa (tai Pivot-kaaviossa), joka käyttää Power Pivot tietoja. Mittayksiköiden perustana voi olla vakiokoostefunktiot, kuten LASKE tai SUMMA, tai voit määrittää oman kaavan DAX-funktiolla. Mittayksikköä käytetään Pivot-taulukon Arvot-alueella . Jos haluat sijoittaa lasketut tulokset pivot-taulukon eri alueelle, käytä sen sijaan laskettua saraketta.

Kun määrität eksplisiittisen mitan kaavan, mitään ei tapahdu, ennen kuin lisäät mitan pivot-taulukkoon. Kun lisäät mitan, kunkin solun kaava lasketaan pivot-taulukon Arvot-alueella. Koska kullekin rivi- ja sarakeotsikoiden yhdistelmälle luodaan tulos, mitan tulos voi olla erilainen kussakin solussa.

Luomasi mitan määritys tallentuu yhdessä lähdetietotaulukon kanssa. Määritys näkyy pivot-taulukon kenttäluettelossa, ja kaikki työkirjan käyttäjät voivat käyttää sitä.

Lisätietoja on artikkelissa Mitat Power Pivotissa.

Kaavojen luominen kaavarivin avulla

Excelin tavoin Power Pivot sisältää kaavarivin, joka helpottaa kaavojen luomista ja muokkaamista, sekä automaattisen täydentämisominaisuuden, joka vähentää kirjoitus- ja syntaksivirheitä.

Taulukon nimen lisääminen Ala kirjoittaa taulukon nimeä. Automaattinen täydentämistoiminto näyttää avattavan luettelon, joka sisältää antamillasi kirjaimilla alkavat sallitut nimet.

Sarakkeen nimen lisääminen Kirjoita hakasulje ja valitse sitten sarake nykyisen taulukon sarakkeiden luettelosta. Jos sarake on toisessa taulukossa, kirjoita taulukon nimen ensimmäiset kirjaimet ja valitse sitten sarake automaattisen täydennystoiminnon näyttämästä avattavasta luettelosta.

Lisätietoja kaavan kirjoittamisen vaiheista on artikkelissa Kaavojen luominen laskutoimituksia varten Power Pivotissa.

Vihjeitä automaattisen täydentämistoiminnon käyttämisestä

Voit käyttää kaavan automaattista täydentämistä sellaisen kaavan keskellä, jossa on sisäkkäisiä funktioita. Avattavassa luettelossa näytettävät arvot määräytyvät lisäyskohtaa edeltävän tekstin perusteella, ja lisäyskohdan jälkeinen teksti säilyy muuttumattomana.

Vakioille antamasi määritetyt nimet eivät näy automaattisen täydentämisen avattavassa luettelossa, mutta voit silti kirjoittaa niitä.

Power Pivot ei lisää funktioiden sulkemissulkeita tai vastaa automaattisesti sulkeita. Varmista, että jokainen funktio on syntaktisesti oikea tai että et voi tallentaa tai käyttää kaavaa.

Usean funktion käyttäminen kaavassa

Voit määrittää sisäkkäisiä funktioita, mikä tarkoittaa sitä, että voit käyttää toisen funktion tulosta toisen funktion argumenttina. Voit määrittää laskettuihin sarakkeisiin jopa 64 funktiotasoa. Huomaa kuitenkin, että sisäkkäiset funktiot tekevät kaavojen luonnista ja vianmäärityksestä vaikeaa.

Useat DAX-funktiot on suunniteltu toimimaan ainoastaan sisäkkäisinä funktioina. Tällaiset funktiot palauttavat taulukon, jota on käytettävä taulukkofunktion syötteenä, joten taulukkoa ei voi suoraan tallentaa tulokseksi. Esimerkiksi funktiot SUMX, AVERAGEX ja MINX edellyttävät taulukkoa ensimmäiseksi argumentikseen.

Huomautus: Funktioiden sisäkkäisyyttä mitoissa koskevat tietyt rajoitukset, jotta sarakkeiden välisten riippuvuuksien vaatimat useat laskutoimitukset eivät vaikuta suorituskykyyn.

DAX-funktioiden ja Excel-funktioiden vertailua

DAX-funktiokirjasto perustuu Excelin funktiokirjastoon, mutta kirjastojen välillä on paljon eroja. Tässä osassa on yhteenveto Excel- ja DAX-funktioiden eroista ja yhtenevyyksistä.

Monilla DAX-funktioilla on sama nimi ja yleinen toiminnallisuus kuin Excel-funktioilla, mutta niitä on muokattu vastaanottamaan erilaisia syötteitä ja ne saattavat joissakin tapauksissa palauttaa eri tietotyypin. DAX-funktioita ei yleensä voi käyttää Excel-kaavassa eikä Excel-kaavoja Power Pivotissa ilman muokkaamista.
DAX-funktiot eivät koskaan voi käyttää viitteenä solua tai solualuetta, vaan ne käyttävät viitteenä saraketta tai taulukkoa.
DAXin päivämäärä- ja kellonaikafunktiot palauttavat datetime-tietotyypin. Excelin päivämäärä- ja kellonaikafunktiot taas palauttavat kokonaisluvun, joka esittää päivämäärän sarjanumerona.
Monet uusista DAX-funktioista joko palauttavat arvotaulukon tai tekevät laskutoimituksia syötteenä annetun arvotaulukon perusteella. Excelissä ei sitä vastoin ole taulukoita palauttavia funktioita, mutta jotkin funktiot voivat käsitellä matriiseja. Mahdollisuus viitata kokonaisiin taulukoihin ja sarakkeisiin helposti on uusi ominaisuus Power Pivotissa.
DAXissa on uusia hakufunktioita, jotka muistuttavat Excelin matriisi- ja vektorihakufunktioita. DAX-funktiot kuitenkin edellyttävät, että taulukoiden välille luodaan suhde.
Sarakkeen tietojen tietotyypin oletetaan olevan aina sama. Jos tietojen tietotyyppi ei ole sama, DAX vaihtaa koko sarakkeeseen tietotyypin, joka sopii parhaiten kaikille arvoille.

DAX-tietotyypit

Power Pivot -tietomalliin voidaan tuoda tietoja monista eri tietolähteistä, jotka saattavat tukea eri tietotyyppejä. Kun tuot tai lataat tietoja ja käytät tietoja laskutoimituksissa tai pivot-taulukoissa, tiedot muunnetaan joksikin Power Pivot -tietotyypiksi. Luettelo tietotyypeistä on artikkelissa Tietotyypit tietomalleissa.

Taulukko-tietotyyppi on DAX-kielen uusi tietotyyppi, jota käytetään monien uusien funktioiden syötteenä tai tuloksena. FILTER-funktio esimerkiksi ottaa syötteenä taulukon ja palauttaa toisen taulukon, jossa on vain suodatusehtoja vastaavat rivit. Taulukkofunktioita ja koostefunktioita yhdistämällä voit tehdä monimutkaisia laskutoimituksia dynaamisesti määritetyistä tietojoukoista. Lisätietoja on artikkelissa Koosteet Power Pivotissa.

Kaavat ja relaatiomalli

Power Pivot-ikkuna on alue, jolla voidaan käsitellä useita tietotaulukoita ja yhdistää taulukot relaatiomallissa. Tässä tietomallissa taulukot yhdistetään toisiinsa suhteilla, joilla voi luoda vastaavuuksia muiden taulukoiden sarakkeisiin ja tehdä kiinnostavia laskutoimituksia. Voit esimerkiksi luoda kaavoja, jotka laskevat yhteen liittyvän taulukon arvot ja tallentavat tuloksen yksittäiseen soluun. Jos haluat ohjata liittyvän taulukon rivejä, voit käyttää suodattimia taulukoissa ja sarakkeissa. Lisätietoja on artikkelissa Taulukoiden väliset suhteet tietomallissa.

Koska taulukoita voidaan linkittää suhteiden avulla, pivot-taulukot voivat sisältää tietoja useiden eri taulukoiden useista eri sarakkeista.

Kaavat voivat käsitellä kokonaisia taulukoita ja sarakkeita, joten laskutoimitukset on suunniteltava eri tavalla kuin Excelissä.

Yleensä sarakkeen DAX-kaavaa sovelletaan aina sarakkeen koko arvojoukkoon (ei koskaan vain muutamaan riviin tai soluun).
Power Pivot -taulukoissa on oltava aina sama määrä sarakkeita kullakin rivillä, ja sarakkeen kaikkien rivien täytyy sisältää sama tietotyyppi.
Kun taulukot yhdistetään suhteella, on varmistettava, että avaimina käytettävissä kahdessa sarakkeessa olevat arvot täsmäävät suurimmaksi osaksi. Koska viite-eheys ei ole käytössä Power Pivotissa, avainsarakkeessa voi olla myös muita kuin täsmääviä arvoja, ja suhteen luominen on silti mahdollista. Tyhjät tai täsmäämättömät arvot voivat kuitenkin vaikuttaa kaavojen tuloksiin ja pivot-taulukoiden ulkoasuun. Lisätietoja on artikkelissa Haut Power Pivot-kaavoissa.
Kun taulukoita linkitetään käyttäen suhteita, laajennetaan samalla vaikutusaluetta eli kontekstia, jossa kaavat lasketaan. Pivot-taulukon kaavoihin voivat esimerkiksi vaikuttaa suodattimet sekä taulukon sarake- ja riviotsikot. Voit kirjoittaa kontekstia muuttavia kaavoja, mutta konteksti voi myös muuttaa tuloksia odottamattomilla tavoilla. Lisätietoja on artikkelissa DAX-kaavojen konteksti.

Kaavojen tulosten päivittäminen

Tietojen p äivittäminen ja uudelleenlaskenta ovat kaksi erillistä mutta toisiinsa liittyvää toimintoa. Niiden ymmärtäminen on tarpeen suunniteltaessa tietomallia, joka sisältää mutkikkaita kaavoja, suuria tietomääriä tai ulkoisista tietolähteistä noudettuja tietoja.

Tietojen päivittäminen tarkoittaa työkirjan tietojen päivittämistä ulkoisen tietolähteen uusilla tiedoilla. Voit päivittää tiedot manuaalisesti määritetyin aikavälein. Jos olet julkaissut työkirjan SharePoint-sivustossa, voit ajoittaa tiedot päivittymään automaattisesti ulkoisista lähteistä.

Uudelleenlaskenta tarkoittaa kaavojen tulosten päivittämistä siten, että itse kaavoihin ja pohjana oleviin tietoihin tehdyt muutokset tulevat näkyviin. Uudelleenlaskenta voi vaikuttaa suorituskykyyn seuraavilla tavoilla:

Lasketuissa sarakkeissa kaavan tulos on laskettava aina uudelleen koko sarakkeelle, jos kaavaa muutetaan.
Mitoissa kaavojen tuloksia ei lasketa, ennen kuin mitta sijoitetaan pivot-taulukon tai pivot-kaavion kontekstiin. Kaava lasketaan uudelleen, jos päivität pivot-taulukon manuaalisesti tai jos muutat rivi- tai sarakeotsikkoa siten, että muutos vaikuttaa tietojen suodatukseen.

Kaavojen vianmääritys

Virheet kaavaa kirjoitettaessa

Jos saat virheilmoituksen määrittäessäsi laskettua saraketta, kaavassa saattaa olla syntaksivirhe, semanttinen virhe tai laskutoimitusvirhe.

Syntaksivirheet ovat usein helpoimmin ratkaistavissa. Yleensä virhe liittyy puuttuvaan sulkumerkkiin tai pilkkuun. Yksittäisten funktioiden syntaksiin liittyviä ohjeita on DAX-funktiohakemistossa.

Toinen virhetyyppi on kyseessä silloin, kun syntaksi on oikein, mutta viitattu arvo tai sarake ei ole ymmärrettävissä kaavan kontekstissa. Tämänkaltaiset laskutoimitus- ja semanttiset virheet voivat johtua esimerkiksi seuraavista ongelmista:

Kaava viittaa sarakkeeseen, taulukkoon tai funktioon, jota ei ole.
Kaava on oikein, mutta kun PowerPivot hakee tiedot, se havaitsee tyyppiristiriidan ja aiheuttaa virhetilan.
Kaava välittää funktiolle virheellisen parametrimäärän tai -tyypin.
Kaava viittaa toiseen sarakkeeseen, jossa on virhe. Kaavan arvot eivät kelpaa.
Kaava viittaa sarakkeeseen, jota ei ole käsitelty. Metatiedot ovat siis käytettävissä, mutta laskutoimituksia varten tarvittavat tiedot puuttuvat.

Neljässä ensimmäisessä tapauksessa DAX merkitsee koko sarakkeen, joka sisältää virheellisen kaavan. Viimeisessä tapauksessa DAX osoittaa sarakkeen olevan käsittelemättömässä tilassa merkitsemällä sen harmaalla.

Virheellinen tai epätavallinen tulos sarakearvojen luokittelussa tai lajittelussa

Saatat saada virheellisen tai epätavallisen tuloksen, kun luokittelet tai lajittelet sarakkeita, joissa on NaN-arvoja (ei-numero). Kun esimerkiksi nolla jaetaan nollalla, tulos on NaN-arvo.

Tämä johtuu siitä, että kaava suorittaa luokittelun ja lajittelun vertaamalla numeerisia arvoja. NaN-arvoja ei kuitenkaan voi verrata sarakkeen muihin numeroihin.

Voit varmistaa oikean tuloksen käyttämällä JOS-funktiota NaN-arvojen testaamiseen ja numeerisen 0-arvon palauttamiseen.

Analysis Services -palvelun taulukkomallien ja DirectQuery-tilan yhteensopivuus

Pääasiallisesti Power Pivotissa luotavat DAX-kaavat ovat täysin yhteensopivia Analysis Services -palvelun taulukkomallien kanssa. Kaavoihin liittyy kuitenkin joitakin rajoituksia, jos siirrät Power Pivot -mallin Analysis Services -esiintymään ja otat mallin käyttöön DirectQuery-tilassa.

Osa DAX-kaavoista voi palauttaa erilaiset tulokset, jos otat mallin käyttöön DirectQuery-tilassa.
Osa kaavoista voi aiheuttaa tarkistusvirheitä, kun otat mallin käyttöön DirectQuery-tilassa, koska kaavassa on sellainen DAX-funktio, joka ei ole tuettu relaatiotietolähteessä.

Lisätietoja on SQL Server 2012 Books Online -sivuston Analysis Services -palvelun taulukkomallinnuksen asiakirjoissa.