Parameter kueri M dinamis di Power BI Desktop

Artikel ini menjelaskan cara membuat dan bekerja dengan parameter kueri M dinamis di Power BI Desktop. Dengan parameter kueri M dinamis, penulis model dapat mengonfigurasi nilai filter atau pemotong yang dapat digunakan penampil laporan untuk parameter kueri M. Parameter kueri M Dinamis memberi penulis model kontrol lebih besar atas pilihan filter untuk dimasukkan ke dalam kueri sumber DirectQuery.

Penulis model memahami semantik filter yang dimaksudkan, dan sering mengetahui cara menulis kueri yang efisien terhadap sumber data mereka. Dengan parameter kueri M dinamis, penulis model dapat memastikan bahwa pemilihan filter dimasukkan ke dalam kueri sumber pada titik yang tepat untuk mencapai hasil yang dimaksudkan dengan performa optimal. Parameter kueri M Dinamis dapat sangat berguna untuk pengoptimalan performa kueri.

Tonton Sujata menjelaskan dan menggunakan parameter kueri M dinamis dalam video berikut, lalu coba sendiri.

Catatan

Video ini mungkin menggunakan Power BI Desktop atau layanan Power BI versi sebelumnya.

Prasyarat

Untuk bekerja melalui prosedur ini, Anda harus memiliki kueri M valid yang menggunakan satu atau beberapa tabel DirectQuery.

Membuat dan menggunakan parameter dinamis

Contoh berikut meneruskan satu nilai ke parameter secara dinamis.

Menambahkan parameter

1. Di Power BI Desktop, pilih Beranda>Ubah daya>Ubah data untuk membuka Editor Power Query.

2. Di Editor Power Query, pilih Parameter Baru di bawah Kelola Parameter di pita.

   

3. Di jendela Kelola Parameter , isi informasi tentang parameter . Untuk informasi selengkapnya, lihat Membuat parameter.

   

4. Pilih Baru untuk menambahkan lebih banyak parameter.

   

5. Setelah selesai menambahkan parameter, pilih OK.

Mereferensikan parameter dalam kueri M

1. Setelah membuat parameter, Anda dapat mereferensikannya dalam kueri M. Untuk mengubah kueri M, saat Anda memilih kueri, buka Editor Lanjutan.

   

2. Referensikan parameter dalam kueri M, seperti yang disorot dalam warna kuning dalam gambar berikut:

   

3. Setelah selesai mengedit kueri, pilih Selesai.

Membuat tabel nilai

Buat tabel untuk setiap parameter dengan kolom yang menyediakan nilai yang mungkin tersedia untuk diatur secara dinamis berdasarkan pemilihan filter. Dalam contoh ini, Anda ingin StartTime parameter dan EndTime menjadi dinamis. Karena parameter ini memerlukan Date/Time parameter, Anda menghasilkan input yang mungkin untuk mengatur tanggal parameter secara dinamis.

1. Di pita Power BI Desktop, di bawah Pemodelan, pilih Tabel baru.

   

2. Buat tabel untuk nilai StartTime parameter, misalnya:

   StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

   

3. Buat tabel kedua untuk nilai EndTime parameter, misalnya:

   EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

   

   Catatan

   Gunakan nama kolom yang tidak ada dalam tabel aktual. Jika Anda menggunakan nama yang sama dengan kolom tabel aktual, nilai yang dipilih berlaku sebagai filter dalam kueri.

Mengikat bidang ke parameter

Sekarang setelah Anda membuat tabel dengan Date bidang, Anda dapat mengikat setiap bidang ke parameter. Mengikat bidang ke parameter berarti bahwa saat nilai bidang yang dipilih berubah, nilai lolos ke parameter dan memperbarui kueri yang mereferensikan parameter.

1. Untuk mengikat bidang, dalam tampilan Model Desktop Power BI, pilih bidang yang baru dibuat, dan di panel Properti, pilih Tingkat Lanjut.

   Catatan

   Jenis data kolom harus cocok dengan jenis data parameter M.

   

2. Pilih menu dropdown di bawah Ikat ke parameter dan pilih parameter yang ingin Anda ikat ke bidang :

   

   Karena contoh ini adalah untuk mengatur parameter ke satu nilai, tetap atur Multi-pilih ke Tidak, yang merupakan default:

   

   Jika Anda mengatur kolom yang dipetakan ke Tidak untuk Multi-pilih, Anda harus menggunakan mode pilih tunggal di pemotong, atau memerlukan satu pilihan di kartu filter.

   Jika kasus penggunaan Anda memerlukan meneruskan beberapa nilai ke satu parameter, atur kontrol ke Ya dan pastikan kueri M Anda disiapkan untuk menerima beberapa nilai. Berikut adalah contoh untuk RepoNameParameter, yang memungkinkan beberapa nilai:

   

3. Ulangi langkah-langkah ini jika Anda memiliki bidang lain untuk diikat ke parameter lain.

   

Sekarang Anda dapat mereferensikan bidang ini di pemotong atau sebagai filter:

Mengaktifkan Pilih semua

Dalam contoh ini, model Power BI Desktop memiliki bidang yang disebut Negara, yang merupakan daftar negara/wilayah yang terikat ke parameter M yang disebut countryNameMParameter. Parameter ini diaktifkan untuk Multi-pilih, tetapi tidak diaktifkan untuk Pilih semua. Agar dapat menggunakan opsi Pilih semua di pemotong atau kartu filter, lakukan langkah-langkah tambahan berikut:

Untuk mengaktifkan Pilih semua untuk Negara:

1. Di properti Tingkat Lanjut untuk Negara, aktifkan tombol Pilih semua, yang memungkinkan Pilih semua input nilai. Edit nilai Pilih semua atau catat nilai default.

   

   Pilih semua nilai yang diteruskan ke parameter sebagai daftar yang berisi nilai yang Anda tentukan. Oleh karena itu, saat Anda menentukan nilai ini atau menggunakan nilai default, pastikan nilainya unik dan tidak ada di bidang yang terikat ke parameter .

2. Luncurkan Editor Power Query, pilih kueri, lalu pilih Editor Lanjutan. Edit kueri M untuk menggunakan opsi Pilih semua nilai untuk merujuk ke opsi Pilih semua .

   

3. Di Editor Lanjutan, tambahkan ekspresi Boolean yang mengevaluasi ke true jika parameter diaktifkan untuk Multi-pilih dan berisi Pilih semua nilai, dan jika tidak mengembalikan false:

   

4. Gabungkan hasil ekspresi Pilih semua Boolean ke dalam kueri sumber. Contoh memiliki parameter kueri Boolean dalam kueri sumber yang disebut includeAllCountries yang diatur ke hasil ekspresi Boolean dari langkah sebelumnya. Anda dapat menggunakan parameter ini dalam klausa filter dalam kueri, sehingga false untuk filter Boolean ke nama negara atau wilayah yang dipilih, dan true secara efektif tidak menerapkan filter.

   

5. Setelah memperbarui kueri M untuk memperhitungkan pilih semua nilai yang baru, Anda bisa menggunakan fungsi Pilih semua di pemotong atau filter.

   

Sebagai referensi, berikut adalah kueri lengkap untuk contoh sebelumnya:

let
    selectedcountryNames = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      Text.Combine({"'", Text.Combine(countryNameMParameter, "','") , "'"})
    else
      Text.Combine({"'" , countryNameMParameter , "'"}),

    selectAllCountries = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      List.Contains(countryNameMParameter, "__SelectAll__")
    else
      false,

    KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(", 
                                 "startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), " , 
                                 "endTimep:datetime = datetime(", DateTime.ToText(EndTimeMParameter, "yyyy-MM-dd hh:mm:ss"), "), ",   
                                 "includeAllCountries: bool = ", Logical.ToText(selectAllCountries) ,",",
                                 "countryNames: dynamic = dynamic([", selectedcountryNames, "]));" }),

   ActualQueryWithKustoParameters = 
                                "Covid19
                                | where includeAllCountries or Country in(countryNames)
                                | where Timestamp > startTimep and Timestamp < endTimep
                                | summarize sum(Confirmed) by Country, bin(Timestamp, 30d)",

    finalQuery = Text.Combine({KustoParametersDeclareQuery, ActualQueryWithKustoParameters}),

    Source = AzureDataExplorer.Contents("help", "samples", finalQuery, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Timestamp", "Date"}, {"sum_Confirmed", "Confirmed Cases"}})
in
    #"Renamed Columns"

Potensi risiko keamanan

Pembaca laporan yang dapat secara dinamis mengatur nilai untuk parameter kueri M mungkin dapat mengakses lebih banyak data atau memicu modifikasi ke sistem sumber dengan menggunakan serangan injeksi. Kemungkinan ini tergantung pada cara Anda mereferensikan parameter dalam kueri M dan nilai apa yang Anda berikan ke parameter.

Misalnya, Anda memiliki kueri Kusto berparameter yang dibuat sebagai berikut:

Products
| where Category == [Parameter inserted here] & HasReleased == 'True'
 | project ReleaseDate, Name, Category, Region

Tidak ada masalah dengan pengguna ramah yang melewati nilai yang sesuai untuk parameter, misalnya, Games:

| where Category == 'Games' & HasReleased == 'True'

Namun, penyerang mungkin dapat meneruskan nilai yang memodifikasi kueri untuk mendapatkan akses ke lebih banyak data, misalnya, 'Games'//:

Products
| where Category == 'Games'// & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region

Dalam contoh ini, penyerang bisa mendapatkan akses ke informasi tentang game yang belum dirilis dengan mengubah bagian kueri menjadi komentar.

Mengurangi risiko

Untuk mengurangi risiko keamanan, hindari perangkaian string nilai parameter M dalam kueri. Sebagai gantinya, gunakan nilai parameter tersebut dalam operasi M yang melipat ke kueri sumber, sehingga mesin M dan konektor membuat kueri akhir.

Jika sumber data mendukung impor prosedur tersimpan, pertimbangkan untuk menyimpan logika kueri Anda di sana dan memanggilnya dalam kueri M. Atau, jika tersedia, gunakan mekanisme passing parameter yang disertakan dalam bahasa kueri sumber dan konektor. Misalnya, Azure Data Explorer memiliki kemampuan parameter kueri bawaan yang dirancang untuk melindungi dari serangan injeksi.

Berikut adalah beberapa contoh mitigasi ini:

• Contoh yang menggunakan operasi pemfilteran kueri M:

  Table.SelectRows(Source, (r) => r[Columns] = Parameter)
  

• Contoh yang mendeklarasikan parameter dalam kueri sumber, atau meneruskan nilai parameter sebagai input ke fungsi kueri sumber:

  declare query_parameters (Name of Parameter : Type of Parameter);
  

• Contoh langsung memanggil prosedur tersimpan:

  let CustomerByProductFn = AzureDataExplorer.Contents("Help", "ContosoSales"){[Name="CustomerByProduct"]}[Data] in
  CustomerByProductFn({1, 3, 5})
  

Pertimbangan dan batasan

Ada beberapa pertimbangan dan batasan yang perlu diperhitungkan saat Anda menggunakan parameter kueri M dinamis:

• Parameter tunggal tidak dapat terikat ke beberapa bidang atau sebaliknya.
• Parameter kueri Dynamic M tidak mendukung agregasi.
• Parameter kueri Dynamic M tidak mendukung keamanan tingkat baris (RLS).
• Nama parameter tidak boleh berupa kata yang dipesan Data Analysis Expressions (DAX) atau berisi spasi. Anda dapat menambahkan Parameter ke akhir nama parameter untuk membantu menghindari batasan ini.
• Nama tabel tidak boleh berisi spasi atau karakter khusus.
• Jika parameter Anda adalah Date/Time jenis data, Anda perlu mentransmisiannya dalam kueri M sebagai DateTime.Date(<YourDateParameter>).
• Jika Anda menggunakan sumber SQL, Anda mungkin mendapatkan dialog konfirmasi setiap kali nilai parameter berubah. Dialog ini disebabkan oleh pengaturan keamanan: Memerlukan persetujuan pengguna untuk kueri database asli baru. Anda dapat menemukan dan menonaktifkan pengaturan ini di bagian Keamanan dari Opsi Desktop Power BI.
• Parameter kueri Dynamic M mungkin tidak berfungsi saat mengakses model semantik di Excel.
• Parameter kueri M Dinamis tidak didukung di Server Laporan Power BI.
• Beralih sumber data menggunakan parameter kueri M dinamis tidak didukung di layanan Power BI. Lihat refresh dan sumber data dinamis untuk informasi tambahan.

Jenis parameter out-of-box yang tidak didukung

• Mana pun
• Durasi
• True/False
• Biner

Filter yang tidak didukung

• Pemotong atau filter waktu relatif
• Tanggal relatif
• Pemotong hierarki
• Multifield menyertakan filter
• Mengecualikan filter / Bukan filter
• Penyorotan silang
• Filter penelusuran mendetail
• Filter penelusuran silang
• Filter N teratas

Operator yang tidak didukung

• Dan
• Berisi
• Kurang dari
• Lebih besar dari
• Dimulai dengan
• Tidak dimulai dengan
• bukan
• Tidak berisi
• Kosong
• Tidak kosong

Konten terkait

Untuk informasi selengkapnya tentang kapabilitas Power BI Desktop, lihat sumber daya berikut ini:

• DirectQuery di Power BI
• Apa itu Power BI Desktop?
• Gambaran umum kueri di Power BI Desktop
• Jenis Data di Power BI Desktop
• Tutorial: Membentuk dan menggabungkan data di Power BI Desktop
• Tugas kueri umum di Power BI Desktop