Tuli pitkästä aikaa taas kirjoitettua DAX-kieltä (mm. Power BI:n koodikieli) ja hetki haparoitua konseptien parissa. Tästä innostuneena päätin kirjoittaa loppuun filtterikontekstista kirjoittamani blogitekstin, jonka aloitin muutama vuosi sitten, mutten ikinä tullut sitä julkaisseeksi. Filtterikontekstista ei tietääkseni ole varsinaisesti kauheasti käytännön materiaalia suomen kielellä, joten toivottavasti tästä on apua jollekin.
Hyvät perusteet sille, mitä on filtterikonteksti, saa vaikkapa tästä Russon ja Ferrarin artikkelista. En tässä käy läpi varsinaisesti perusteita. Terminologia on lainattu myös Russolta ja Ferrarilta ja lienee tuttua kaikille heidän blogejaan ja kirjaa lukeneille tai koulutuksiaan käyneille.
Data ja tietomalli
Katsotaan ensin läpi esimerkkidatan yksinkertainen tietomalli, jossa on kaksi taulua: Tuote ja Myynti.
Tuotteita on kolme kappaletta.
Tuotteella 1 on kaksi transaktiota ja muilla tuotteilla yksi transaktio molemmilla.
Tarkastelemme myytyjen tuotteiden rivimääriä, joten [Euroa] ja [Kuukausi] -kolumneista ei tarvitse välittää tässä kohtaa.
Tarkastelemme filtterikontekstia eli sitä, miten DAX laskee lennosta taulukossa olevia measureita. Jokaisella solulla taulussa on potentiaalisesti oma filtterikontekstinsa. Tässä tapauksessa jokaisella rivillä on oma filtterikontekstinsa.
Alla oleva taulukko näyttää läpikäytävät measuret ja kuinka monta riviä kukin measure palauttaa Tuote[Nimi]- ja Tuote[Väri] -kontekstissa. On siis tärkeää huomata, että taulukossa ryhmittely tehdään Tuote-taulun Tuote[Nimi] ja Tuote[Väri] kolumnien perusteella, ei esim Myynti[TuoteId]-kolumnin perusteella. Yhteenlaskettavien rivien määrä on taas Myynti-taulun rivien määrä, joka näkyy kyseisessä kontekstissa.
Esimerkkejä filtterikontekstin muuttamisesta
Countrows =
COUNTROWS(Myynti)
Laskee rivien määrän Myynti-taulussa muokkaamatta filter kontekstia.
Koska jokainen measure on implisiittisesti kiedottu CALCULATE-funtioon, on COUNTROWS(Myynti) = CALCULATE(COUNTROWS(Myynti)).
Tuote_1_ALL =
CALCULATE(
COUNTROWS(Myynti),
ALL(Tuote)
)
ALL-funktio poistaa Tuote-taulun filtterikontektista.
Measure palauttaa kaikki rivit myyntitaulusta (4 riviä). Koska myynti-tauluun ei kohdistu filtterikontekstia, niin jokaisessa solussa lasketaan yhteen kaikki myyntitaulun rivit.
ALL_nimi =
CALCULATE(
COUNTROWS(Myynti),
ALL(Tuote[Nimi])
)
Measure palauttaa saman tuloksen kuin pelkkä COUNTROWS.
ALL-funktio poistaa Tuote[Nimi] -filtterin filtterikontekstista, mutta ei Tuote[Väri]:n -filtteriä
CALCULATE-funktion lopullisessa filtterikontekstissa, joka kohdistetaan myynti-tauluun, on Tuote[Nimi] -filtteri ja Tuote[Väri] -filtteri. Tuote[Nimi] -filtteri sisältää kaikki rivit {Tuote 1, Tuote 2, Tuote 3} ja Tuote[Väri] -filtteri vain taulusta tulevan filtterikontekstin mukaisesti yhden värin per tuoterivi. Kun nämä kaksi filtteriä yhdistetään AND-logiikan mukaan lopulliseksi filtterikontekstiksi, niin näkyviin jää yksi rivi johtuen Tuote[Väri] -filtteristä.
Tuote_1_FILTER_ALL_nimi =
CALCULATE(
COUNTROWS(Myynti),
FILTER(
ALL(Tuote[Nimi]),
Tuote[Nimi] = "Tuote 1"
)
)
Measure palauttaa Tuote 1:lle sen todelliset 2 riviä, mutta ei mitään muille tuotteille.
ALL-funktio FILTER-funtion sisällä palauttaa uniikin listan kaikista Tuote[Nimi] kolumnin arvoista (ei siis enää FILTER:n argumenttina poista filtterikontekstia). FILTER-funktio palauttaa filtteriksi vain Tuote 1:n Tuote[Nimi] -arvon.
Lopullisessa filtterikontekstissa yhdistyy AND-logiikan mukaan Tuote[Nimi] ja Tuote[Väri]. Lopullinen filtterikonteksti sisältää siis vain ne rivit, joissa Tuote[Nimi] = 1 ja Tuote[Väri] on sama kuin mikä se on taulun luomassa filtterikontekstissa. Tämä ei toteudu yllä olevan taulukon toisella ja kolmannella rivillä, koska Tuote-taulussa ei ole riviä jossa Tuote[Nimi] = Tuote 1 ja Tuote[Väri] = Sininen tai Keltainen.
Tuote_1_nimi =
CALCULATE(
COUNTROWS(Myynti),
Tuote[Nimi] = "Tuote 1"
)
Measure palauttaa saman mitä Tuote_1_FILTER_ALL_nimi.
Koska Tuote[Nimi] = ”Tuote 1” -filtteri vastaa laskennassa FILTER(ALL(Tuote[Nimi]), Tuote[Nimi] = ”Tuote 1” -filtteriä.
Tuote_1_VALUES =
CALCULATE(
COUNTROWS(Myynti),
FILTER(
VALUES(Tuote[Nimi]),
Tuote[Nimi] = "Tuote 1"
)
)
Measure palauttaa Tuote 1:lle sen todelliset 2 riviä, mutta ei mitään muille tuotteille.
VALUES-funktio palauttaa FILTER-funktiolle vain filtterikontekstissa sille näkyvät rivit. Koska taulukon rivillä 2 ja 3 Tuote 1 ei näy filtterikontekstissa, FILTER-funktio palauttaa niille nolla riviä.
Tuote_1_myynti =
CALCULATE(
COUNTROWS(Myynti),
FILTER(
Myynti,
Myynti[TuoteId] = 1
)
)
Measure palauttaa Tuote 1:lle sen todelliset 2 riviä, mutta ei mitään muille tuotteille.
Myynti-taulusta (FILTER-funktion sisällä) jää näkyviin vain filtterikontekstissa sille näkyvät rivit. FILTER iteroi myyntitaulusta näkyviin jäävät rivit ja pitää ne, joissa Myynti[TuoteId] = 1 (eli Myynti-taulun riveillä 1 ja 2). Koska Myynti-taulun riveillä 3 ja 4 Myynti[TuoteId] != 1, ei FILTER-funtio palauta niille yhtään riviä. Täten taulukon riveillä 2 ja 3 FILTER-funktio ei palauta yhtään riviä.
Vaikka lopputulema on sama kuin edellisessä esimerkissä, lopullisessa filtterikontekstissa on aiemmasta poiketen kuitenkin vain Myynti(taulu)-filtteri. Tuote[Nimi]- ja Tuote[Väri]- kolumnit kuuluvat laajennettuun Myynti-tauluun (Expanded tables -käsite), joten Myynti(taulu)-filtteri yliajaa Tuote[Nimi] ja Tuote[Väri] –(kolumni)filtterit.
Tuote_1_ALL_myynti =
CALCULATE(
COUNTROWS(Myynti),
FILTER(
ALL(Myynti),
Myynti[TuoteId] = 1
)
)
Measure palauttaa kaikille tuoteriveille Tuote 1:sen 2 riviä.
ALL-funktio palauttaa FILTER-funktiolle koko Myynti-taulun. FILTER-funktio iteroi läpi koko taulun ja palauttaa ulkopuolisesta filtterikontekstista riippumatta aina rivit 1 ja 2 (Myynti[TuoteId] = 1). FILTER palauttaa siis lopulliseen filtterikontekstiin 1 ja 2 rivit myyntitaulusta.
Kuten edellisessä esimerkissä, Myynti(taulu)-filtteri yliajaa lopullisesta filtterikontekstista kaikki sen laajennetussa kontekstissa olevat kolumnit ja samalla poistaa Tuote[Nimi]- ja Tuote[Väri] -filtterit.
On tärkeää huomata, että jos FILTER-funktio olisi iteroinut yli ALL(Myynti[TuoteId]):n, olisi lopulliseen filtterikontekstiin jäänyt myös Tuote-taulusta tulevat filtterit. Kolumneilla ei ole laajennettua kontekstia. Tällöin CALCULATE-funktioon olisi pitänyt lisätä erillisiksi argumenteiksi FILTER:n rinnalle ALL(Tuote[Nimi]) ja ALL(Tuote[Väri]). Tämä on esitetty seuraavassa esimerkissä.
Tuote_1_myynti_tuote =
CALCULATE(
COUNTROWS(Myynti),
FILTER(
ALL(Myynti[tuoteid]),
Myynti[TuoteId] = 1
),
ALL(Tuote[Nimi], Tuote[Väri])
)
Measure palauttaa kaikille tuoteriveille Tuote 1:sen 2 riviä.
FILTER-funktion kanssa samalle tasolle tarvitaan ALL(Tuote[Nimi], Tuote[Väri]) poistamaan Tuote-taulun kolumnien filtterit. Muussa tapauksessa muille kuin Tuote 1:lle ei näkyisi yhtään riviä.
Tuote_1_KEEPFILTERS_nimi =
CALCULATE(
COUNTROWS(Myynti),
KEEPFILTERS(Tuote[Nimi] = "Tuote 1")
)
Measure palauttaa Tuote 1:lle sen todelliset 2 riviä, mutta ei mitään muille tuotteille.
Normaalisti ALL-funktiot yliajavat aiemman (saman) filtterikontekstin. Samoin toimii mm. sisäkkäiset CALCULATE-funktiot. KEEPFILTERS-funktio muuttaa kuitenkin päällekkäisten filtterikontekstien kohtaamisen yliajamisesta yhdistämiseksi. Käytännössä tällöin tapahtuu AND-logiikka, kuten CALCULATE-funktion argumenttien välillä.
Yllä olevassa kaavassa ei siis yliajeta olemassa olevaa Tuote[Nimi] filtteriä, vaan yhdistetään uusi filtteri jo olemassa olevaan. Tämän takia Tuotteiden 2 ja 3 filtterikontekstissa ei CALCULATE-funktion lopullisessa filtterikontekstissa lopulta näy yhtään Tuote[Nimi]-riviä.
Context Transition & Calculated columns
Lopuksi mainittakoon käytännön tärkeyden takia myös ns. Context Transition -tilanne, jossa rivikonteksti muuttuu filtterikontekstiksi. Tämä tapahtuu esimerkiksi, kun lasketaan CALCULATE-funktiolla arvo Calculated Column:n sisälle, tai käyttäessä Table-funktioita measureissa. Tällöin on hyvä muistaa, että Context Transitionin yhteydessä uusi filtterikonteksti ei vaikuta CALCULATE-funktio filtteri-argumentteihin (argumentit 2,3…).
Jos CALCULATE-funktion 2. argumentti palauttaa taulun, jonka laajennettuun tauluun kuuluu myös Context Transitionin yhteydessä tulleen uuden filtterikontekstin kolumnit, ovat tulokset erilaisia kuin täysin vastaavan measuren laskennassa.
Tämä johtuu siitä, että CALCULATE-funktion 2. argumenttina oleva taulu-filtteri on itse immuuni ulkopuolisille Context Transition -filttereille, mutta yliajaa ne, kun yksittäiset filtterit yhdistetään filtterikontekstiksi. Varsinaisella filtterikontekstilla on yhdistymistilanteessa prioriteetti Contect Transitionin kautta tulleeseen filtterikontekstiin. Tähän ongelmaan lienevät törmänneet kaikki pidempään DAX:n parissa työskennelleet.
Alla vielä esimerkki tilanteesta, jossa measure on täysin sama kuin aiemmassa taulukossa, mutta tulos eri Calculated Columnin sisällä kuin meidän aiemmassa taulukossa. Huomaa, että myös Tuote 2 ja 3 saavat Tuote 1:sen rivimäärän.
Yhteenveto
Edellä käytiin läpi useasti vastaan tulevia CALCULATE-funktion filtterikontekstin muokkaukseen liittyviä caseja. Pohjimmiltaan DAX on intuitiivinen kieli, mutta vaatiin hyvän ymmärryksen mm. filtterikontekstin toiminnasta.
(Alkuperäinen kirjoittaja Tero Isoranta)