Tämä osio kuvaa, miten suodattimia luodaan Data Analysis Expressions- eli DAX-kaavoihin. Voit rajata laskennoissa käytettäviä lähdetietojen arvoja luomalla kaavojen sisälle suodattimia. Luo suodatin määrittämällä taulukko kaavan lähtöarvoksi ja määrittämällä sitten suodatinlausekkeen. Lähdetiedot suodatetaan antamallasi lausekkeella siten, että niistä palautuu vain alijoukko. Suodatinta käytetään tietojesi kontekstin mukaan dynaamisesti aina, kun päivität kaavan tulokset.
Tämän artikkelin sisältö
Suodattimen luominen kaavassa käytettyyn taulukkoon
Voit käyttää suodattimia kaavoissa, jotka käyttävät syötteenään taulukkoa. Sen sijaan, että antaisit taulukon nimen, määritä FILTER-funktiolla määritetyn taulukon rivien alijoukko. Tämä alijoukko välitetään toiselle funktiolle muita toimintoja, kuten mukautettujen koosteiden luomista, varten.
Oletetaan esimerkiksi, että sinulla on taulukossa jälleenmyyjien tilaustietoja, ja haluat laskea, kuinka paljon kukin jälleenmyyjä on myynyt. Haluat kuitenkin näyttää vain niiden jälleenmyyjien myyntisumman, jotka ovat myyneet useita arvokkaampia tuotteita. Seuraavassa DAX-mallityökirjaan perustuvassa kaavassa on esimerkki siitä, miten tämä laskutoimitus voidaan luoda suodattimen avulla:
=SUMX(
SUODATA ('ResellerSales_USD', 'ResellerSales_USD'[Määrä] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
-
Kaavan ensimmäinen osa määrittää Power Pivotin koostefunktion, joka hyväksyy argumentiksi taulukon. SUMX laskee taulukon tietojen summan.
-
Kaavan toinen osa, FILTER(table, expression),, ilmaisee SUMX:lle, mitä tietoja käytetään. SUMX edellyttää taulukkoa tai lauseketta, joka tuottaa tulokseksi taulukon. Sen sijaan, että käyttäisit taulukon kaikkia arvoja, käytä tässä FILTER-funktiota määrittämään, mitä rivejä taulukosta käytetään.
Suodatinlausekkeessa on kaksi osaa: ensimmäinen osa nimeää taulukon, jota suodatin koskee. Toinen osa määrittää suodatusehtona käytettävän lausekkeen. Tässä tapauksessa suodatat ne jälleenmyyjät, jotka ovat myyneet yli viisi yksikköä ja joiden hinta on yli 100 $. Operaattori, && on looginen JA-operaattori, joka ilmaisee, että ehdon molempien osien on oltava tosia, jotta rivi kuuluu suodatettuun alijoukkoon.
-
Kaavan kolmas osa ilmaisee SUMX -funktiolle, mitkä arvot lasketaan yhteen. Tässä tapauksessa käytetään vain myyntisummaa.
Huomaa, että taulukon palauttavat funktiot, kuten FILTER, eivät palauta taulukkoa tai rivejä suoraan, vaan ne upotetaan aina toiseen funktioon. Lisätietoja SUODATA-funktiosta ja muista suodatusfunktioista sekä lisää esimerkkejä on kohdassa Suodatinfunktiot (DAX).
Huomautus: Suodatinlausekkeeseen vaikuttaa konteksti, jossa sitä käytetään. Jos esimerkiksi käytät suodatinta mittayksikössä, jota käytetään Pivot-taulukossa tai -kaaviossa, palautettavaan tietojen alijoukkoon voivat vaikuttaa lisäsuodattimet tai osittajat, joita käyttäjä on lisännyt Pivot-taulukkoon. Lisätietoja kontekstista on kohdassa DAX-kaavojen konteksti.
Kaksoiskappaleet poistavat suodattimet
Tiettyjen arvojen suodattamisen lisäksi voit palauttaa yksilöllisten arvojen tietojoukon toisesta taulukosta tai sarakkeesta. Tästä on hyötyä, kun haluat laskea yksilöllisten arvojen määrän sarakkeessa tai käyttää yksilöllisten arvojen luetteloa muissa toiminnoissa. DAX sisältää kaksi funktiota eri arvojen palauttamiseen: DISTINCT-funktio ja VALUES-funktio.
-
DISTINCT-funktio tutkii funktion argumentiksi määrittämäsi yhden sarakkeen ja palauttaa uuden sarakkeen, joka sisältää vain yksilölliset arvot.
-
VALUES-funktio palauttaa yksilöllisten arvojen luettelon lisäksi tuntemattoman jäsenen. Tästä on hyötyä, kun käytät arvoja kahdesta taulukosta, jotka ovat yhteydessä toisiinsa, ja arvo puuttuu toisesta taulukosta mutta sisältyy toiseen. Lisätietoja tuntemattomasta jäsenestä on kohdassa DAX-kaavojen konteksti.
Molemmat funktiot palauttavat koko arvosarakkeen. Sen vuoksi funktioiden avulla saat luettelon arvoista, jotka välitetään toiselle funktiolle. Voit esimerkiksi seuraavan kaavan avulla saada luettelon tietyn jälleenmyyjän myytäjistä käyttämällä yksilöllistä tuoteavainta ja laskea sitten luettelon tuotteet LASKE.ROWS-funktiolla:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Kontekstin vaikutus suodattimiin
Kun lisäät DAX-kaavan Pivot-taulukkoon tai -kaavioon, konteksti voi vaikuttaa kaavan tuloksiin. Jos käsittelet Power Pivot -taulukkoa, kontekstina on nykyinen rivi ja sen arvot. Jos käsittelet Pivot-taulukkoa tai -kaaviota, kontekstina on toiminnoilla, kuten osittamisella tai suodattamalla, määritetty tietojoukko tai tietojen alijoukko. Myös Pivot-taulukon tai -kaavion rakenne vaikuttaa kontekstiin. Jos luot esimerkiksi Pivot-taulukon, joka ryhmittelee myynnin alueen ja vuoden mukaan, vain määritettyjen alueiden ja vuosien tiedot näkyvät Pivot-taulukossa. Tämän vuoksi kaikki Pivot-taulukkoon lisätyt arvot lasketaan sarake- ja riviotsikoiden kontekstissa sekä kaikissa mittayksikkökaavan suodattimissa.
Lisätietoja on kohdassa DAX-kaavojen konteksti.
Suodattimien poistaminen
Kun käsittelet monimutkaisia kaavoja, haluat ehkä tietää tarkasti, mitä suodattimia on käytössä, tai haluat ehkä muokata kaavan suodatusosaa. DAX-kieli sisältää useita funktioita, joiden avulla voit poistaa suodattimia ja määrittää, mitkä sarakkeet säilytetään osana nykyistä suodatuskontekstia. Tässä osiossa on yleiskuvaus siitä, kuinka nämä funktiot vaikuttavat kaavan tuloksiin.
Kaikkien suodattimien ohittaminen ALL-funktiolla
Voit käyttää ALL-funktiota, kun haluat ohittaa kaikki aiemmin lisätyt suodattimet ja palauttaa taulukon kaikki rivit koostetta tai muuta toimintoa suorittavaa funktiota varten. Jos käytät taulukon sijasta saraketta tai sarakkeita ALL-argumenttina, ALL-funktio palauttaa kaikki rivit ja ohittaa mahdolliset kontekstisuodattimet.
Huomautus: Jos tunnet relaatiotietokantoihin liittyvää terminologiaa, voit ajatella, että ALL -funktio luo luonnollisen vasemman ulkoliitoksen kaikista taulukoista.
Oletetaan, että tarkastelet myyntitaulukkoa (Sales) ja tuotetaulukkoa (Products) ja haluat luoda kaavan, joka laskee yhden tuotteen myynnin jaettuna kaikkien tuotteiden myynnillä. Jos kaavaa käytetään mittayksikössä, ota huomioon, että Pivot-taulukon käyttäjä on saattanut suodattaa osittajan avulla näkyviin vain tietyn tuotteen niin, että tuotteen nimi näkyy riveillä. Jotta saisit nimittäjän todellisen arvon suodattimista tai osittajista riippumatta, sinun on siis ohitettava mahdolliset suodattimet käyttämällä ALL-funktiota. Seuraava kaava on esimerkki tilanteesta, jossa ALL-funktion avulla ohitetaan aiempien suodattimien vaikutukset:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
-
Kaavan ensimmäinen osa, SUM (Sales[Amount]), laskee osoittajan.
-
Summaa laskettaessa huomioidaan nykyinen konteksti. Toisin sanoen, jos lisäät kaavan laskettuun sarakkeeseen, käytetään rivikontekstia, ja jos lisäät kaavan mittayksikkönä Pivot-taulukkoon, käytetään Pivot-taulukon suodattimia (suodatuskontekstia).
-
Kaavan toinen osa laskee nimittäjän. ALL-funktio ohittaa kaikki suodattimet, joita on mahdollisesti käytetty Products-taulukossa.
Lisätietoja ja yksityiskohtaisia esimerkkejä on kohdassa ALL-funktio.
Tiettyjen suodattimien ohittaminen ALLEXCEPT-funktiolla
Myös ALLEXCEPT-funktio ohittaa aiemmin lisätyt suodattimet, mutta voit määrittää, että osa niistä säilytetään. ALLEXCEPT-funktion argumenteiksi määrittämäsi sarakkeet määrittävät, mitkä sarakkeet suodatetaan edelleen. Jos haluat ohittaa suodattimet useimmissa mutta et kaikissa sarakkeissa, ALLEXCEPT-funktio on käytännöllisempi kuin ALL-funktio. ALLEXCEPT-funktiosta on erityisesti hyötyä silloin, kun luot useita suodatettavissa olevia sarakkeita sisältäviä pivot-taulukoita ja haluat määrittää kaavassa käytettävät arvot. Lisätietoja ja yksityiskohtainen esimerkki ALLEXCEPT-funktion käytöstä Pivot-taulukossa on kohdassa ALLEXCEPT-funktio.
