Kontekstin avulla voit suorittaa dynaamisia analyyseja, joissa kaavan tulokset voivat muuttua vastaamaan nykyistä rivin tai solun valintaa ja myös siihen liittyviä tietoja. Kontekstin ymmärtäminen ja kontekstin tehokas käyttö ovat erittäin tärkeitä tehokkaiden kaavojen, dynaamisten analyysien ja kaavojen ongelmien vianmäärityksen kannalta.
Tässä osassa määritetään erityyppiset kontekstit: rivikonteksti, kyselykonteksti ja suodatinkonteksti. Siinä selitetään, miten laskettujen sarakkeiden ja Pivot-taulukoiden kaavojen konteksti arvioidaan.
Tämän artikkelin viimeisessä osassa on linkkejä yksityiskohtaisiin esimerkkeihin, jotka havainnollistavat, miten kaavojen tulokset muuttuvat kontekstin mukaan.
Tietoja kontekstista
Power Pivot kaavoihin voivat vaikuttaa Pivot-taulukossa käytetyt suodattimet, taulukoiden väliset yhteydet ja kaavoissa käytetyt suodattimet. Konteksti mahdollistaa dynaamisen analyysin suorittamisen. Kontekstin ymmärtäminen on tärkeää kaavojen luomisessa ja vianmäärityksessä.
Kontekstia on monenlaisia: rivikonteksti, kyselykonteksti ja suodatinkonteksti.
Rivikontekstia voidaan pitää "nykyisenä rivinä". Jos olet luonut lasketun sarakkeen, rivikonteksti koostuu kunkin yksittäisen rivin arvoista ja nykyiseen riviin liittyvien sarakkeiden arvoista. On myös joitakin funktioita (AIEMPI ja AIKAISIN), jotka saavat arvon nykyiseltä riviltä ja käyttävät sitten tätä arvoa suorittaessaan toimintoa koko taulukossa.
Kyselykonteksti viittaa tietojen alijoukkoon, joka luodaan implisiittisesti kullekin Pivot-taulukon solulle rivi- ja sarakeotsikoiden mukaan.
Suodatinkonteksti on jokaisessa sarakkeessa sallittujen arvojen joukko, joka perustuu suodatinrajoituksiin, joita on käytetty rivillä tai jotka on määritetty kaavan suodatuslausekkeilla.
Rivikonteksti
Jos luot kaavan lasketussa sarakkeessa, kaavan rivikonteksti sisältää nykyisen rivin kaikkien sarakkeiden arvot. Jos taulukko on liitetty toiseen taulukkoon, sisältö sisältää myös kaikki kyseisen toisen taulukon arvot, jotka liittyvät nykyiseen riviin.
Oletetaan esimerkiksi, että luot lasketun sarakkeen =[Rahti] + [Vero], joka laskee yhteen kaksi saraketta samasta taulukosta. Tämä kaava toimii samalla tavalla kuin Excel-taulukon kaavat, jotka viittaavat automaattisesti saman rivin arvoihin. Huomaa, että taulukot eroavat alueista: et voi viitata arvoon riviltä ennen nykyistä riviä käyttämällä aluemerkintää, etkä voi viitata mihinkään satunnaiseen yksittäiseen arvoon taulukossa tai solussa. Taulukoita ja sarakkeita on aina käytettävä.
Rivikonteksti seuraa automaattisesti taulukoiden välisiä yhteyksiä ja määrittää, mitkä toisiinsa liittyvien taulukoiden rivit liittyvät nykyiseen riviin.
Esimerkiksi seuraavassa kaavassa käytetään RELATED-funktiota veroarvon noutamiseen liittyvästä taulukosta sen alueen perusteella, johon tilaus toimitettiin. Veroarvo määritetään käyttämällä nykyisen taulukon aluearvoa, etsimällä alue liittyvästä taulukosta ja hakemalla sitten kyseisen alueen verokanta liittyvästä taulukosta.
= [Freight] + RELATED('Region'[TaxRate])
Tämä kaava hakee nykyisen alueen verokannan Alue-taulukosta. Sinun ei tarvitse tietää tai määrittää avainta, joka yhdistää taulukot.
Usean rivin konteksti
Lisäksi DAX sisältää funktioita, jotka iteroivat laskutoimituksia taulukon päällä. Näillä funktioilla voi olla useita nykyisiä rivejä ja nykyisiä rivikontekstia. Ohjelmointitermiten avulla voit luoda kaavoja, jotka toistuvat sisemmän ja ulomman silmukan kautta.
Oletetaan esimerkiksi, että työkirjassa on Tuotteet-taulukko ja Myynti-taulukko . Haluat ehkä käydä läpi koko myyntitaulukon, joka on täynnä useita tuotteita sisältäviä tapahtumia, ja etsiä kullekin tuotteelle tilatun suurimman määrän missä tahansa yhdessä kaupassa.
Excelissä tämä laskutoimitus edellyttää väliyhteenvetojen sarjaa, joka pitäisi rakentaa uudelleen, jos tiedot muuttuisivat. Jos olet Excelin tehokäyttäjä, voit ehkä luoda matriisikaavoja, jotka tekevät työn. Vaihtoehtoisesti voit kirjoittaa sisäkkäisiä alivalintoja relaatiotietokannassa.
DAX-funktiolla voit kuitenkin luoda yksittäisen kaavan, joka palauttaa oikean arvon, ja tulokset päivitetään automaattisesti aina, kun lisäät tietoja taulukoihin.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Lisätietoja tästä kaavasta on AIEMMASSA-funktiossa.
Lyhyesti sanottuna AIEMPI-funktio tallentaa rivikontekstin nykyistä toimintoa edeltäneestä toiminnosta. Funktio tallentaa aina muistiin kaksi kontekstijoukkoa: yksi kontekstijoukko edustaa kaavan sisemmän silmukan nykyistä riviä ja toinen kontekstijoukko edustaa kaavan ulkosilmukan nykyistä riviä. DAX syöttää automaattisesti kahden silmukan väliset arvot, jotta voit luoda monimutkaisia koosteita.
Kyselyn konteksti
Kyselykonteksti viittaa tietojen alijoukkoon, joka on implisiittisesti noudettu kaavaa varten. Kun pudotat mittayksikön tai muun arvokentän Pivot-taulukon soluun, Power Pivot-moduuli tutkii rivien ja sarakkeiden otsikot, osittajat ja raporttisuodattimet kontekstin määrittämiseksi. Power Pivot tekee sitten tarvittavat laskutoimitukset pivot-taulukon kunkin solun täyttämiseksi. Noudettava tietojoukko on kunkin solun kyselykonteksti.
Koska konteksti voi muuttua sen mukaan, mihin kaava sijoitetaan, kaavan tulokset muuttuvat sen mukaan, käytätkö kaavaa Pivot-taulukossa, jossa on useita ryhmityksiä ja suodattimia, vai lasketussa sarakkeessa ilman suodattimia ja minimaalista kontekstia.
Oletetaan esimerkiksi, että luot tämän yksinkertaisen kaavan, joka laskee yhteen Myynti-taulukonTuotto-sarakkeen arvot:
=SUMMA('Myynti'[Tuotto])
Jos käytät tätä kaavaa Myynti-taulukon lasketussa sarakkeessa, kaavan tulokset ovat samat koko taulukossa, koska kaavan kyselykonteksti on aina Koko Myynti-taulukon tietojoukko. Tuloksesi ovat tuottoa kaikille alueille, kaikille tuotteille, kaikille vuosille ja niin edelleen.
Et kuitenkaan yleensä halua nähdä samaa tulosta satoja kertoja, vaan haluat saada tietyn vuoden, tietyn maan tai alueen, tietyn tuotteen tai jonkin näiden yhdistelmän voiton ja saada sitten kokonaissumman.
Pivot-taulukossa kontekstia on helppo muuttaa lisäämällä tai poistamalla sarake- ja riviotsikoita sekä lisäämällä tai poistamalla osittajia. Voit luoda mittayksikön kaavan, kuten edellä, ja pudottaa sen sitten Pivot-taulukkoon. Aina kun lisäät sarake- tai riviotsikoita Pivot-taulukkoon, muutat kyselyn kontekstia, jossa mittayksikkö arvioidaan. Myös leikkaus- ja suodatustoiminnot vaikuttavat kontekstiin. Siksi samaa pivot-taulukossa käytettyä kaavaa arvioidaan eri kyselykontekstissa kullekin solulle.
Suodatinkonteksti
Suodatinkonteksti lisätään, kun määrität suodatinrajoitukset sarakkeessa tai taulukossa sallittavien arvojen joukolle käyttämällä argumentteja kaavassa. Suodatinkontekstia käytetään muiden kontekstien, kuten rivikontekstin tai kyselykontekstin, päällä.
Esimerkiksi Pivot-taulukko laskee kunkin solun arvot rivi- ja sarakeotsikoiden perusteella kyselykontekstia koskevassa edellisessä osassa kuvatulla tavalla. Pivot-taulukkoon lisätyissä mittayksiköissä tai lasketuissa sarakkeissa voit kuitenkin määrittää suodatinlausekkeita, joilla hallitaan kaavan käyttämiä arvoja. Voit myös poistaa valikoidusti suodattimia tietyistä sarakkeista.
Lisätietoja suodattimien luomisesta kaavojen sisällä on Suodatin-funktioissa.
Esimerkki siitä, miten suodattimet voidaan poistaa loppusummien luomista varten, on ALL-funktiossa.
Esimerkkejä suodattimien valikoivasta tyhjentämisestä ja käytöstä kaavoissa on ALLEXCEPT-funktiossa.
Siksi sinun on tarkistettava Pivot-taulukossa käytettyjen mittojen tai kaavojen määritelmä, jotta olet tietoinen suodatinkontekstista kaavojen tuloksia tulkittaessa.
Kontekstin määrittäminen kaavoissa
Kun luot kaavan, Power Pivot Excel tarkistaa ensin yleisen syntaksin ja vertaa sitten antamiesi sarakkeiden ja taulukoiden nimiä nykyisessä kontekstissa mahdollisiin sarakkeisiin ja taulukoihin. Jos Power Pivot ei löydä kaavan määrittämiä sarakkeita ja taulukoita, saat virheilmoituksen.
Konteksti määritetään edellä kuvatulla tavalla käyttämällä työkirjan käytettävissä olevia taulukoita, taulukoiden välisiä yhteyksiä ja mahdollisesti käytettyjä suodattimia.
Jos olet esimerkiksi juuri tuonut joitakin tietoja uuteen taulukkoon etkä ole käyttänyt suodattimia, taulukon koko sarakejoukko on osa nykyistä kontekstia. Jos sinulla on useita taulukoita, jotka on linkitetty yhteyksillä ja työskentelet Pivot-taulukossa, joka on suodatettu lisäämällä sarakeotsikoita ja käyttämällä osittajia, konteksti sisältää liittyvät taulukot ja tietojen suodattimet.
Konteksti on tehokas käsite, joka voi myös vaikeuttaa kaavojen vianmääritystä. Suosittelemme, että aloitat yksinkertaisilla kaavoilla ja yhteyksillä, jotta näet, miten konteksti toimii, ja aloitat sitten yksinkertaisten kaavojen kokeilun Pivot-taulukoissa. Seuraavassa osassa on myös esimerkkejä siitä, miten kaavat käyttävät erilaisia kontekstityyppejä tulosten dynaamiseen palauttamiseen.
Esimerkkejä kontekstista kaavoissa
-
RELATED-funktio laajentaa nykyisen rivin kontekstin sisältämään arvot liittyvään sarakkeeseen. Näin voit suorittaa hakuja. Tämän ohjeaiheen esimerkki havainnollistaa suodatuksen ja rivikontekstin vuorovaikutusta.
-
SUODATA-funktion avulla voit määrittää nykyiseen kontekstiin sisällytettävät rivit. Tämän ohjeaiheen esimerkeissä kuvataan myös, miten voit upottaa suodattimia muihin koostetoimintoja suorittavien funktioiden sisään.
-
KAIKKI-funktio määrittää kontekstin kaavassa. Sen avulla voit ohittaa kyselykontekstin tuloksena käytetyt suodattimet.
-
ALLEXCEPT-funktion avulla voit poistaa kaikki paitsi määrittämäsi suodattimet. Molemmissa aiheissa on esimerkkejä, jotka opastavat kaavojen luomisen ja monimutkaisten kontekstien ymmärtämisessä.
-
AIEMMAT- ja AIKAISIN-funktioiden avulla voit selata taulukoita suorittamalla laskutoimituksia ja viittaamalla sisemmän silmukan arvoon. Jos tunnet rekursion käsitteen sekä sisemmät ja ulkosilmukat, arvostat AIKAISEMPI- ja AIKAISIN-funktioiden tarjoamaa tehoa. Jos et ole ennen käyttänyt näitä käsitteitä, noudata esimerkin vaiheita huolellisesti nähdäksesi, miten sisempiä ja ulompia konteksteja käytetään laskutoimituksissa.
Viite-eheys
Tässä osassa käsitellään joitakin kehittyneitä käsitteitä, jotka liittyvät puuttuviin arvoihin Power Pivot taulukoissa, jotka on yhdistetty yhteyksillä. Tästä osasta voi olla hyötyä, jos sinulla on työkirjoja, joissa on useita taulukoita ja monimutkaisia kaavoja, ja haluat ohjeita tulosten ymmärtämiseen.
Jos et ole ennen tutustunut relaatiotietojen käsitteisiin, suosittelemme, että luet ensin johdantoaiheen Yhteydet yleiskatsaus.
Viite-eheys ja Power Pivot suhteet
Power Pivot ei edellytä viite-eheyden noudattamista kahden taulukon välillä kelvollisen yhteyden määrittämiseksi. Sen sijaan kunkin yksi-moneen-yhteyden "yksi"-päähän luodaan tyhjä rivi, jonka avulla käsitellään kaikki toisiinsa liittyvän taulukon rivit, jotka eivät vastaa toisiaan. Se toimii tehokkaasti SQL-ulkoliitokseksi.
Jos pivot-taulukoissa tiedot ryhmitetään yhteyden toisen puolen mukaan, kaikki yhteyden monta-puolella olevat vertaansa vailla olevat tiedot ryhmitetään yhteen ja sisällytetään summiin, joissa on tyhjä riviotsikko. Tyhjä otsikko vastaa suurin piirtein tuntematonta jäsentä.
Tuntemattoman jäsenen ymmärtäminen
Tuntemattoman jäsenen käsite 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ä luot laskutoimituksen, joka laskee yhteen kunkin säilön kuukausittaisen myynnin, mutta Myynti-taulukon sarakkeesta puuttuu kaupan nimen arvo. Koska Kaupan ja Myynnin taulukot on yhdistetty kaupan nimellä, mitä kaavassa voisi tapahtua? Miten Pivot-taulukkoryhmän pitäisi näyttää tai näyttää myyntiluvut, jotka eivät liity aiemmin luotuun säilöön?
Tämä ongelma on yleinen tietovarastoissa, joissa suurten faktatietojen taulukoiden on oltava loogisesti yhteydessä dimensiotaulukoihin, jotka sisältävät tietoja tallennuksista, alueista ja muista määritteistä, joita käytetään tosiasioiden luokitteluun ja laskemiseen. Ongelman ratkaisemiseksi kaikki uudet tosiseikat, jotka eivät liity olemassa olevaan entiteettiin, määritetään tilapäisesti tuntemattomalle jäseneltä. Siksi toisiinsa liittymättömät faktat näkyvät ryhmiteltynä Pivot-taulukkoon tyhjän otsikon alle.
Tyhjien arvojen ja tyhjän rivin käsittely
Tyhjät arvot poikkeavat tyhjistä riveistä, jotka lisätään tuntemattoman jäsenen mukaan. Tyhjä arvo on erikoisarvo, jota käytetään null-arvojen, tyhjien merkkijonojen ja muiden puuttuvien arvojen esittämiseen. Lisätietoja tyhjästä arvosta ja muista DAX-tietotyypeistä on kohdassa Tietomallien tietotyypit.
