Kontekstin avulla voit suorittaa dynaamisen analyysin, jossa kaavan tulokset voivat muuttua vastaamaan nykyistä rivi- tai soluvalintaa sekä liittyviä tietoja. Kontekstin ymmärtäminen ja kontekstin tehokas käyttäminen ovat erittäin tärkeitä tehokkaiden kaavojen luomisessa, dynaamisten analyysien suorittamisessa ja kaavoissa esiintyvien ongelmien vianmäärityksessä.
Tässä osassa määritetään erityyppiset kontekstit: rivikonteksti, kyselykonteksti ja suodatinkonteksti. Siinä selitetään, miten konteksti arvioidaan kaavojen osalta lasketuissa sarakkeissa ja Pivot-taulukoissa.
Tämän artikkelin viimeisessä osassa on linkkejä yksityiskohtaisiin esimerkkeihin, jotka havainnollistavat, miten kaavojen tulokset muuttuvat kontekstin mukaan.
Kontekstin ymmärtäminen
Pivot-taulukossa käytetyt suodattimet, taulukoiden väliset suhteet ja kaavoissa käytetyt suodattimet voivat vaikuttaa PowerPivotin kaavoihin. Konteksti mahdollistaa dynaamisen analyysin suorittamisen. Kontekstin ymmärtäminen on tärkeää kaavojen muodostamisessa ja vianmäärityksessä.
Konteksteja on erilaisia: rivikonteksti, kyselykonteksti ja suodatinkonteksti.
Rivikontekstia voidaan ajatella "nykyiseksi riviksi". Jos olet luonut lasketun sarakkeen, rivikonteksti koostuu kunkin yksittäisen rivin arvoista ja nykyiseen riviin liittyvien sarakkeiden arvoista. Jotkin funktiot (EARLIER ja EARLIEST) saavat arvon nykyiseltä riviltä ja käyttävät sitten kyseistä arvoa suorittaessaan toimintoa koko taulukossa.
Kyselykontekstilla tarkoitetaan tietojen alijoukkoa, joka luodaan implisiittisesti kullekin pivot-taulukon solulle rivi- ja sarakeotsikoiden mukaan.
Suodatinkonteksti on kussakin sarakkeessa sallittu arvojoukko, joka perustuu riville lisättyihin suodatinrajoituksiin tai jotka kaavan suodatinlausekkeet ovat määrittäneet.
Sivun alkuun
Rivikonteksti
Jos luot kaavan laskettuun sarakkeeseen, kyseisen kaavan rivikonteksti sisältää nykyisen rivin kaikkien sarakkeiden arvot. Jos taulukko liittyy toiseen taulukkoon, sisältö sisältää myös kaikki kyseisen taulukon arvot, jotka liittyvät nykyiseen riviin.
Oletetaan esimerkiksi, että luot lasketun sarakkeen =[Rahti] + [Vero], joka laskee yhteen saman taulukon kaksi saraketta. Tämä kaava toimii samoin kuin Excel-taulukon kaavat, jotka viittaavat automaattisesti saman rivin arvoihin. Huomaa, että taulukot ovat erilaisia kuin alueet: et voi viitata nykyistä riviä edeltävän rivin arvoon käyttämällä aluemerkintätapaa etkä viitata mihinkään satunnaiseen arvoon taulukossa tai solussa. Taulukoita ja sarakkeita on aina käsiteltävä.
Rivikonteksti seuraa automaattisesti taulukoiden välisiä yhteyksiä ja määrittää, mitkä liittyvien taulukoiden rivit liittyvät nykyiseen riviin.
Esimerkiksi seuraavassa kaavassa käytetään RELATED-funktiota veron hakemiseen liittyvästä taulukosta sen alueen perusteella, johon tilaus toimitettiin. Verosumma määritetään käyttämällä nykyisen taulukon alueen arvoa, etsimällä alue liittyvästä taulukosta ja hakemalla sitten kyseisen alueen veroprosentti liittyvästä taulukosta.
= [Rahti] + RELATED('Alue'[Veroprosentti])
Tämä kaava noutaa vain nykyisen alueen veroprosentin Alue-taulukosta. Sinun ei tarvitse tietää tai määrittää taulukot yhdistävää avainta.
Usean rivin konteksti
Lisäksi DAX sisältää funktioita, jotka iteroivat laskutoimituksia taulukosta. Näillä funktioilla voi olla useita nykyisiä rivejä ja rivikonteksteja. Ohjelmointitermein voit luoda kaavoja, jotka toistuvat sisä- ja ulkosilmukan kautta.
Oletetaan esimerkiksi, että työkirjassa on Tuotteet-taulukko ja Myynti-taulukko . Kannattaa ehkä käydä läpi koko myyntitaulukko, joka on täynnä useita tuotteita koskevia tapahtumia, ja etsiä kunkin tuotteen suurin tilausmäärä yhdestä tapahtumasta.
Excelissä tämä laskutoimitus edellyttää joukon väliyhteenvetoja, jotka on muodostettava uudelleen, jos tiedot muuttuvat. Jos olet Excelin tehokäyttäjä, osaat ehkä luoda matriisikaavoja, jotka hoitavat työn. Relaatiotietokantaan voit myös kirjoittaa sisäkkäisiä alivalintoja.
DAX-kielen avulla voit kuitenkin muodostaa yhden kaavan, joka palauttaa oikean arvon, ja tulokset päivitetään automaattisesti aina, kun lisäät tietoja taulukoihin.
=MAXX(SUODATA(Myynti,[Tuoteavain]=AIKAISEMPI([Tuoteavain])),Myynti[Tilausmäärä])
Kaavan yksityiskohtaiset ohjeet ovat EARLIER-funktiossa.
Lyhyesti sanottuna EARLIER-funktio tallentaa nykyistä toimintoa edeltäneen toiminnon rivikontekstin. Funktio tallentaa muistiin aina kaksi kontekstijoukkoa: yksi kontekstijoukko edustaa kaavan sisemmän silmukan nykyistä riviä ja toinen esittää kaavan ulomman silmukan nykyistä riviä. DAX syöttää arvot automaattisesti kahden silmukan väliin, jotta voit luoda monimutkaisia koosteita.
Sivun alkuun
Kyselykonteksti
Kyselykontekstilla tarkoitetaan tietojen alijoukkoa, joka noudetaan implisiittisesti kaavaa varten. Kun pudotat mittayksikön tai muun arvon kentän Pivot-taulukon soluun, Power Pivot -moduuli määrittää kontekstin tutkimalla rivi- ja sarakeotsikot, osittajat ja raporttisuodattimet. Tämän jälkeen Power Pivot tekee tarvittavat laskutoimitukset Pivot-taulukon jokaisen solun täyttämiseksi. Haettava tietojoukko on kunkin solun kyselykonteksti.
Konteksti voi muuttua sen mukaan, mihin sijoitat kaavan, mutta myös sen mukaan, käytätkö kaavaa paljon ryhmittelyjä ja suodattimia sisältävässä Pivot-taulukossa vai lasketussa sarakkeessa, jossa ei ole suodattimia ja jossa on mahdollisimman vähän kontekstia.
Oletetaan, että luot seuraavan yksinkertaisen kaavan, joka laskee yhteen Myynti-taulukonTuotto-sarakkeen arvot:
=SUMMA('Myynti'[Voitto])
Jos käytät tätä kaavaa myyntitaulukon lasketussa sarakkeessa, kaavan tulokset ovat samat koko taulukossa, koska kaavan kyselykonteksti on aina myyntitaulukon koko tietojoukko. Tuloksilla näkyy tuotto kaikille alueille, kaikille tuotteille, kaikille vuosille ja niin edelleen.
Yleensä et kuitenkaan halua nähdä samaa tulosta satoja kertoja, vaan haluat sen sijaan saada tietyn vuoden, tietyn maan tai alueen, tietyn tuotteen tai näiden yhdistelmän voiton ja saada sitten kokonaissumman.
Pivot-taulukossa on helppo muuttaa kontekstia lisäämällä tai poistamalla sarake- ja riviotsikoita ja lisäämällä tai poistamalla osittajia. Voit luoda edellä olevan kaltaisen kaavan mittayksikössä ja pudottaa sen sitten pivot-taulukkoon. Aina kun lisäät sarake- tai riviotsikoita Pivot-taulukkoon, muutat kyselykontekstia, jossa mittayksikkö arvioidaan. Osittamis- ja suodatustoiminnot vaikuttavat myös kontekstiin. Näin ollen sama Pivot-taulukossa käytetty kaava arvioidaan eri kyselykontekstissa kullekin solulle.
Sivun alkuun
Suodatinkonteksti
Suodatinkonteksti lisätään, kun määrität suodatinrajoituksia sarakkeen tai taulukon sallitulle arvojoukolle käyttämällä kaavan argumentteja. Suodatinkontekstia käytetään muiden kontekstien, kuten rivikontekstin tai kyselykontekstin, lisäksi.
Esimerkiksi pivot-taulukko laskee jokaisen solun arvot rivi- ja sarakeotsikoiden perusteella edellisessä kyselykontekstia käsitelleessä osiossa kuvatulla tavalla. Pivot-taulukkoon lisättävissä mitoissa tai lasketuissa sarakkeissa voit kuitenkin määrittää suodatinlausekkeita, joilla hallitaan kaavan käyttämiä arvoja. Voit myös poistaa valikoivasti tiettyjen sarakkeiden suodattimet.
Lisätietoja suodattimien luomisesta kaavoihin on kohdassa Suodatinfunktiot.
Esimerkki suodattimien tyhjentämisestä loppusummien luomiseksi on ALL-funktiossa.
Esimerkkejä suodattimien valikoivasta poistamisesta ja käyttämisestä kaavoissa on ALLEXCEPT-funktiossa.
Siksi sinun on tarkistettava Pivot-taulukossa käytettävien mittojen tai kaavojen määritykset, jotta olet tietoinen suodatinkontekstista tulkitessasi kaavojen tuloksia.
Sivun alkuun
Kaavojen kontekstin määrittäminen
Kun luot kaavan, Power Pivot for Excel tarkistaa ensin yleisen syntaksin ja tarkistaa sitten lisäämiesi sarakkeiden ja taulukoiden nimet mahdollisista sarakkeista ja taulukoista nykyisessä kontekstissa. Jos Power Pivot ei löydä kaavan määrittämiä sarakkeita ja taulukoita, näyttöön tulee virhesanoma.
Konteksti määritetään edeltävissä osissa kuvatulla tavalla käyttämällä työkirjassa käytettävissä olevia taulukoita, taulukoiden välisiä yhteyksiä ja mahdollisesti käytettyjä suodattimia.
Jos olet esimerkiksi juuri tuonut tietoja uuteen taulukkoon etkä ole käyttänyt suodattimia, taulukon koko sarakejoukko on osa nykyistä kontekstia. Jos sinulla on useita taulukoita, jotka on linkitetty toisiinsa, ja käytät Pivot-taulukkoa, joka on suodatettu lisäämällä sarakeotsikoita ja käyttämällä osittajia, konteksti sisältää liittyvät taulukot ja kaikki tietojen suodattimet.
Konteksti on tehokas käsite, joka voi myös vaikeuttaa kaavojen vianmääritystä. Suosittelemme, että aloitat yksinkertaisilla kaavoilla ja suhteilla, jotta näet, miten konteksti toimii, ja aloitat sitten kokeilun yksinkertaisilla kaavoilla Pivot-taulukoissa. Seuraavassa osassa on myös esimerkkejä siitä, miten kaavat palauttavat tuloksia dynaamisesti erityyppisten kontekstien avulla.
Esimerkkejä kaavojen kontekstista
- RELATED-funktio laajentaa nykyisen rivin kontekstin sisällyttämällä siihen liittyvän sarakkeen arvot. Näin voit suorittaa hakuja. Tämän aiheen esimerkissä havainnollistetaan suodatuksen ja rivikontekstin vuorovaikutusta.
- SUODATA-funktion avulla voit määrittää nykyiseen kontekstiin sisällytettävät rivit. Tämän aiheen esimerkeissä kuvataan myös, miten suodattimia voidaan upottaa muihin koosteita suorittaviin funktioihin.
- ALL-funktio määrittää kontekstin kaavassa. Sen avulla voit ohittaa kyselykontekstin tuloksena käytetyt suodattimet.
- ALLEXCEPT-funktiolla voit poistaa kaikki suodattimet yhtä määrittämääsi suodattimia lukuun ottamatta. Molemmat aiheet sisältävät esimerkkejä, joissa kerrotaan kaavojen muodostamisesta ja monimutkaisten kontekstien ymmärtämisestä.
- EARLIER- ja EARLIEST-funktioiden avulla voit selata taulukoita suorittamalla laskutoimituksia ja viitata samalla sisemmän silmukan arvoon. Jos rekursion käsite sekä sisä- ja ulkosilmukat ovat sinulle tuttuja, arvostat EARLIER- ja EARLIES-funktioiden tehoa. Jos nämä käsitteet eivät ole sinulle entuudestaan tuttuja, noudata esimerkin vaiheita huolellisesti nähdäksesi, miten sisempiä ja ulompia konteksteja käytetään laskutoimituksissa.
Sivun alkuun
Viite-eheys
Tässä osassa käsitellään lisäkäsitteitä, jotka liittyvät suhteiden kautta yhdistettyjen Power Pivot -taulukoiden puuttuviin arvoihin. Tästä osasta voi olla hyötyä, jos työkirjassasi on useita taulukoita ja monimutkaisia kaavoja ja haluat apua tulosten ymmärtämiseen.
Jos relaatiotietojen käsitteet eivät ole sinulle entuudestaan tuttuja, on suositeltavaa lukea ensin Suhteiden yleiskatsaus -johdanto.
Viite-eheys ja Power Pivot -yhteydet
Power Pivot ei edellytä kahden taulukon välisen viite-eheyden säilyttämistä kelvollisen yhteyden määrittämiseksi. Sen sijaan jokaisen yksi-moneen-yhteyden "yksi"-päähän luodaan tyhjä rivi, jota käytetään käsittelemään kaikki yhdistetyn taulukon rivit, jotka eivät täsmää toisiaan. Se toimii käytännössä SQL-ulkoliitoksena.
Jos ryhmittelet tiedot Pivot-taulukoissa yhteyden yksi-puolen mukaan, suhteen monta-puolen vastineettomat tiedot ryhmitellään yhteen ja sisällytetään kokonaissummiin tyhjän riviotsikon kanssa. Tyhjä otsikko vastaa suurin piirtein tuntematonta jäsentä.
Tuntemattoman jäsenen ymmärtäminen
Tuntematon jäsen on sinulle todennäköisesti tuttu, jos olet käyttänyt moniulotteisia tietokantajärjestelmiä, kuten SQL Server Analysis Services. Jos termi on sinulle uusi, seuraavassa esimerkissä kerrotaan, mikä tuntematon jäsen on ja miten se vaikuttaa laskutoimituksiin.
Oletetaan, että olet luomassa laskutoimitusta, joka laskee yhteen kunkin myymälän kuukausittaisen myynnin, mutta Myynti-taulukon sarakkeesta puuttuu myymälän nimen arvo. Kun otetaan huomioon, että Store- ja Sales-taulukot on yhdistetty myymälän nimellä, mitä kaavassa voisi odottaa tapahtuvan? Miten Pivot-taulukon tulisi ryhmitellä tai näyttää myyntiluvut, jotka eivät liity olemassa olevaan myymälään?
Tämä ongelma on yleinen tietovarastoissa, joissa faktatietoja sisältävät suuret taulukot on yhdistettävä loogisesti dimensiotaulukoihin, jotka sisältävät tietoja myylmistä, alueista ja muista määritteistä, joita käytetään faktojen luokitteluun ja laskemiseen. Ongelman ratkaisemiseksi tuntemattomalle jäsenelle määritetään väliaikaisesti kaikki uudet faktat, jotka eivät liity aiemmin luotuun entiteettiin. Tästä syystä liittymättömät faktat näkyvät ryhmiteltyinä Pivot-taulukossa tyhjän otsikon alle.
Tyhjien arvojen käsittely verrattuna tyhjään riviin
Tyhjät arvot ovat erilaisia kuin tyhjät rivit, jotka lisätään tuntemattoman jäsenen huomioon ottamista varten. Tyhjä arvo on erikoisarvo, jolla voidaan edustaa nollia, tyhjiä merkkijonoja ja muita puuttuvia arvoja. Lisätietoja tyhjästä arvosta ja muista DAX-tietotyypeistä on kohdassa Tietotyypit tietomalleissa.
Sivun alkuun