Power BI Desktop 中的動態 M 查詢參數

發行項
01/31/2024

本文說明如何在 Power BI Desktop 中建立及使用動態 M 查詢參數。使用動態 M 查詢參數，模型作者可以設定報表檢視者可用於 M 查詢參數的篩選或交叉分析篩選器值。動態 M 查詢參數可讓模型作者更充分掌控篩選選取範圍，以納入 DirectQuery 來源查詢。

模型作者瞭解其篩選的預期語意，並經常知道如何針對其數據源撰寫有效率的查詢。使用動態 M 查詢參數，模型作者可以確保篩選選取專案併入正確的時間點，以達到預期的結果，以獲得最佳效能。動態 M 查詢參數對於查詢效能優化特別有用。

觀看 Sujata 說明並使用下列影片中的動態 M 查詢參數，然後自行試用。

注意

這段影片可能會使用舊版 Power BI Desktop 或 Power BI 服務。

必要條件

若要完成這些程式，您必須具有使用一或多個 DirectQuery 數據表的有效 M 查詢。

建立及使用動態參數

下列範例會以動態方式將單一值傳遞至參數。

新增參數

在 Power BI Desktop 中，選取 [首頁>轉換數據轉換數據>] 以開啟 Power Query 編輯器。
在 Power Query 編輯器中，選取功能區中 [管理參數] 底下的 [新增參數]。
在 [ 管理參數] 視窗中，填寫參數的相關信息。如需詳細資訊，請參閱建立參數。
選取 [ 新增 ] 以新增更多參數。
當您完成新增參數時，請選取 [ 確定]。

參考 M 查詢中的參數

建立參數之後，您可以在 M 查詢中參考它們。若要修改 M 查詢，當您選取查詢時，請開啟 [ 進階編輯器]。
參考 M 查詢中的參數，如下圖所示，以黃色醒目提示：
當您完成編輯查詢時，請選取 [ 完成]。

建立值的數據表

使用數據行建立每個參數的數據表，以提供根據篩選選取專案動態設定的可能值。在此範例中，您希望 StartTime 和 EndTime 參數是動態的。由於這些參數需要 Date/Time 參數，因此您可以產生可能輸入，以動態設定參數的日期。

在 Power BI Desktop 功能區的 [模型] 底下，選取 [新增數據表]。
為參數的值 StartTime 建立數據表，例如：

StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))
為參數的值 EndTime 建立第二個數據表，例如：

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

注意

使用不在實際數據表中的數據行名稱。如果您使用與實際數據表數據行相同的名稱，則選取的值會套用為查詢中的篩選。

將欄位系結至參數

現在您已使用 Date 欄位建立資料表，您可以將每個欄位系結至參數。將欄位系結至參數表示當選取的域值變更時，值會傳遞至參數，並更新參考參數的查詢。

若要系結欄位，請在 Power BI Desktop 模型檢視中選取新建立的字段，然後在 [ 屬性 ] 窗格中選取 [ 進階]。

注意

數據行數據類型應該符合 M 參數數據類型。
選取 [系結至參數] 下的下拉式清單，然後選取您要繫結至欄位的參數：

由於此範例用於將參數設定為單一值，因此請將 [多重選取] 保留為 [否]，這是預設值：

如果您針對多重選取將對應的數據行設定為 [否]，則必須在交叉分析篩選器中使用單一選取模式，或在篩選卡片中要求單一選取。

如果您的使用案例需要將多個值傳遞至單一參數，請將控件 設定為 [是 ]，並確定您的 M 查詢已設定為接受多個值。以下是的 RepoNameParameter範例，其允許多個值：
如果您有其他欄位要系結至其他參數，請重複這些步驟。

您現在可以在交叉分析篩選器或篩選中參考此欄位：

Screenshot that shows referencing the fields.

開啟[全部選取]

在此範例中，Power BI Desktop 模型具有名為 Country 的欄位，這是系結至名為 countryNameMParameter 之 M 參數的國家/地區清單。此參數已啟用 多重選取，但未針對 [全部選取] 啟用。若要能夠使用 交叉分析篩選器或篩選卡片中的 [選取所有 ] 選項，請採取下列步驟：

Screenshot that shows an example of a multiselect M parameter.

若要啟用 [全選國家 /地區]：

在 [國家/地區] 的 [進階屬性] 中，啟用 [全部選取] 切換，以啟用 [選取所有值輸入]。編輯 [ 選取所有值 ] 或記下預設值。

[ 選取所有值 ] 會傳遞至參數作為包含您所定義值的清單。因此，當您定義此值或使用預設值時，請確定值是唯一的，而且不存在於系結至參數的欄位中。
啟動 Power Query 編輯器，選取查詢，然後選取 [進階編輯器]。編輯 M 查詢，以使用 [ 選取所有] 值 來參考 [ 全選] 選項。
在 進階編輯器 中，新增布爾表達式，評估為 true 是否啟用 Multi-select 參數並包含 Select all 值，否則會傳false回：
將 Select all Boolean 運算式的結果併入來源查詢中。此範例在名為 includeAllCountries 的來源查詢中有布爾查詢參數，該參數會設定為上一個步驟中布爾表達式的結果。您可以在查詢的篩選子句中使用此參數，讓 false 布爾篩選條件套用至選取的國家/地區名稱，並 true 有效地套用任何篩選。
更新 M 查詢以考慮新的 [全選] 值之後，您可以使用 交叉分析篩選器或篩選條件中的 [選取所有 函式]。

如需參考，以下是上述範例的完整查詢：

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"

潛在的安全性風險

可以動態設定 M 查詢參數值的報表讀取者，可以使用插入式攻擊來存取更多數據或觸發對來源系統的修改。這種可能性取決於您在 M 查詢中參考參數的方式，以及您傳遞至參數的值。

例如，您建構的參數化 Kusto 查詢如下所示：

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

針對參數傳遞適當值的易記用戶沒有任何問題，例如 Games：

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

不過，攻擊者可能會傳遞一個值，修改查詢以取得更多數據的存取權，例如 'Games'//：

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

在此範例中，攻擊者可以藉由將查詢的一部分變更為批注，來存取尚未發行的遊戲相關信息。

降低風險

若要降低安全性風險，請避免查詢內 M 參數值的字串串連。相反地，在折迭至來源查詢的 M 作業中取用那些參數值，讓 M 引擎和連接器建構最終查詢。

如果數據源支援匯入預存程式，請考慮將查詢邏輯儲存到該處，並在 M 查詢中叫用它。或者，如果有的話，請使用內建至來源查詢語言和連接器的參數傳遞機制。例如， Azure 數據總管具有內建的查詢參數功能，其設計目的是要防止插入式攻擊。

以下是這些風險降低的一些範例：

使用 M 查詢篩選作業的範例：

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

在來源查詢中宣告參數的範例，或將參數值當做輸入傳遞至來源查詢函式：
```
declare query_parameters (Name of Parameter : Type of Parameter);
```

直接呼叫預存程式的範例：

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

考量與限制

當您使用動態 M 查詢參數時，有一些考慮和限制：

單一參數無法系結至多個字段，反之亦然。
動態 M 查詢參數不支援匯總。
動態 M 查詢參數不支援資料列層級安全性（RLS）。
參數名稱不能是數據分析表達式（DAX）保留字，也不能包含空格。您可以將附加 Parameter 至參數名稱的結尾，以協助避免這項限制。
數據表名稱不能包含空格或特殊字元。
如果您的參數是 Date/Time 數據類型，您必須將它轉換成 M 查詢 DateTime.Date(<YourDateParameter>)。
如果您使用 SQL 來源，則每次參數值變更時，可能會收到確認對話方塊。此對話框是因為安全性設定： 需要使用者核准新的原生資料庫查詢。您可以在 Power BI Desktop 選項的 [安全性] 區段中找到並關閉此設定。
在 Excel 中存取語意模型時，動態 M 查詢參數可能無法運作。
Power BI 報表伺服器不支持動態 M 查詢參數。

不支援的現用參數類型

任意
期間
True/False
二進位

不支援的篩選

相對時間交叉分析篩選器或篩選
相對日期
階層交叉分析篩選器
多欄位包含篩選
排除篩選/非篩選
交叉醒目提示
向下切入篩選
交叉鑽研篩選
前 N 個篩選條件

不支援的作業

且
Contains
小於
大於
開始於
不要以開頭
不是
不包含
為空白
不是空白

如需 Power BI Desktop 功能的詳細資訊，請參閱下列資源：