DAX (Data Analysis Expressions) -kieli Power Pivotissa

Data Analysis Expressions (DAX) kuulostaa aluksi hieman pelottavalta, mutta älä anna nimen hämätä sinua. DAX-kielen perusteet on helppo ymmärtää. Todetaan heti aluksi, että DAX ei ole ohjelmointikieli. DAX on kaavakieli. Voit käyttää DAX-kieltä mukautettujen laskutoimitusten määrittämiseen laskettuja sarakkeita ja mittoja (kutsutaan myös lasketuiksi kentiksi) varten. DAX sisältää joitakin Excelin kaavoissa käytettyjä funktioita sekä muita funktioita, jotka on suunniteltu toimimaan relaatiotietojen kanssa ja toteuttamaan dynaamisia koosteita.

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.
• Aikatietojen funktiotDAX-kaavoissa sallivat laskutoimitukset päivämäärävälejä käyttämällä ja vertaavat rinnakkaisten ajanjaksojen tuloksia.

DAX-kaavojen käyttötavat

Voit luoda Power Pivotissa kaavoja joko lasketuissa sarakkeissa tai lasketuissa kentissä.

Lasketut sarakkeet

Laskettu sarake on sarake, jonka lisäät olemassa olevaan 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), laskettua saraketta voidaan käyttää samalla tavalla kuin mitä tahansa muuta tietosaraketta.

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 on luotu erityisesti käytettäväksi Power Pivot -tietoja käyttävässä Pivot-taulukossa (tai Pivot-kaaviossa). 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

Power Pivotissa, kuten Excelissä, on kaavarivi, joka helpottaa kaavojen luomista ja muokkaamista, sekä automaattisen täydennyksen toimintoja kirjoitus- ja syntaksivirheiden minimoimiseksi.

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

Sarakkeen nimen kirjoittaminen Kirjoita hakasulje ja valitse sitten sarake nykyisen taulukon sarakeluettelosta. 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 loppusulkeita 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.

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. Yleensä DAX-funktioita ei voi käyttää Excel-kaavassa tai Excel-kaavoja Power Pivotissa ilman muutoksia.
• 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. Power Pivotin uusi ominaisuus on mahdollisuus viitata helposti täydellisiin taulukoihin ja sarakkeisiin.
• 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

Voit tuoda tietoja Power Pivot -tietomalliin useista eri tietolähteistä, jotka saattavat tukea eri tietotyyppejä. Kun tuot tai lataat tietoja ja käytät sitten tietoja laskutoimituksissa tai Pivot-taulukoissa, tiedot muunnetaan yhdeksi 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, jossa voit käsitellä useita tietotaulukoita ja yhdistää taulukot relaatiomalliin. 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 Pivotin taulukoissa on aina oltava sama määrä sarakkeita kullakin rivillä, ja sarakkeen kaikkien rivien on sisällettävä 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 Power Pivot ei säilytä viite-eheyttä, avainsarakkeessa voi olla arvoja, jotka eivät täsmää, ja silti luoda suhteen. 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

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

• 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.