Kontekstin avulla voit tehdä dynaamista analyysia, jossa kaavan tulokset voivat muuttua vastaamaan nykyisen rivin tai solun valintaa ja myös siihen liittyviä tietoja. Kontekstin ymmärtäminen ja asiayhteyden tehokas käyttö ovat erittäin tärkeitä tehokkaiden kaavojen, dynaamisten analyysien ja kaavoissa ongelmien vianmäärityksessä.
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ä yksityiskohtaisisiin esimerkkeihin, joissa kuvataan, miten kaavojen tulokset muuttuvat kontekstin mukaan.
Kontekstin ymmärtäminen
Pivot Power Pivot taulukon suodattimet, taulukoiden väliset yhteydet ja kaavoissa käytetyt suodattimet voivat vaikuttaa kaavan kaavoihin. Konteksti mahdollistaa dynaamisen analyysin. Kontekstin ymmärtäminen on tärkeää kaavojen rakentamiseen ja vianmääritykseen.
Kontekstia on erilaisia: rivikonteksti, kyselyn konteksti ja suodatinkonteksti.
Rivikontekstia voidaan pitää "nykyisenä rivinä". Jos olet luonut lasketun sarakkeen, rivikonteksti koostuu kunkin yksittäisen rivin arvoista ja nykyiseen riviin liittyvistä sarakkeiden arvoista. Myös jotkin funktiot (EARLIER ja EARLIEST) saavat arvon nykyiseltä riviltä ja käyttävät arvoa koko taulukon toimintoja suoritettaessa.
Kyselykonteksti viittaa tietojen alijoukkoon, joka on implisiittisesti luotu pivot-taulukon kullekin solulle rivi- ja sarakeotsikoiden mukaan.
Suodatinkonteksti on kussakin sarakkeessa sallittu arvojoukko, joka perustuu riville määritettyihin suodatinrajoituksiin tai jotka on määritetty kaavan suodatinlausekkeilla.
Rivikonteksti
Jos luot kaavan lasketussa sarakkeessa, kaavan rivikonteksti sisältää nykyisen rivin kaikkien sarakkeiden arvot. Jos taulukko on liittyvä toiseen taulukkoon, sisältö sisältää myös kaikki tämän toisen taulukon arvot, jotka liittyvät nykyiseen riviin.
Oletetaan esimerkiksi, että luot lasketun sarakkeen , =[Freight] + [Tax],
, joka yhdistää kaksi saraketta samasta taulukosta. Tämä kaava toimii samalla tavalla kuin taulukon Excel, joka viittaa automaattisesti saman rivin arvoihin. Huomaa, että taulukot ovat erilaisia kuin alueet: et voi viitata arvoa nykyisen rivin edessä olevalta riviltä käyttämällä alue-merkintää, eikä taulukkoon tai soluun voi viitata satunnaisiin arvoihin. Sinun on aina käytettävä taulukoita ja sarakkeita.
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 hakemiseen liittyvästä taulukosta sen alueen perusteella, jolle tilaus toimitettiin. Veroarvo määritetään käyttämällä nykyisen taulukon alueen arvoa, etsimällä alueen liittyvästä taulukosta ja saamalla sitten alueen veroprosenttia liittyvästä taulukosta.
= [Freight] + RELATED('Region'[TaxRate])
Tämä kaava saa nykyisen alueen veroprosenttia Alue-taulukosta. Sinun ei tarvitse tietää tai määrittää taulukoiden välilleavainta.
Usean rivin konteksti
Lisäksi DAX sisältää funktioita, jotka iteraattivat laskutoimitukset taulukon päälle. Näillä funktioilla voi olla useita nykyisiä rivejä ja rivikontekstit. Ohjelmointitermien avulla voit luoda kaavoja, jotka toistuvat sisemmän ja ulomman silmukan yli.
Oletetaan esimerkiksi, että työkirjassa on Tuotteet-taulukko ja Myynti-taulukko. Haluat ehkä käydä läpi koko myyntitaulukon, joka on täynnä tapahtumia, joissa on useita tuotteita, ja etsiä kunkin tuotteen suurimman tilatun määrän missä tahansa tapahtumassa.
Tässä Excel laskutoimituksessa tarvitaan sarja väliyhteenvedoita, jotka on rakennettava uudelleen, jos tiedot muuttuvat. Jos olet tehokäyttäjä Excel, voit ehkä luoda matriisikaavoja, jotka tekisivat työn. Relaatiotietokannassa voit myös kirjoittaa sisäkkäisiä alivalintoja.
DAX-kaavojen avulla voit kuitenkin luoda yhden kaavan, joka palauttaa oikean arvon, ja tulokset päivittyvät automaattisesti aina, kun lisäät tietoja taulukoihin.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Tämän kaavan yksityiskohtainen vaiheittaiset ohjeet ovat AIEMMASSA KOHDASSA.
Lyhyesti sanottuna EARLIER-funktio tallentaa nykyistä toimintoa edeltävän toiminnon rivikontekstin. Funktio tallentaa aina muistiin kaksi kontekstijoukkoa: yksi kontekstijoukko edustaa kaavan sisemmän silmukan nykyistä riviä ja toinen kontekstijoukko edustaa kaavan uloimman silmukan nykyistä riviä. DAX syöttää arvot automaattisesti kahden silmukan väliin, jotta voit luoda monimutkaisia koosteja.
Kyselykonteksti
Kyselykonteksti viittaa tietojen alijoukkoon, joka noudetaan implisiittisesti kaavaa varten. Kun pudotat mittayksikön tai muun arvokentän Pivot-taulukon soluun, Power Pivot-ohjelma tarkistaa rivi- ja sarakeotsikot, osittajat ja raporttisuodattimet kontekstin määrittämiseksi. Tämän Power Pivot tekee tarvittavat laskutoimitukset Pivot-taulukon kunkin solun täyttäminen. Noudettu tietojoukko on kunkin solun kyselykonteksti.
Konteksti voi muuttua kaavan paikan mukaan, mutta myös kaavan tulokset muuttuvat sen mukaan, käytätkö kaavaa Pivot-taulukossa, jossa on useita ryhmittelyjä ja suodattimia, vai lasketussa sarakkeessa ilman suodattimia ja vähäistä kontekstia.
Oletetaan esimerkiksi, että luot tämän yksinkertaisen kaavan, joka summaa Myynti-taulukon Tuotto-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 Myynti-taulukon koko tietojoukko. Tuloksillasi on tuotto kaikille alueille, kaikille tuotteille, kaikkina vuosina ja niin edelleen.
Yleensä et kuitenkaan halua nähdä samaa tulosta satoja kertoja, mutta haluat sen sijaan 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 yllä mainitun kaltaista kaavaa mittayksikössä ja pudottaa sen sitten Pivot-taulukkoon. Kun lisäät sarake- tai riviotsikot Pivot-taulukkoon, muutat kyselyn kontekstia, jossa mittayksikkö arvioidaan. Myös viivointi- ja suodatustoiminnot vaikuttavat kontekstiin. Tästä syystä sama kaava, jota käytetään Pivot-taulukossa, arvioidaan eri kyselykontekstissa kullekin solulle.
Suodatinkonteksti
Suodatinkonteksti lisätään, kun määrität suodatinrajoitukset sarakkeessa tai taulukossa sallitulle arvosarjalle käyttämällä kaavan argumentteja. Suodatinkontekstia käytetään muiden kontekstien, kuten rivikontekstin tai kyselykontekstin, päällä.
Esimerkiksi Pivot-taulukko laskee kunkin solun arvot rivi- ja sarakeotsikoiden perusteella, kuten edellä olevassa kyselykontekstia osassa on kuvattu. Pivot-taulukkoon lisättävissä toimenpiteissä tai lasketuissa sarakkeissa voit kuitenkin määrittää suodatinlausekkeita, jotka määrittävät kaavan käyttämät arvot. Voit myös poistaa tietyn sarakkeen suodattimet valikoidusti.
Lisätietoja kaavojen suodattimien luo käytöstä on suodatinfunktioissa.
Esimerkki siitä, miten suodattimet voidaan tyhjentää loppusumman luontia varten, on KOHDASSA KAIKKI.
Esimerkkejä suodattimien valikoidusta tyhjentäminen ja käyttäminen kaavoissa on ALLEXCEPT-funktiossa.
Siksi sinun on tarkistettava Pivot-taulukossa käytössä olevan mitat tai kaavat, jotta tiedät suodatinkontekstin kaavojen tuloksia tulkittaessa.
Kontekstin määrittäminen kaavoissa
Kun luot Power Pivot, Excel tarkistaa ensin yleisen syntaksin ja tarkistaa sitten, mitä sarakkeita ja taulukoita käytät nykyisessä kontekstissa. Jos Power Pivot ei löydä kaavan määrittäjä sarakkeita ja taulukoita, saat virheilmoituksen.
Konteksti määräytyy edellisissä osissa kuvatulla tavalla käyttämällä työkirjan käytettävissä olevia taulukoita, taulukoiden välisiä yhteyksiä ja käytettyjä suodattimia.
Jos olet esimerkiksi juuri tuonut joitakin tietoja uuteen taulukkoon mutta et ole lisännyt suodattimia, taulukon koko sarakejoukko on osa nykyistä kontekstia. Jos sinulla on useita taulukoita, jotka on linkitetty yhteyksien mukaan ja työskentelet Pivot-taulukossa, joka on suodatettu lisäämällä sarakeotsikot 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 aloita sitten yksinkertaisten kaavojen kokeileminen Pivot-taulukoissa. Seuraavassa osassa on myös esimerkkejä siitä, miten kaavat käyttävät erityyppisiä kontekstityyppejä tulosten dynaamiseen palauttamiseen.
Esimerkkejä kontekstista kaavoissa
-
RELATED-funktio laajentaa nykyisen rivin kontekstia niin, että se sisällyttää arvot liittyvään sarakkeeseen. Näin voit suorittaa hakuja. Tässä aiheessa olevassa esimerkissä kuvataan suodatuksen ja rivikontekstin vuorovaikutusta.
-
SUODATA-funktion avulla voit määrittää rivit, jotka sisällytetään nykyiseen kontekstiin. Tämän ohjeaiheen esimerkeissä kuvataan myös, miten voit upottaa suodattimia muihin koostefunktioita suorittavaan toimintoon.
-
ALL-funktio määrittää kontekstin kaavassa. Sen avulla voit ohittaa kyselykontekstin tuloksena käytettävät suodattimet.
-
ALLEXCEPT-funktion avulla voit poistaa kaikki suodattimet paitsi sen, jonka määrität. Kummassakin aiheessa on esimerkkejä, joissa kuvataan kaavojen luominen ja monimutkaisten kontekstien ymmärtäminen.
-
EARLIER- ja EARLIEST-funktioiden avulla voit selata taulukoita suorittamalla laskutoimituksia ja viitata arvoon sisemmällä silmukalla. Jos tunnet recursion käsitteen ja sisemmät ja ulommat silmukat, arvostat AIEMPIEN VERSIOIDEN ja AIKAISIMMAN funktioiden tehon. Jos et ole ennen käyttänyt näitä käsitteitä, noudata esimerkin vaiheita huolellisesti, jotta näet, miten sisempää ja ulompaa kontekstia käytetään laskelmissa.
Viite-eheys
Tässä osassa käsitellään edistyneitä käsitteitä, jotka liittyvät yhteyksillä yhdistettyjen taulukoiden puuttuvien Power Pivot arvoihin. Tästä osiosta voi olla hyötyä, jos työkirjoissa on useita taulukoita ja monimutkaisia kaavoja ja haluat apua tulosten ymmärtämiseen.
Jos et ole ennen tutustunut relaatiotietojen käsitteisiin, lue ensin johdantoaihe Yhteydet – yleiskatsaus.
Viite-eheys ja Power Pivot yhteydet
Power Pivot ei edellytä, että viite-eheys on pakotettu kahden taulukon välillä kelvollisen yhteyden määrittämiseksi. Sen sijaan yksi-moneen-yhteyden "yksi"-loppuun luodaan tyhjä rivi, jota käytetään kaikkien ei-toisiaan vastaavien rivien käsittelemisen yhteydessä olevasta taulukosta. Se toimii SQL ulkoliitoksena.
Jos ryhmittelet tietoja yhteyden toisen puolen mukaan Pivot-taulukoissa, yhteyden monta-puolen vastaamattomat tiedot ryhmitellään yhteen ja sisällytetään summaan, jossa on tyhjä riviotsikko. Tyhjä otsikko vastaa suurin piirtein "tuntematonta jäsentä".
Tuntemattoman jäsenen ymmärtäminen
Tuntemattoman jäsenen käsite on todennäköisesti tuttu sinulle, jos olet työskennellyt moniulotteisten tietokantajärjestelmien, kuten SQL Server Analysis Servicesin, kanssa. Jos termi on sinulle uusi, seuraavassa esimerkissä kerrotaan, mikä tuntematon jäsen on ja miten se vaikuttaa laskutoimitmiin.
Oletetaan, että luot laskutoimituksen, joka sisältää kunkin myymälän kuukausimyynnin summan, mutta Myynti-taulukon sarakkeesta puuttuu kaupan nimen arvo. Kun otetaan huomioon, että Myymälä-ja Myynti-taulukot on yhdistetty säilön nimellä, mitä kaavassa pitäisi tapahtua? Miten Pivot-taulukko ryhmitellä tai näyttää myyntiluvut, jotka eivät liity aiemmin luotuun kauppaan?
Tämä ongelma on yleinen tietovarastoissa, jossa suurten faktatietojen taulukoiden on liityttävä loogisesti dimensiotaulukoihin, jotka sisältävät tietoja kaupoista, alueista ja muista määritteitä, joita käytetään faktatietojen luokitteluun ja laskemiseen. Ongelman ratkaisemiseksi tuntemattomalle jäsenille määritetään tilapäisesti uudet tiedot, jotka eivät liity aiemmin luotuun entiteettiin. Tästä syystä toisiinsa liittymättömät faktat näkyvät ryhmiteltynä Pivot-taulukossa tyhjän otsikon alle.
Tyhjien arvojen ja tyhjän rivin käsittely
Tyhjät arvot ovat erilaisia kuin tyhjät rivit, jotka lisätään tuntemattoman jäsenen mukaan. Tyhjä arvo on erikoisarvo, jota käytetään edustamaan tyhjäarvoja, tyhjiä merkkijonoja ja muita puuttuvia arvoja. Lisätietoja tyhjästä arvosta sekä muista DAX-tietotyypeistä on kohdassa Tietotyypit tietomalleissa.
