Minulta kysyttiin taannoin, voisiko Power BI -raporttia suodattaa Power Appsia hyödyntäen. Siten että suodattimena käytetään 1-150 asiakasnumeroa, jotka kopioidaan toisesta työkalusta leikepöydälle pilkulla eroteltuina.

En tiedä onko tämä tyypillinen käyttötapaus, mutta kuulostaa mielenkiintoiselta. Pakkohan sitä on kokeilla.

Esimerkkiraportti

Käytetään esimerkissämme yksinkertaista raporttia. Se sisältää asiakkaita (~1500kpl) ja asiakkaiden myyntitapahtumia.

Raportti näyttää tältä.

Tehtävänämme on rakentaa tapa suodattaa raporttia n asiakkaalla, jotka saadaan kopioitua toisesta työkalusta pilkulla eroteltuna listana.

Power Apps -visualisointi Power BI:lle

Ensimmäisenä mieleen tulee upottaa raportille Power Apps -visualisointi ja rakentaa suodatus Power Appsiin.

Power Apps -visualisointi toimii kuitenkin yksisuuntaisesti. Power Appsiin välitettävä tietojoukko suodattuu käyttäjän tehdessä valintoja raportin muilla visualisoinneilla. Mutta käyttäjän Power Appsissa tekemillä valinnoilla ei ole vaikutusta Power BI:n muihin visualisointeihin.

Eli pitää keksiä jotai muuta.

Suodatusvalintojen tallennus omaan tauluunsa

Luodaan raportille upotettava Power Apps, jonka avulla käyttäjä voi tallentaa omia suodattimiaan (filter). Suodattimesta tallennetaan

  • Nimi
  • Millä arvoilla suodatetaan (FilterValue)
  • Kuka suodattimen on tehnyt (Login)
  • Tallennetaanko suodatin pysyvään käyttöön (Permanent)

Lisätään tätä varten taulu (PowerBIFilter) Dataverseen.

Suodatustaulun lisääminen raportille

Lisätään PowerBIFilter -taulu ensimmäiseksi Power BI:hin.

Ympäristön url (environment domain), löytyy nopeiten Power Appsin asetuksista (Settings – Session details). Se syötetään kenttään ilman https:// -määrettä.

Poimitaan oikea taulu ja muokataan sitä hieman (Transform Data).

Poistetaan kaikki muut, kuin meitä kiinnostavat kentät (Remove Other Columns).

Seuraavaksi jaetaan filttervalue-kenttä omiksi riveikseen (Split Column).

Vaihdetaan advanced options -kohdasta kohteeksi rivit. Näin jokaisesta suodatuksessa käytettävästä asiakasnumerosta syntyy oma rivi.

Viimeistellään työ uudellennimeämällä kentät.

Yksi tallennettu suodatin näyttää tältä.

Power Apps -visualisointi

Lisätään raportille Power Apps -visualisointi.

Valitaan visualisoinnissa käytettävät kentät (fields) ja luodaan uusi Power Apps (Create new).

Meille avautuu Power Apps, joka on kiinni raportin tietomallissa (PowerBIIntegration).

Poistetaan galleria ja lisätään

  • PowerBIFilter-taulu tietolähteeksi
  • luomaamme PowerBIFilter-taulua vastaavat kentät
  • tekstikenttä, jossa esitetään pilkulla erotettujen asiakasnumeroiden lukumäärä
  • painike tallennusta varten

Pilkulla erotettujen suodatustekijöiden lukumäärä selviää seuraavalla kaavalla.

"Values in filter: " & CountRows(Split(inpFilterValues.Text, ","))

Suodatin tallennetaan yhdelle riville. Tallennuksen jälkeen tietomallin PowerBIFilter-taulu päivitetään PowerBIIntegration.Refresh() -komennolla.

Näin.

Patch(PowerBIFilters, 
      Defaults(PowerBIFilters),
      {Name:inpFilterName.Text, 
       FilterValue:inpFilterValues.Text,      
       Permanent:chkPermanent.Value, 
       Login: User().Email}
     );
PowerBIIntegration.Refresh()

Tarvittava Power Apps on nyt valmis.

Suodattimen käyttäminen

Lisätään Power BI:n tietomalliin suodatustaulu (PowerBIFilter). Sen suodatuskenttä (FilterValue) liittyy asiakkaan tunnisteeseen (CustomerID).

Lisätään raportille suodatin (slicer) ja kytketään se PowerBIFIlter-taulun Filtername-kenttään. Nyt käyttäjä voi valita Power Appsilla luodun suodattimen ja raporttia suodatetaan siihen tallennetulla asiakaslistalla.

Suodatinta voidaan halutessa viilata siten, että kukin käyttäjä näkee vain itse tallentamansa suodattimet.

Täydellistä!

Vai onko…

Ratkaisu ei ole tällaisenaan käyttökelpoinen. Käyttäjän tallentamat suodattimet eivät ole raportilla heti käytettävissä. Jotta tämä olisi mahdollista, tulisi PowerBIFilter-tauluun olla Direct Query -yhteys. Tällöin taas filttervalue-kentän arvoa ei voida jakaa useaksi riviksi Split Column -toiminnolla.

Meidän tulisikin tehdä jotain seuraavista

  • Käynnistää Power Appsisa koko tietomallin päivitys suodattimen tallennuksen jälkeen. Tässä ei ole järkeä, kyseessä voi olla todella raskas ja pitkäkestoinen toiminto.
  • Tallentaa Power Appsissa suodattimen arvot suoraan omiksi riveikseen. Tämä onnistuu mainiosti, mikäli arvoja on maltillinen määrä (~100).
  • Pitää suodattimen arvot omilla riveillään tietomallissa ja loihtia raportin suodatus kasaan DAX:ia hyödyntäen

Vedetään kuitenkin vielä ässä hihasta ja lähestytään ongelmaa toisella tavalla.

URL-parametrin generointi Power Appsilla

Entä jos tekisimmekin Power Appsin, johon syötetään halutut asiakasnumerot ja sovellus muodostaisi linkin raporttiin. Linkin jossa annetut asiakasnumerot olisivat url:ssa suodattimina.

Sovellus näyttää lähes samalta, kuin aikaisempi yrityksemme. Sen avaamisen yhteydessä (näytön OnVisible) tallennetaan raportin osoite muuttujaan (varReportBaseURL).

Käyttäjä syöttää tekstikenttään asiakasnumerot pilkulla erotettuina. Meidän tehtäväksemme jää muodostaa linkki, jolla raportti aukeaa suodatettuna. Eli se on muodossa

 url&filter=Customer/CustomerID%20in%20(%271%27,%272%27,%273%27)

Suodatin on siis pilkulla eroteltu lista asiakasnumeroista, joiden ympärillä on hipsut (%27).

Käytännössä

  • muodostetaan käyttäjän syöttämästä listasta taulukko (Split) ja tallennetaan se muuttujaan (varUrl)
  • muodostetaan taulukosta uudelleen merkkijono (Concat), siten että jokaisen asiakasnumeron ympärille lisätään hipsut (%27) ja perään pilkku
  • lopuksi poistetaan viimeisen asiakasnumeron perästä turha pilkku (Left)
Launch(
  varReportBaseURL & "&filter=Customer/CustomerID%20in%20(" & 
  With(
       {varUrl: Concat(Split(inpFilterValues.Text,","),
                "%27" & Result & "%27,")},
        Left(
            varUrl,Len(varUrl) - 1)
    ) & ")"
)

Mikäli asiakasnumerot olisivat tietomallissa oikeasti numeroita, olisi tämä ollut huomattavasti suoraviivaisempaa. Koska hipsuja ei olisi tarvinnut lisätä.

Kokeillaan kolmella asiakasnumerolla.

Toimii!

Tässä lähestymisessä tulee sitten vastaan url:in pituus. Suuremmalla joukolla asiakasnumeroita meitä lopulta tervehtii pöllö.

Power BI raportin upottaminen Power Appsiin

Ideaa voidaan vielä jatkojalostaa. Miksi avaisimme raportin uuteen selainikkunaan, kun voimme upottaa sen Power Appsin sisään hyödyntäen edellisessä kohdassa toteuttamaamme url-suodatusta.

Näin raportti suodattuu välittömästi tekstikenttään syötetyillä asiakasnumeroilla.

Yhteenveto

Mikään esitetyistä toteutustavoista ei ole täydellinen. Mikä niistä sopii omaan käyttötapaukseesi? Riipuu täysin yksityiskohdista.

Oletan että saman ongelman ratkaisee myös joku 3. osapuolen valmis Power BI visualisointi.