Pikaopas: Opi DAX-kielen perusteet puolessa tunnissa

Tehtävä: Yksinkertaisen lasketun sarakkeen kaavan luominen

1. Jos et ole vielä Power Pivot -ikkunassa, napsauta Excelin Power Pivot-valintanauhassa Power Pivot Ikkuna.

2. Valitse Power Pivot -ikkunassa FactSales-taulukko (välilehti).

3. Siirry äärimmäisenä oikealla olevaan sarakkeeseen ja valitse sarakeotsikossa Lisää sarake.

4. Napsauta kaavariviä mallin suunnitteluikkunan yläreunassa.

   

   Kohdistin näkyy nyt kaavarivillä. Kaavariville voi kirjoittaa lasketun sarakkeen tai lasketun kentän kaavan.

   Tarkastellaan hetki kaavarivin vasemmalla puolella olevia kolmea painiketta.

   

   Kun kohdistin on aktiivinen kaavarivillä, nuo kolme painiketta aktivoituvat. Vasemmanpuoleisin painike X on peruutuspainike. Napsauta sitä. Kohdistin ei enää näy kaavarivillä, eivätkä peruutuspainike ja valintamerkkipainike ole enää näkyvissä. Napsauta kaavariviä uudelleen. Peruutuspainike ja valintamerkkipainike tulevat uudelleen näkyviin. Tämä tarkoittaa, että voit aloittaa kaavan syöttämisen.

   Valintamerkkipainike on kaavan tarkistuspainike. Sillä ei ole käyttöä, ennen kuin olet syöttänyt kaavan. Palaamme tähän hieman jäljempänä.

   Napsauta Fx-painiketta. Huomaat, että näkyviin tulee uusi valintaikkuna: Funktion lisääminen -valintaikkuna. Funktion lisääminen -valintaikkuna on helpoin tapa aloittaa DAX-kaavan syöttäminen. Lisäämme funktion kaavaan, kun luomme mittayksikön hieman jäljempänä, mutta nyt sinun ei tarvitse lisätä funktiota lasketun sarakkeen kaavaan. Sulje Funktion lisääminen -valintaikkuna.

5. Kirjoita kaavariville yhtäläisyysmerkki = ja sen jälkeen vasen hakasulku [. Huomaat, että FactSales-taulukkoon tulee näkyviin pieni ikkuna, jossa ovat kaikki sarakkeet. Näin IntelliSense toimii.

   Koska lasketut sarakkeet luodaan aina siihen aktiiviseen taulukoon, jossa olet, ei sarakkeen nimen edessä tarvitse olla taulukon nimeä. Siirry alas ja kaksoisnapsauta [SalesQuantity]-kohtaa. Voit myös siirtyä haluamaasi sarakkeen nimeen ja painaa sitten sarkainta.

   Kohdistin on nyt aktiivinen [SalesQuantity]-kohdan oikealla puolella.

6. Kirjoita välilyönti ja kirjoita sitten vähennyslaskuoperaattori - (miinusmerkki) ja sen jälkeen toinen välilyönti.

7. Kirjoita nyt toinen vasen hakasulku [. Valitse tällä kertaa [ReturnQuantity]-sarake ja paina Enter-näppäintä.

   Jos saat virheilmoituksen, tarkastele syntaksiasi huolellisesti. Vertaa sitä tarvittaessa aiemmin kuvatun Margin-nimiseen lasketun sarakkeen kaavaan.

   Kun olet painanut Enter-näppäintä kaavan viimeistelemiseksi, Power Pivot -ikkunan alalaidan tilariville tulee näkyviin teksti Lasketaan. Toiminto etenee nopeasti, vaikka uusia arvoja lasketaan yli kolmelle miljoonalle riville.

8. Napsauta sarakeotsikkoa hiiren kakkospainikkeella ja nimeä sarake uudelleen NetSales-nimiseksi.

Siinä se! Olet juuri luonut yksinkertaisen mutta hyvin tehokkaan DAX-kaavan. NetSales-kaava laskee kullekin FactSales-taulukon riville arvon vähentämällä [ReturnQuantity]-sarakkeen arvon [SalesQuantity]-sarakkeen arvosta. Huomaa, miten juuri sanoimme "Kullekin riville". Tämä on toisen hyvin tärkeän DAX-käsitteen välähdys. rivikonteksti. Saat lisätietoja rivikontekstista myöhemmin.

Kun DAX-kaavaan kirjoitetaan operaattoria, on hyvin tärkeää ymmärtää käytettävien argumenttien tietotyyppi. Jos esimerkiksi kirjoitat seuraavan kaavan, = 1 & 2, palautettu arvo on tekstiarvo "12". Tämä johtuu siitä, että et-merkkioperaattoria (&) käytetään tekstin yhdistämiseen. DAX tulkitsee tämän kaavan seuraavasti: laske tulos ottamalla arvo 1 tekstinä ja lisäämällä arvo 2 tekstinä. Jos kirjoitat = 1 + 2, DAX lukee kaavan seuraavasti: laske tulos ottamalla numeroarvo 1 ja lisäämällä numeroarvo 2. Tuloksena on tietysti "3", numeroarvo. DAX laskee tulokseksi saatavat arvot kaavan operaattorin perusteella, ei argumentissa käytettävien sarakkeiden tietotyyppien perusteella. DAX-kielen tietotyypit ovat hyvin tärkeitä, mutta tämän pikaoppaan soveltamisalueen ulkopuolella. Katso lisätietoja DAX-kaavojen tietotyypeistä ja operaattoreista DAX-viitteestä (http://go.microsoft.com/fwlink/?LinkId=239769&clcid=0x409) Books Onlinessa.

Otetaanpa toinen esimerkki. Tällä kertaa luot mittayksikön kirjoittamalla kaavan ja käyttämällä IntelliSenseä. Älä ole huolissasi, vaikket ymmärräkään kaavaa täysin. On tärkeää oppia luomaan kaava käyttämällä useita elementtejä yhdessä oikeassa syntaksissa.

Tehtävä: Mittayksikön kaavan luominen

1. Napsauta FactSales-taulukossa mitä tahansa laskenta-alueella olevaa tyhjää solua. Tämä tyhjien solujen alue on Power Pivot -ikkunassa heti taulukon alapuolella.

1. Kirjoita kaavariville nimi Previous Quarter Sales:

2. Aloita laskukaava kirjoittamalla yhtäläisyysmerkki =.

3. Kirjoita muutama ensimmäinen kirjain CAL ja kaksoisnapsauta sitten funktiota, jota haluat käyttää. Tässä kaavassa haluat käyttää CALCULATE-funktiota.

4. Kirjoita alkusulkumerkki ( osoittamaan CALCULATE-funktioon välitettävien argumenttien alkua.

   Huomaa, että alkusulkumerkin kirjoittamisen jälkeen IntelliSense näyttää sinulle CALCULATE-funktioon vaadittavat argumentit. Lisätietoa argumenteista on hieman jäljempänä.

5. Kirjoita FactSales-taulukon pari ensimmäistä kirjainta ja kaksoisnapsauta sitten avattavassa luettelossa FactSales[Sales].

6. Kirjoita pilkku (,) ensimmäisen suodattimen määrittämiseksi, kirjoita sitten PRE ja kaksoisnapsauta PREVIOUSQUARTER-funktiota.

   PREVIOUSQUARTER-funktion valinnan jälkeen näkyviin tulee toinen alkusulkumerkki merkkinä siitä, että tarvitaan toinen argumentti, tällä kertaa PREVIOUSQUARTER-funktiolle.

7. Kirjoita pari ensimmäistä kirjainta Dim ja kaksoisnapsauta sitten DimDate[DateKey].

8. Sulje sekä PREVIOUSQUARTER-funktiolle että CALCULATE-funktiolle välitettävä argumentti kirjoittamalla kaksi loppusulkumerkkiä )).

   Kaavan pitäisi näyttää seuraavanlaiselta:

   Previous Quarter Sales:=CALCULATE(FactSales[Sales], PREVIOUSQUARTER(DimDate[DateKey]))

9. Vahvista kaava napsauttamalla kaavan tarkistuspainiketta kaavarivillä. Jos saat virheilmoituksen, tarkista syntaksin jokainen elementti.

Hienoa! Onnistuit juuri luomaan mittayksikön DAX-kielen avulla, mikä ei ole helppo tehtävä. Tämä kaava laskee edellisen neljänneksen kokonaismyynnin pivot-taulukossa tai pivot-kaaviossa määritettyjen suodattimien mukaan.

Tutustuit juuri useisiin tärkeisiin DAX-kaavoja koskeviin seikkoihin. Ensinnäkin tässä kaavassa oli kaksi funktiota. Huomaa, että PREVIOUSQUARTER-funktio on sisäkkäin CALCULATE-funktiolle välitettynä argumenttina. DAX-kaavoissa voi olla enintään 64 sisäkkäistä funktiota. On epätodennäköistä, että jossain kaavassa olisi niin monta sisäkkäistä funktiota. Tällaisen kaavan luominen ja sen virheiden korjaaminen olisi hyvin vaikeaa, eikä kaava varmaankaan olisi kovin nopea.

Tässä kaavassa käytit myös suodattimia. Suodattimilla rajataan laskettavia asioita. Tässä tapauksessa valitsit yhden suodattimen argumentiksi, joka on itse asiassa toinen funktio. Suodattimista on lisätietoja jäljempänä.

Lisäksi käytit CALCULATE-funktiota. Se on yksi DAX-kielen tehokkaimmista funktioista. Kun luot tietomalleja ja luot monimutkaisempia kaavoja, käytät todennäköisesti tätä funktiota monta kertaa. CALCULATE-funktiosta kertominen ei kuulu tämän pikaoppaan piiriin, mutta kun tietämyksesi DAX-kielestä kasvaa, kiinnitä erityistä huomiota kyseiseen funktioon.

Lisäpistetehtävä: Mittayksikön kaavan luominen AutoSum-toiminnon avulla

1. Vieritä FactSales-taulukossa ReturnQuantity-sarakkeeseen ja napsauta sitten sarakeotsikkoa koko sarakkeen valitsemiseksi.

2. Napsauta Aloitus-välilehden valintanauhan Laskutoimitukset-ryhmässä Automaattinen summa -painiketta.

Napsauta Automaattinen summa -kohdan vieressäolevaa alanuolta ja valitse Keskiarvo (huomaa myös muut käytettävissäsi ovat vakiokoostefunktiot).

Tällöin luodaan heti uusi laskettu kenttä nimellä Average of ReturnQuantity:, ja sen jälkeen tulee kaava =AVERAGE([ReturnQuantity]).

Eikö ollutkin helppoa? Kaikki luomasi kaavat eivät ole yhtä yksinkertaisia. AutoSum-ominaisuuden avulla voit luoda kaavoja nopeasti ja helposti käyttämällä vakiokoostelaskutoimituksia.

Tämän avulla sinun pitäisi ymmärtää melko hyvin, mistä DAX-kaavoissa käytettävässä syntaksissa on kyse. Tutustuit lisäksi joihinkin todella upeisiin ominaisuuksiin, kuten IntelliSense ja AutoSum, joiden avulla luot kaavoja nopeasti, helposti ja tarkasti. Toki voit oppia syntaksista vielä paljon enemmän. Hyödyllistä lisätietoa on DAX-viitetiedoissa tai SQL Books Online -resursseissa.

Testaa tietosi syntaksista

1. Mitä tämä painike kaavarivillä tekee?
   

2. Mitä DAX-kaavassa on aina sarakkeen nimen ympärillä?

3. Miten kirjoitat kaavan seuraavaa varten: Laske
   DimProduct-taulukonlasketun UnitMargin-sarakkeenkunkin rivin arvo vähentämällä UnitCost-sarakkeen arvot Yksikköhinta-sarakkeen arvoista?

Vastaukset ovat tämän ohjeaiheen lopussa.

Tehtävä: Funktion lisääminen kaavaan funktion lisäämisominaisuutta käyttämällä

1. Vieritä FactSales-taulukossa oikeanlaisinta saraketta ja valitse sarakeotsikossa Lisää sarake.

2. Kirjoita kaavariville yhtäläisyysmerkki =.

3. Napsauta Lisää funktio -painiketta. Lisää funktio -valintaikkuna avautuu.

4. Napsauta Funktion lisääminen -valintaikkunassa Valitse luokka -luetteloruutua. Oletusarvoisesti kaikki on valittuna, ja kaikki Kaikki-luokan funktiot on lueteltu alla. Siinä on paljon funktioita, joten funktioita kannattaa suodattaa, jotta haluamasi tyyppisen funktion löytäminen helpottuu.

5. Tätä kaavaa varten halutaan palauttaa joitakin tietoja, jotka ovat jo toisessa taulukossa. Tähän käytetään Suodatin-luokkaan kuuluvaa funktiota. Napsauta Suodatin-luokkaa ja valitse sitten Valitse funktio ,vieritä alaspäin ja kaksoisnapsauta RELATED-funktiota. Sulje Funktion lisääminen -valintaikkuna valitsemallaOK.

6. Käytä IntelliSenseä apuna DimChannel[ChannelName]-sarakkeen löytämisessä ja valitsemisessa.

7. Sulje kaava ja paina Enter-näppäintä.

8. Kun olet painanut Enter-näppäintä kaavan viimeistelemiseksi, Power Pivot -ikkunan alalaidan tilarivillä näkyy teksti Lasketaan. Olet luonut uuden sarakkeen FactSales-taulukkoon käyttämällä DimChannel-taulukon kanavatietoja.

9. Nimeä sarake uudelleen Channel-nimiseksi.

   Kaavan pitäisi näyttää tältä: =RELATED(DimChannel[ChannelName])

Tutustuit juuri toiseen hyvin tärkeään DAX-funktion, RELATED-funktion, käyttöön. RELATED-funktio palauttaa arvoja toisesta taulukosta. RELATED-funktion käyttämisen edellytyksenä on, että käsittelemäsi taulukon ja noudettavia tietoja sisältävän taulukon väillä on suhde. RELATED-funktion käyttömahdollisuudet ovat valtaisat. Tässä tapauksessa voit nyt sisällyttää FactSales-taulukon jokaiseen myyntitapahtumaan myyntikanavan. Nyt voit piilottaa DimChannel-taulukon pivot-taulukon kenttäluettelosta, mikä helpottaa luettelossa siirtymistä ja vain tärkeimpien tarvittavien tietojen tarkastelua. Edellä kuvan CALCULATE-funktion lailla myös RELATED-funktio on hyvin tärkeä, ja käytät sitä todennäköisesti monta kertaa.

Kuten huomaat, DAX-kielen funktioiden avulla voi luoda hyvin tehokkaita kaavoja. Tässä käsiteltiin vain funktioiden perusteita. Kun DAX-taitosi paranevat, voit luoda kaavoja käyttäen monia eri funktioita. Yksi parhaista paikoista saada lisätietoja kaikista DAX-funktioista on Dax (Data Analysis Expressions) -oppaassa.

Testaa tietosi funktioista

1. Mihin funktio viittaa aina?

2. Voiko kaava sisältää enemmän kuin yhden funktion?

3. Mitä funktioluokkaa sinä käyttäisit kahden tekstimerkkijonon liittämiseen yhdeksi merkkijonoksi?

Vastaukset ovat tämän ohjeaiheen lopussa.

Rivikonteksti

Rivikontekstiksi ymmärretään tavallisesti nykyinen rivi. Muistatko esimerkiksi Margin-nimisen lasketun sarakkeen, joka mainittiin edellä, kun tutustuttiin syntaksiin? Kaava =[SalesAmount] - [TotalCost] laskee arvon Margin-sarakkeeseen taulukon jokaiselle riville. Kunkin rivin arvot lasketaan kahden muun sarakkeen, [SalesAmount] ja [TotalCost], samalla rivillä olevista arvoista. DAX voi laskea arvot Margin-sarakkeen kullekin riville, koska sillä on konteksti: se ottaa jokaista riviä varten [TotalCost]-sarakkeen arvot ja vähentää nämä [SalesAmount]-sarakkeen arvoista.

Alla näkyvässä valitussa solussa nykyisen rivin arvo $49.54 laskettiin vähentämällä [TotalCost]-sarakkeen arvo $51.54 [SalesAmount]-sarakkeen arvosta $101.08.

Rivikontekstia käytetään muuallakin kuin lasketuissa sarakkeissa. Rivikontekstia käytetään myös silloin, kun kaavassa on funktio, joka käyttää suodattimia taulukon yksittäisen rivin tunnistamiseen. Funktio käyttää luontaisesti rivikontekstia jokaiseen sen taulukon riviin, jota se suodattaa. Tämäntyyppistä rivikontekstia sovelletaan useimmiten mittayksiköihin.

Suodatinkonteksti

Suodatinkonteksti on hieman vaikeampi ymmärtää kuin rivikonteksti. Suodatinkontekstin voi helpoiten ymmärtää seuraavasti: yksi tai useampi suodatin, jota käytetään tuloksen tai arvon määrittävässä laskutoimituksessa.

Suodatinkontekstia ei käytetä rivikontekstin sijasta, vaan sitä käytetään rivikontekstin lisäksi. Jos esimerkiksi haluat rajata laskutoimitukseen sisällytettäviä arvoja tarkemmin, voit käyttää suodatinkontekstia, joka määrittää paitsi rivikontekstin myös vain tietyn arvon (suodattimen) kyseisessä rivikontekstissa.

Suodatinkontekstia näkee helpoimmin pivot-taulukoissa. Kun esimerkiksi lisäät arvoalueelle TotalCost ja lisäät sen jälkeen vuoden ja alueen riveihin tai sarakkeisiin, määrität suodatinkontekstin, joka valitsee tietojen alijoukon antamasi tietyn vuoden ja alueen perusteella.

Miksi suodatinkonteksti on niin tärkeä DAX-kielelle? Suodatinkontekstia on helpointa käyttää lisäämällä sarake- ja riviotsikoita ja osittajia pivot-taulukkoon, mutta suodatinkontekstia voidaan myös käyttää DAX-kaavassa määrittämällä suodatin, joka käyttää funktioita kuten ALL, RELATED, FILTER, CALCULATE, käyttämällä suhteita ja käyttämällä muita mittayksiköitä ja sarakkeita. Tarkastellaanpa esimerkiksi seuraavaa kaavaa StoreSales-nimisessä mittayksikössä:

Tämä kaava on selvästi paljon monimutkaisempi kuin jotkin muut näkemäsi kaavat. Tämä kaava on helpompi ymmärtää, kun se eritellään samalla tavalla kuin muut kaavat edellä.

Tämä kaava sisältää seuraavat syntaksielementit:

1. Mittayksikön nimi StoreSales, jonka jälkeen tulee kaksoispiste :.

2. Yhtäläisyysmerkkioperaattori (=) ilmaisee kaavan alun.

3. CALCULATE-funktio laskee argumenttina lausekkeen kontekstissa, jota on muutettu määritetyillä suodattimilla.

4. Kaarisulkeet () ympäröivät yhtä tai useampaa argumenttia.

5. Mittayksikkö [Sales] samassa taulukossa lausekkeena. Sales-mittayksikössä on kaava: =SUM(FactSales[SalesAmount]).

6. Kukin suodatin erotellaan pilkulla (,).

7. Viitattu sarake ja tietty arvo DimChannel[ChannelName] ="Store" suodattimena.

Tämä kaava varmistaa, että vain Sales-mittayksikön suodattimena määrittämät myyntiarvot lasketaan vain DimChannel[ChannelName]-sarakkeen sellaisille riveille, joissa on suodattimena ”Store”-arvo.

Kuten voit kuvitella, mahdollisuus määrittää suodatinkonteksteja kaavassa tarjoaa valtaisaa ja tehokasta käyttöpotentiaalia. Mahdollisuus viitata vain tiettyyn arvoon liittyvässä taulukossa on vain yksi esimerkki siitä. Älä ole huolissasi, vaikket ymmärräkään kontekstia heti täysin. Kun luot omia kaavojasi, opit paremmin ymmärtämään kontekstia ja sen tärkeyttä DAX-kielessä.

Testaa tietosi kontekstista

1. Mitkä ovat kaksi kontekstityyppiä?

2. Mikä suodatinkonteksti on?

3. Mikä rivikonteksti on?

Vastaukset ovat tämän ohjeaiheen lopussa.