Tässä osassa on linkkejä esimerkkeihin, jotka havainnollistavat DAX-kaavojen käyttöä seuraavissa skenaarioissa.
- monimutkaisten laskutoimitusten suorittaminen
- Tekstin ja päivämäärien käsitteleminen
- Ehdolliset arvot ja virheiden testaaminen
- Aikatietojen käyttäminen
- Arvojen luokittelu ja vertailu
Artikkelin sisältö
Aloitusopas
DAX-resurssikeskuksen wikissä on kaikenlaisia tietoja DAX:stä, kuten alan johtavien ammattilaisten ja Microsoftin blogeja, näytteitä, teknisiä raportteja ja videoita.
Skenaariot: monimutkaisten laskutoimitusten suorittaminen
DAX-kaavoilla voidaan suorittaa monimutkaisia laskutoimituksia, joihin liittyy mukautettuja koosteita, suodatusta ja ehdollisten arvojen käyttöä. Tässä osassa on esimerkkejä siitä, miten pääset alkuun mukautettujen laskutoimitusten kanssa.
Mukautettujen laskutoimitusten luominen Pivot-taulukkoon
CALCULATETABLE- ja CALCULATETABLE-funktiot ovat tehokkaita ja joustavia funktioita, jotka ovat hyödyllisiä laskettujen kenttien määrityksessä. Näiden funktioiden avulla voit muuttaa kontekstia, jossa laskutoimitus suoritetaan. Voit myös mukauttaa suoritettavan koosteen tai matemaattisen laskutoimituksen tyypin. Katso esimerkkejä seuraavista ohjeartikkeleista.
Suodattimen käyttäminen kaavassa
Useimmissa paikoissa, joissa DAX-funktio käyttää taulukkoa argumenttina, voit yleensä välittää suodatetun taulukon joko käyttämällä FILTER-funktiota taulukon nimen sijasta tai määrittämällä suodatinlausekkeen yhdeksi funktion argumenteista. Seuraavissa artikkeleissa on esimerkkejä suodattimien luomisesta ja suodattimien vaikutuksesta kaavojen tuloksiin. Lisätietoja on artikkelissa DAX-kaavojen tietojen suodattaminen.
FILTER-funktion avulla voit määrittää suodatusehtoja lausekkeen avulla, kun taas muut funktiot on suunniteltu erityisesti tyhjien arvojen suodattamiseen pois.
Luo dynaaminen suhde poistamalla suodattimet valikoivasti
Luomalla dynaamisia suodattimia kaavoihin voit helposti vastata esimerkiksi seuraaviin kysymyksiin:
- Mikä oli nykyisen tuotteen myynnin osuus vuoden kokonaismyynnistä?
- Kuinka paljon tämä divisioona on vaikuttanut kokonaistulokseen kaikilla toimintavuosina verrattuna muihin divisiooniin?
Pivot-taulukon konteksti voi vaikuttaa Pivot-taulukossa käyttämiisi kaavoihin, mutta voit muuttaa kontekstia valikoivasti lisäämällä tai poistamalla suodattimia. ALL-ohjeaiheen esimerkissä näytetään, miten tämä tehdään. Jos haluat selvittää tietyn jälleenmyyjän myynnin suhteen kaikkien jälleenmyyjien myyntiin, luo mittayksikkö, joka laskee nykyisen kontekstin arvon jaettuna ALL-kontekstin arvolla.
ALLEXCEPT-ohjeaiheessa on esimerkki kaavan suodattimien valikoivasta poistamisesta. Molemmissa esimerkeissä kerrotaan, miten tulokset muuttuvat Pivot-taulukon rakenteen mukaan.
Muita esimerkkejä suhteiden ja prosenttiarvojen laskemisesta on seuraavissa ohjeaiheissa:
Ulkosilmukan arvon käyttäminen
Sen lisäksi, että DAX käyttää laskutoimituksissa nykyisen kontekstin arvoja, se voi käyttää edellisen silmukan arvoa luodessaan joukon toisiinsa liittyviä laskutoimituksia. Seuraavassa aiheessa kuvataan, miten voit muodostaa kaavan, joka viittaa arvoon ulonnasta silmukasta. EARLIER-funktio tukee enintään kahta sisäkkäisten silmukoiden tasoa.
Lisätietoja rivikontekstista ja siihen liittyvistä taulukoista sekä tämän käsitteen käyttämisestä kaavoissa on kohdassa DAX-kaavojen konteksti.
Skenaariot: tekstin ja päivämäärien käsitteleminen
Tämä osio sisältää linkkejä DAX-viiteaiheisiin, joissa on esimerkkejä yleisistä skenaarioista, joihin liittyy tekstin käsitteleminen, päivämäärä- ja kellonaika-arvojen poimiminen ja muodostaminen tai arvojen luominen ehdon perusteella.
Avainsarakkeen luominen ketjuttamalla
Power Pivot ei salli yhdistelmänäppäimiä. Jos tietolähteessäsi on yhdistelmäavaimia, sinun on ehkä yhdistettävä ne yhdeksi avainsarakkeeksi. Seuraavassa ohjeaiheessa on esimerkki yhdistelmäavaimeen perustuvan lasketun sarakkeen luomisesta.
Compose a date based on text date rates
Power Pivot käyttää SQL Server:n päivämäärä/aika-tietotyyppiä päivämääriä käsiteltäessä. Jos siis ulkoiset tietosi sisältävät eri tavalla muotoiltuja päivämääriä -- jos päivämäärät on esimerkiksi kirjoitettu alueellisessa päivämäärämuodossa, jota Power Pivot -tietomoduuli ei tunnista, tai jos tiedot käyttävät kokonaisluvun korvaavia avaimia, sinun on ehkä poimittava päivämääräosat DAX-kaavalla ja muodostettava osat kelvolliseksi päivämääräksi/ aikaesitys.
Jos sinulla on esimerkiksi päivämääriä sisältävä sarake, joka on esitetty kokonaislukuna ja sitten tuotu tekstimerkkijonona, voit muuntaa merkkijonon päivämäärä- ja aika-arvoksi seuraavan kaavan avulla:
=PÄIVÄYS(OIKEA([Arvo1];4);VASEN([Arvo1];2),POIMI([Arvo1];2))
| Arvo1 | Tulos |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Seuraavissa artikkeleissa on lisätietoja päivämäärien poimimiseen ja muodostamiseen käytettävistä funktioista.
Mukautetun päivämäärä- tai numeromuodon määrittäminen
Jos tiedot sisältävät päivämääriä tai numeroita, joita ei ole esitetty missään Windowsin vakiotekstimuodossa, voit määrittää mukautetun muodon sen varmistamiseksi, että arvot käsitellään oikein. Näitä muotoja käytetään muunnettaessa arvoja merkkijonoiksi tai merkkijonoista. Seuraavissa artikkeleissa on myös yksityiskohtainen luettelo esimääritetyistä muodoista, jotka ovat käytettävissä päivämäärien ja numeroiden käsittelyyn.
- FORMAT-funktion ennalta määritetyt numeeriset muodot
- FORMAT-funktion mukautetut numeeriset muodot
- FORMAT-funktion ennalta määritetyt päivämäärä- ja kellonaikamuodot
- FORMAT-funktion mukautetut päivämäärä- ja kellonaikamuodot
Tietotyyppien muuttaminen kaavan avulla
Power Pivotissa lähdesarakkeet määräävät tuloksen tietotyypin, eikä tuloksen tietotyyppiä voi määrittää eksplisiittisesti, koska Power Pivot määrittää optimaalisen tietotyypin. Voit kuitenkin käyttää Power Pivotin suorittamia implisiittisiä tietotyyppimuunnoksia tulosteen tietotyypin muokkaamiseen.
- Muunna päivämäärä tai numeromerkkijono luvuksi kertomalla 1,0:lla. Esimerkiksi seuraava kaava laskee kuluvan päivämäärän miinus 3 päivää ja tulostaa sitten vastaavan kokonaislukuarvon.
=(TÄNÄÄN()-3)*1.0 - Jos haluat muuntaa päivämäärä-, luku- tai valuutta-arvon merkkijonoksi, ketjuta arvo tyhjällä merkkijonolla. Esimerkiksi seuraava kaava palauttaa tämän päivän päivämäärän merkkijonona.
=""& TODAY()
Seuraavia funktioita voidaan myös käyttää varmistamaan, että tietty tietotyyppi palautetaan:
Reaalilukujen muuntaminen kokonaisluvuiksi
- ROUND-funktio
- CEILING-toiminto
-
FLOOR-funktio
Reaalilukujen, kokonaislukujen tai päivämäärien muuntaminen merkkijonoiksi - FIXED-funktio
-
FORMAT-funktio
Merkkijonojen muuntaminen todellisiksi luvuiksi tai päivämääriksi - VALUE-funktio
- PÄIVÄYSARVO-funktio
- AIKA_ARVO-funktio
Tilanne: Ehdolliset arvot ja virheiden testaaminen
Excelin tavoin myös DAX-kielessä on funktioita, joiden avulla voit testata tietojen arvoja ja palauttaa eri arvon tietyn ehdon perusteella. Voit esimerkiksi luoda lasketun sarakkeen, jossa jälleenmyyjille on merkintä joko Ensisijainen tai Arvo vuosittaisen myyntisumman mukaan. Arvoja testaavat funktiot ovat hyödyllisiä myös arvoalueen tai -tyypin tarkistamisessa, jotta odottamattomat tietovirheet eivät keskeytä laskutoimituksia.
Arvon luominen ehdon perusteella
Voit käyttää sisäkkäisiä JOS-ehtoja arvojen testaamiseen ja uusien arvojen luomiseen ehdollisesti. Seuraavissa ohjeaiheissa on yksinkertaisia esimerkkejä ehdollisesta käsittelystä ja ehdollisista arvoista:
Kaavan virheiden testaaminen
Toisin kuin Excelissä, lasketun sarakkeen yhdellä rivillä ei voi olla kelvollisia arvoja ja toisella rivillä virheellisiä arvoja. Jos siis jossakin Power Pivot -sarakkeen osassa on virhe, koko sarake merkitään virheelliseksi, joten virheellisiin arvoihin johtavat kaavavirheet on aina korjattava.
Jos esimerkiksi luot kaavan, joka jakaa nollalla, voit saada äärettömän tuloksen tai virheen. Jotkin kaavat epäonnistuvat myös, jos funktio havaitsee tyhjän arvon, vaikka se odottaa numeerista arvoa. Kun tietomallia kehitetään, virheiden kannattaa tulla näkyviin, jotta voit napsauttaa sanomaa ja tehdä ongelman vianmäärityksen. Kun julkaiset työkirjoja, käytä kuitenkin virheenkäsittelyä, jotta odottamattomat arvot eivät aiheuta laskutoimitusten epäonnistumista.
Jotta laskettuun sarakkeeseen ei palauteta virheitä, testaa virheet loogisten funktioiden ja tietofunktioiden yhdistelmällä, jolloin palautat kelvolliset arvot. Seuraavissa aiheissa on yksinkertaisia esimerkkejä siitä, miten tämä tehdään DAX-kielessä:
Skenaariot: aikatietojen käyttäminen
DAX-kielen aikatietofunktiot sisältävät funktioita, joiden avulla voit noutaa päivämääriä tai päivämääräalueita tiedoistasi. Voit sitten käyttää näitä päivämääriä tai päivämääräalueita samankaltaisten ajanjaksojen arvojen laskemiseen. Aikatietofunktioissa on myös vakiopäivämäärävälejä käyttäviä funktioita, joiden avulla voit verrata kuukausien, vuosien tai neljännesvuosien arvoja. Voit myös luoda kaavan, joka vertaa määritetyn ajanjakson ensimmäisen ja viimeisen päivämäärän arvoja.
Luettelo kaikista aikatietojen funktioista on artikkelissa Time Intelligence Functions (DAX). Vihjeitä päivämäärien ja kellonaikojen tehokkaaseen käyttöön Power Pivot -analyysissa on artikkelissa Päivämäärät Power Pivotissa.
Kumulatiivisen myynnin laskeminen
Seuraavissa artikkeleissa on esimerkkejä loppu- ja alkusaldojen laskemisesta. Esimerkeissä voit luoda juoksevia saldoja eri aikajaksoille, kuten päiville, kuukausille, neljännesvuosille tai vuosille.
- CLOSINGBALANCEMONTH-funktio, CLOSINGBALANCEQUARTER-funktio, CLOSINGBALANCEYEAR-funktio
- OPENINGBALANCEMONTH-funktio, OPENINGBALANCEQUARTER-funktio, OPENINGBALANCEYEAR-funktio
Arvojen vertaaminen eri aikoina
Seuraavissa ohjeaiheissa on esimerkkejä siitä, miten voit vertailla summia eri aikakausina. DAX-kielen tukemat oletusarvoiset ajanjaksot ovat kuukaudet, neljännesvuodet ja vuodet.
- PREVIOUSMONTH-funktio, PREVIOUSQUARTER,PREVIOUSYEAR-funktio
- TOTALMTD-funktio, TOTALQTD-funktio, TOTALYTD-funktio
- PARALLELPERIOD-funktio
Arvon laskeminen mukautetulla päivämäärävälillä
Seuraavissa ohjeaiheissa on esimerkkejä mukautettujen päivämääräjaksojen hakemisesta, kuten ensimmäiset 15 päivää myynninedistämisen alkamisen jälkeen.
Jos käytät aikatietofunktioita mukautetun päivämääräjoukon hakemiseen, voit käyttää tätä päivämääräjoukkoa syötteenä funktiolle, joka suorittaa laskutoimituksia ja luoda mukautettuja koosteita aikajaksoista. Seuraavassa aiheessa on esimerkki tästä:
-
Huomautus
Jos et halua määrittää mukautettua päivämääräaluetta, mutta käytät tavanomaisia laskentayksiköitä, kuten kuukausia, neljännesvuosia tai vuosia, suosittelemme laskutoimitusten suorittamiseen tarkoitukseen suunniteltuja aikatietofunktioita, kuten TOTALQTD, TOTALMTD, TOTALQTD jne.
Skenaariot: Arvojen luokittelu ja vertailu
Jos haluat näyttää vain sarakkeessa tai Pivot-taulukossa olevat ylimmät n kohdetta, käytettävissäsi on useita vaihtoehtoja:
- Voit luoda yläsuodattimen Excelin ominaisuuksien avulla. Voit myös valita pivot-taulukosta useita ylimpiä tai alimmat arvoja. Tämän osion ensimmäisessä osassa kuvataan, miten voit suodattaa Pivot-taulukon ensimmäiset 10 kohdetta. Lisätietoja on Excelin ohjeissa.
- Voit luoda kaavan, joka asettaa arvot dynaamisesti järjestykseen, ja suodattaa sitten luokitusarvojen mukaan, tai käyttää luokitusarvoa osittajana. Tämän osan toisessa osassa kuvataan, miten tämä kaava luodaan ja miten sitä sitten käytetään osittajassa.
Jokaisella menetelmällä on etuja ja haittoja.
- Excelin yläsuodatinta on helppo käyttää, mutta suodatin on tarkoitettu vain näyttötarkoituksiin. Jos Pivot-taulukon pohjana olevat tiedot muuttuvat, sinun on päivitettävä Pivot-taulukko manuaalisesti, jotta näet muutokset. Jos sinun tarvitsee käsitellä sijoituksia dynaamisesti, voit luoda DAX-kielen avulla kaavan, jossa verrataan arvoja sarakkeen muihin arvoihin.
- DAX-kaava on tehokkaampi; Lisäämällä lajitteluarvon osittajaan voit lisäksi napsauttaa osittajaa ja muuttaa näytettävien ensimmäisten arvojen määrää. Laskutoimitukset ovat kuitenkin laskennallisesti kalliita, eikä tämä menetelmä sovellu taulukoihin, joissa on useita rivejä.
Vain kymmenen ensimmäistä kohdetta näyttäminen Pivot-taulukossa
Ylimpien tai pienimpien arvojen näyttäminen Pivot-taulukossa
|
|---|
Kohteiden järjestäminen dynaamisesti kaavan avulla
Seuraavassa aiheessa on esimerkki siitä, miten voit DAX-kielen avulla luoda luokittelun, joka tallennetaan laskettuun sarakkeeseen. Koska DAX-kaavat lasketaan dynaamisesti, voit aina olla varma, että luokitus on oikein, vaikka pohjana olevat tiedot olisivat muuttuneet. Koska kaavaa käytetään lasketussa sarakkeessa, voit käyttää sijoitusta Osittajassa ja valita sitten 5, 10 parasta tai jopa 100 parasta arvoa.