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

本文說明如何在 Power BI Desktop 中建立及使用動態 M 查詢參數。 透過使用動態 M 查詢參數，模型作者可以設定篩選或交叉分析篩選器值，讓報表檢視者可以將其用於 M 查詢參數 (部分機器翻譯)。 動態 M 查詢參數可讓模型作者更充分掌控要納入 DirectQuery 來源查詢之中的篩選選取項目。

模型作者了解其篩選的預期語意，且通常知道如何針對其資料來源撰寫有效率的查詢。 透過使用動態 M 查詢參數，模型作者可以確保篩選選取項目能在正確的時間點納入來源查詢之中，以達成預期的結果和最佳效能。 動態 M 查詢參數對於查詢效能最佳化特別有用。

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

注意

此影片可能使用舊版的 Power BI Desktop 或 Power BI 服務。

必要條件

若要完成這些程序，您必須具有使用一或多個 DirectQuery 資料表的有效 M 查詢 (部分機器翻譯)。

建立及使用動態參數

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

新增參數

1. 在 Power BI Desktop 中，選取 [常用]>[轉換資料]>[轉換資料]，以開啟 Power Query 編輯器。

2. 在 Power Query 編輯器中，選取功能區中 [管理參數] 底下的 [新增參數]。

   

3. 在 [管理參數] 視窗中，填寫參數的相關資訊。 如需詳細資訊，請參閱建立參數。

   

4. 選取 [新增] 以新增更多參數。

   

5. 當您完成新增參數時，請選取 [確定]。

參考 M 查詢中的參數

1. 建立參數之後，您可以在 M 查詢中參考那些參數。 若要修改 M 查詢，請在選取查詢時，開啟 [進階編輯器]。

   

2. 參考 M 查詢中的參數，其已在下圖中以黃色醒目提示：

   

3. 當您完成編輯查詢時，請選取 [完成]。

建立值的資料表

針對每個參數建立一個資料表，其中包含一個資料行以提供可供根據篩選選取項目加以動態設定的可能值。 在此範例中，您希望 StartTime 和 EndTime 參數都是動態的。 由於這些參數需要 Date/Time 參數，因此您會產生可能的輸入以動態設定參數的日期。

1. 在 Power BI Desktop 功能區的 [模型化] 底下，選取 [新增資料表]。

   

2. 為 StartTime 參數的值建立資料表，例如：

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

   

3. 為 EndTime 參數的值建立第二個資料表，例如：

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

   

   注意

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

將欄位繫結至參數

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

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

   注意

   資料行資料類型應該符合 M 參數資料類型。

   

2. 選取 [繫結至參數] 底下的下拉式清單，然後選取您想要繫結至欄位的參數：

   

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

   

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

   如果您的使用案例需要將多個值傳遞至單一參數，請將控制項設定為 [是]，並確定您的 M 查詢已設定為接受多個值。 以下是 RepoNameParameter 的範例，其允許多個值：

   

3. 如果您有要繫結至其他參數的其他欄位，請重複這些步驟。

   

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

啟用全選

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

若要針對 Country 啟用 [全選]：

1. 在 Country 的 [進階] 屬性中，啟用 [全選] 切換，其能啟用 [全選值] 輸入。 編輯 [全選值] 或記下預設值。

   

   [全選值] 會作為包含您所定義值的清單傳遞至參數。 因此，當您定義此值或使用預設值時，請確定值是唯一的，而且不存在於繫結至參數的欄位中。

2. 啟動 Power Query 編輯器，選取查詢，然後選取 [進階編輯器]。 編輯 M 查詢以使用 [全選值] 來參考 [全選] 選項。

   

3. 在 [進階編輯器] 中，新增能在參數啟用 [多重選取] 且包含 [全選值] 的情況下評估為 true，否則會傳回 false 的布林運算式：

   

4. 將全選布林運算式的結果納入來源查詢。 此範例在來源查詢中具有名為 includeAllCountries 的布林值查詢參數，其會設定為上一個步驟中布林運算式的結果。 您可以在查詢的篩選子句中使用此參數，使得 false 的布林值會篩選至選取的國家或地區名稱，而 true 實際上則不會套用任何篩選。

   

5. 更新 M 查詢以將新的 [全選值] 納入考量之後，您就可以在交叉分析篩選器或篩選中使用 [全選] 功能。

   

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

Kusto

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 查詢：

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 查詢篩選作業的範例：

  Kusto

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

• 在來源查詢中宣告參數，或將參數值作為輸入傳遞至來源查詢函式的範例：

  Kusto

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

• 直接呼叫預存程序的範例：

  Kusto

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

考量與限制

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

• 單一參數無法繫結至多個欄位，反之亦然。
• 動態 M 查詢參數不支援彙總。
• 動態 M 查詢參數不支援列層級安全性 (RLS)。
• 參數名稱不能是 Data Analysis Expressions (DAX) 保留字，也不能包含空格。 您可以將 Parameter 附加至參數名稱的結尾，以協助避開這項限制。
• 資料表名稱不可包含空格或特殊字元。
• 如果您的參數是 Date/Time 資料類型，您必須在 M 查詢中將其轉換成 DateTime.Date(<YourDateParameter>)。
• 如果您使用 SQL 來源，則每次參數值變更時，您都可能會收到確認對話方塊。 此對話方塊是基於下列安全性設定：[需要使用者核准新的原生資料庫查詢]。 您可以在 Power BI Desktop [選項] 的 [安全性] 區段中找到並關閉此設定。
• 在 Excel 中存取語意模型時，動態 M 查詢參數可能會無法運作。
• Power BI 報表伺服器不支援動態 M 查詢參數。
• Power BI 服務 不支援使用動態 M 查詢參數切換數據源。 如需詳細資訊，請參閱重新整理和動態數據源。

不支援的現用參數類型

• 任意
• 期間
• True/False
• 二進位

不支援的篩選

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

不支援的作業

• 且
• 包含
• 小於
• 大於
• 開始於
• 開頭不是
• 不是
• 不包含
• 是空白
• 不是空白

相關內容

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

• Power BI 中的 DirectQuery
• Power BI Desktop 是什麼？
• Power BI Desktop 中的查詢概觀
• Power BI Desktop 中的資料類型
• 教學課程：在 Power BI Desktop 中將資料成形及合併
• 在 Power BI Desktop 中執行常見查詢工作