Microsoft julkisti marraskuussa 2023 Power BI Desktopiin esiversion uudesta DAX-kyselynäkymästä (DAX Query View). Aiemmin DAX-kyselyiden suoritus on vaatinut lisätyökaluja, kuten esimerkiksi DAX Studion, Tabular Editorin tai SQL Server Management Studion. [Edit 2024/03: Kyselynäkymä aktivoitui oletusarvoisesti käyttöön maaliskuisen Power BI Desktop -päivityksen yhteydessä.]

Uusi näkymä on helppokäyttöinen ja riittävän yksinkertainen, jotta myös itsepalvelukäyttäjät voivat hyötyä DAX-kyselyistä. Teknisemmille kehittäjille mainitut lisätyökalut tarjoavat edelleen monipuolisempia toiminnallisuuksia, joskin uusi kyselynäkymä on näppärä apu myös heille.

Kyselynäkymä on esiversiovaiheessa, joten sitä kehitetään edelleen ja se pitää erikseen aktivoida käyttöön Power BI:n asetuksista, jos käytät maaliskuuta 2024 aiempaa Power BI Desktop -versiota.

Jatka lukemista, jos sinua kiinnostaa mitä DAX-kyselyt ovat ja miten voit hyötyä kyselynäkymästä, kun tarkastelet tietomallia ja kehität DAX-kaavalogiikkaa. Blogissa esitellään melko lyhyesti mistä on kyse, mutta löydät artikkelin lopusta linkkejä myös kattavampiin ohjeisiin sekä ohjevideoihin.

Katsaus DAX-kaavoihin ja DAX-kyselyihin

DAX-kaavat

Artikkelissa oletetaan, että ymmärrät DAX-kaavojen ja -funktioiden perusteita ja osaat laatia Power BI Desktopissa DAX-kielellä mittareita (measures) ja laskettuja sarakkeita (calculated columns). Olet saattanut luoda tietomalliin DAX-kielellä myös tauluja (table).

Kun tietomalliin luodaan kaavarivin avulla mittareita, kaavasarakkeita tai tauluja, Power BI Deskopin kaavariville kirjoitetaan kaavan nimi, yhtäsuuruusmerkki ja suoritettava lauseke.

Mittari:
Total Sales =
SUMX( Sales, Sales[Quantity] * Sales[UnitPrice] )

Kaavasarake:
Delivery Delay in Days =
Sales[Delivery Date] - Sales[Order Date]

Taulukko:
SalesPerson =
FILTER( Employee, Employee[IsSalesPerson] = ”Yes” )

DAX-kaavojen avulla tietomalliin lisätään laskentalogiikkaa sekä taulukoita. DAX-kaavat luovat tietomalliin uusia kohteita (items), jotka näkyvät raportointinäkymässä ja joita voi hyödyntää raporteissa.

DAX-kyselyt

DAX-kyselyt puolestaan muistuttavat SQL-kyselyitä, sillä niiden avulla tietomallista voidaan saada tarkasteltavaksi tulosnäkymiä.

DAX-kyselyt eivät luo tietomalliin kohteita, kuten fyysisiä taulukoita. Ne eivät myöskään näy raportointinäkymässä eikä niitä voi hyödyntää raporteissa.

Kysymys: Mitä hyötyä on kyselyistä, jos niiden tuloksia ei pysty hyödyntämään raporteissa?

Vastaus: Kyselyitä käytetään apuvälineinä tietomallin tarkastelussa ja dokumentoinnissa sekä kaavojen laatimisessa ja muokkaamisessa. Niistä on paljon apua tietomallin logiikan toteutukseen liittyvässä taustatyössä.

Alla on esimerkki yksinkertaisesta DAX-kyselystä, jossa on käytetty EVALUATE-komentoa. EVALUATE-komennon avulla voi suorittaa halutun lausekkeen ja saada näkyviin lausekkeen muodostaman tulostaulukon. Yleensä EVALUATE on tapana kirjoittaa omalle rivilleen ja suoritettava lauseke sen alle. Lauseke kirjoitetaan DAX-funktioiden avulla ja se voi olla pitkä erilaisista funktioista muodostuva koodi.

Alla oleva kysely suorittaa EVALUATE-komennon avulla FILTER-funktion ja näyttää tuloksen Results (Tulokset) -paneelissa. Tauluun on suodatettu ne Sales-taulun rivit, joissa yksikköhinta on suurempi kuin 1000.

Kun kysely suoritetaan (Run), tulos näytetään Results-paneelissa, mutta se ei muodostu fyysisenä taulukkona tietomalliin.

Kyselyistä on hyötyä, kun esimerkiksi kehittelet DAX-funktion argumentiksi sopivaa virtuaalista taulua. Voit ensin testata kyselyn avulla, osaatko kirjoittaa koodin oikein. DAX-kyselyistä on myös hyötyä, kun sinun pitää tarkastella ja muokata tiettyä mittaria sekä sitä edeltäviä mittareita.

DAX-kyselynäkymän aktivointi Power BI Desktopissa

Aktivoi esiversio-ominaisuus komennolla File > Options and Settings > Options > GLOBAL > Preview features >  DAX query view  (Tiedosto > Asetukset ja vaihtoehdot > Valinnat > YLEINEN > Esiversio-ominaisuudet > DAX-kyselynäkymä). 

Kun olet aktivoinut DAX-kyselynäkymän käyttöön, sulje ja käynnistä Power BI Desktop, jonka jälkeen se ilmestyy muiden näkymien alapuolelle.

DAX-kyselynäkymän hyödyt

Seuraavassa on joitakin hyödyllisiä DAX-kyselynäkymän käyttötapauksia. Voit esimerkiksi

• tarkastella tietomallin sisältöä
• tarkastella tietomalliin liittyviä tilastoja
• hyödyntää näkymää mittareiden luonnissa ja niiden muokkauksessa
• tarkastella yhden mittarin sekä sitä edeltävien mittareiden koodeja yhdessä näkymässä
• tarkastella ja validoida mittareiden tuloksia, ilman että sinun tarvitsee luoda sitä varten visualisointeja raporttisivuille
• kehitellä koodeja, jotka muodostavat virtuaalisia tauluja muiden funktioiden sisällä käyttettäväksi
• hyödyntää kyselynäkymän sivuja tietomallin mittareiden dokumentoinnissa
• käyttää INFO-funktioita tietomallin sisällön tarkastelussa ja dokumentoinnissa
• tarkastella Performance Analyzer -työkalun muodostamia kyselyitä ja tutkia mahdollisia ongelmia
• jne.

Kyselyn suoritus

Yksinkertaisimmillaan kyselyssä on vain yksi EVALUATE-komento ja lauseke, joka halutaan suorittaa. Alla oleva kysely suorittaa Sales-taulun sisällön näyttämisen sellaisenaan. Kysely suoritetaan Run-painikkeella ja kyselyn tulostaulukko näytetään näkymän alaosassa.

Jos kyselyssä on useita EVALUATE-komentoja, kustakin muodostuu suorituksen yhteydessä oma tulostaulukkonsa.

Koska kyselyiden tulostaulukoista ei muodostu fyysisiä tauluja tietomalliin, ne eivät myöskään kasvata tietomallin kokoa. Kyselyiden tulostaulukot eivät näy raportointi-, taulukko- tai mallinnusnäkymissä. Ainoastaan kyselyn koodi tallentuu Power BI -tiedostoon ja sen voi suorittaa milloin tahansa uudelleen. Kun tallennat, suljet ja avaat Power BI -tiedoston, tulostaulukot ovat tyhjiä kunnes suoritat kyselyt.

Voit luoda kyselyitä eri välilehdille ja nimetä välilehtiä kaksoisnapsautamalla niitä. Jos välilehden kysely/kyselyt on suoritettu onnistuneesti, nimen edessä näkyy vihreä symboli. Punainen hälytysmerkki kertoo, että kyselyissä on ollut virheitä.

Huomaa, että EVALUATE-komennolle pitää antaa suoritettavaksi sellainen lauseke, jonka tuloksena on taulukko.

Tavallista mittaria (esim. [Total Sales]) ei voi evaluoida sellaisenaan, sillä mittarin tulos ei ole taulukko vaan yksittäinen arvo.

Jos haluat tarkastella mittarin tulosta kyselynäkymän avulla, luo lauseke, jonka tuloksena on taulukko. Taulukon muodostamisessa voi käyttää esimerkiksi { } -sulkumerkkejä, SUMMARIZECOLUMNS-funktiota tai jotain muuta funktiota, joka antaa tuloksenaan taulukon (table).

Tulostaulukon sarakkeet ovat vielä tällä hetkellä muotoilemattomia. Tähän tulee toivottavasti pian päivitys, sillä isoja lukuja on hankala tarkastella ilman tuhaterottimia.

EVALUATE-komennon lisäksi kyselyissä voi olla muita DAX-kyselykielen komentosanoja, kuten ORDER BY, START AT, DEFINE, MEASURE, VAR, TABLE ja COLUMN. Voit ryhtyä opiskelemaan DAX-kyselyitä sekä komentoja vaikkapa Microsoftin sivuilta:

• DAX queries
• DAX statements

Alla olevan koodin tulostaulukkona on Product-taulun sisältö lajiteltuna tuotteen nimen perusteella nousevaan järjestykseen (ASC).

Kyselynäkymän työkalut

Kyselynäkymässä on hyödyllisiä työkaluja, joiden avulla voit mm. muotoilla kyselyä, kommentoida koodilohkoja, poistaa kommentteja sekä etsiä ja korvata tekstiä.

Tavallinen Power BI Desktopin kaavarivi ei ole käytössä kyselynäkymässä. Sinun pitää siirtyä muihin Power BI Desktopin näkymiin, jos muokkaat tai luot mittareita tavallisella kaavarivillä.

Mittarin sekä sen edeltäjien tarkastelu pikakyselyn avulla

Jos avaat pikavalikon yksittäisen mittarin päältä (esim. Profit), saat esiin mittariin liittyviä pikakyselyitä (quick queries). Pikakyselyt kirjoittavat puolestasi kyselyitä, jotka suoritetaan heti.

Define with references and evaluate (Määritä käyttäen viittauksia ja arvioi) on pikakyselyistä hyödyllisin. Sen avulla saa muodostettua kyselyn, jossa näytetään sekä valittu mittari (esim. Profit) että kaikki sitä edeltävät viitatut mittarit (Total Sales ja Total Cost). Kyselystä on apua, kun pitää pystyä tarkastelemaan mittarin edeltäjiä eli kaikkia niitä mittareita, jotka vaikuttavat kyseiseen mittariin.

Jos kuljetat hiiren koodissa olevan mittariviittauksen päälle, saat näkyviin myös mittarin koodin.

Kun yllä oleva kysely suoritetaan Run-toiminnolla, kysely ei tee muutoksia tietomalliin. Kyselyssä olevat DEFINE MEASURE -komennot eivät luo eivätkä muokkaa tietomallissa olevia mittareita. Kaikki tapahtuu vain keskusmuistissa. Tästä on hyötyä, kun haluat testata erilaisia muutoksia DEFINE MEASURE -lauseissa, mutta et halua muuttaa varsinaisia mittareita ennen kuin olet varmistunut muutosten toimivuudesta. 

Mittarin muokkaus ja luonti

Jos teet kyselynäkymässä muutoksia DEFINE-osiossa oleviin mittareihin, mittarin yläpuolelle ilmestyy linkki, jota napsauttamalla muutos toteutetaan myös varsinaiseen tietomallissa olevaan mittariin. Alla olevassa kuvassa Profit-mittariin on lisätty välilyöntejä miinusmerkin ympärille, jolloin Update model: Overwrite measure (Päivitä malli: korvaa mittayksikkö) -linkki on aktivoitunut.

Jos lisäät kyselynäkymässä uusia mittareita DEFINE-osioon MEASURE-komennon avulla, voit lisätä mittarin myös tietomalliin lisätyn koodin yläpuolelle ilmestyneen Update model: Add new measure (Päivitä malli: Lisää uusi mittari) -linkin avulla.

Muista että uusi näkymä on vielä esiversiovaiheessa. Omien testailuitteni perusteella Update model -linkki saattaa joskus jäädä näkyviin, vaikka sitä on jo painettu ja tietomallissa olevaa mittaria on jo muokattu. Yleensä linkki katoaa, kun vierailet toisessa näkymässä ja palaat takaisin kyselynäkymään. Bugi korjaantuu toivottavasti pian.

Muut mittarin pikakyselyt

Edellä kuvattiin yksi hyödyllisimmistä pikakyselyistä, sillä mittareiden välisten viittausten tarkastelu on ollut Power BI Desktopissa hankalaa pelkän kaavarivin avulla. Alla on lyhyesti kuvattu myös muut mittarin pikavalikosta löytyvät pikakyselyt.

• • Define and evaluate (Määritä ja arvioi) luo kyselyn, jossa on mittarin tuloksen lisäksi myös DEFINE-osio. Tässä näkymässä mittaria voi myös muokata ja päivittää sen jälkeen linkkiä napsauttamalla muutokset itse tietomalliin, kuten aiemmin kerrottiin.
    

• • Evaluate (Arvioi) luo kyselyn, joka näyttää mittarin tuloksen tulosjoukossa. Evaluate kirjoittaa koodin SUMMARIZECOLUMNS-funktiolla eikä käyttämällä {} -sulkeita.

    

    Jos haluat myöhemmin saada näkyviin myös mittarin määritelmän (define) tai mittarin määritelmän sekä edeltävät mittarit (define with reference), napsauta mittarin nimeä (esim. Profit %) ja koodiriville ilmestyvää lamppua ja valitse mitä haluat lisätä kyselyyn.
    

Taulujen sisällön tarkastelu pikakyselyiden avulla

Myös taulun pikavalikosta löytyy pikakyselyitä.

Nyt joulukuussa 2023 pikakyselyitä on vain neljä:

• Show top 100 rows (Näytä 100 ylintä riviä) luo ja suorittaa kyselyn, joka näyttää taulusta 100 ensimmäistä riviä sen perusteella miten TOPN funktiossa on määritelty taulun lajittelu. Tulostaulukossa voi olla enemmänkin kuin 100 riviä, jos 100 pienimmän joukkoon kuuluva lajitteluperuste esiintyy useammalla kuin 100 rivillä.
• Show column statistics (Näytä saraketilastot) luo ja suorittaa kyselyn, jonka tulostaulukossa näytetään taulun eri sarakkeisiin liittyviä tilastoja, kuten Min, Max, Distinct Values, jne.
• Define all measures in this table (Määritä kaikki tämän taulukon mittarit) luo kyselyn, joka määrittelee kaikki taulussa olevat mittarit ja muodostaa niiden tuloksista tulostaulukon.
• Define all measures in this model (Määritä kaikki tämän mallin mittarit) luo kyselyn, joka määrittelee kaikki tietomallissa olevat mittarit ja muodostaa niiden tuloksista tulostaulukon.

Mittareiden tulosten tarkastelu eri näkökulmista

Mittareiden tuloksia voi tarkastella perinteisesti luomalla raporttinäkymään visualisointeja. Tuloksia voi tarkastella myös kyselynäkymässä. Korvaamalla rivillä 3 olevan kentän ja suorittamalla kyselyn uudelleen, voi tarkastella samoja lukuja eri näkökulmista.

INFO-funktioiden käyttäminen tietomallin tarkastelussa sekä dokumentoinnissa

Kyselynäkymään tuli ensimmäinen päivitys heti joulukuussa, kun Microsoft julkisti yli 50 erilaista DAX-funktiota, joiden avulla saat tietoja tietomallista. 

Esimerkiksi INFO.MEASURES() listaa kaikki tietomallin mittarit sekä näyttää niihin liittyviä tietoja.

INFO.TABLES() listaa kaikki tietomallin taulut. Se näyttää myös kustakin tietomallin päivämäärä-sarakkeesta keskusmuistiin muodostuneet ja tilaa vievät piilotetut päivämäärätaulut. Löydät lisätietoja päivämäärien käyttäytymisestä tästä aiemmasta artikkelistani: Power BI ja päivämäärät

Hyödyllisiä linkkejä lisäopiskelun tueksi

Alla on muutamia hyödyllisiä artikkeli- ja videolinkkejä, joiden avulla voit jatkaa sekä DAX-kyselykielen opiskelua että uuteen kyselynäkymään perehtymistä.

• • Tiivis Guy in a Cube -opasvideo (kesto 7 min): https://www.youtube.com/watch?v=oPGGYLKhTOA

• • Loistava sekä hyvin kattava Marco Russon opasvideo (kesto 48 min), jonka suosittelen katsomaan: https://www.youtube.com/watch?v=hiBRhmX-pbM
  • Power BI tiimin kyselynäkymästä kertova blogiartikkeli: https://powerbi.microsoft.com/en-gb/blog/deep-dive-into-dax-query-view-and-writing-dax-queries/

• • Microsoftin DAX-kyselyopas: https://learn.microsoft.com/en-us/dax/dax-queries

• • Microsoftin DAX-kyselynäkymäopas: https://learn.microsoft.com/en-us/power-bi/transform-model/dax-query-view

• • Power BI tiimin INFO-funktioista kertova blogiartikkeli: https://powerbi.microsoft.com/en-us/blog/dax-query-view-introduces-new-info-dax-functions/

Kiitos lukemisesta!

Jos et ole aiemmin kirjoittanut DAX-kyselyitä, toivottavasti pääset alkuun tämän lyhyen ohjeen ja yllä olevien linkkien avulla. 

DAX-kyselyiden ymmärtämisestä on hyötyä, sillä esimerkiksi DAX-gurut Marco Russo ja Alberto Ferrari käyttävät DAX-kyselyitä omilla sivustoillaan. Ks. esimerkiksi tämä FILTER-funktion ohje. Heidän sivustoilla esiintyy myös DAX.do -kyselytyökalu, jonka avulla sivuilla olevia esimerkkikyselyitä pystyy testailemaan.

•