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 查询后，可以在切片器或筛选器中使用“全选”功能。

   

下面是上述示例的完整查询，供参考：

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 Report Server 不支持动态 M 查询参数。

不受支持的现成参数类型

• 任意
• Duration
• True/False
• 二进制

不受支持的筛选器

• 相对时间切片器或筛选器
• 相对日期
• 层次结构切片器
• 多字段包含筛选器
• 排除筛选器/非筛选器
• 交叉突出显示
• 向下钻取筛选器
• 交叉钻取筛选器
• 前 N 个筛选器

不受支持操作

• 且
• 包含
• 小于
• 大于
• 开头为
• 开头不为
• 不是
• 不包含
• 为空白
• 不为空白

相关内容

有关 Power BI Desktop 功能的详细信息，请查看以下资源：

• Power BI Desktop 中的 DirectQuery
• 什么是 Power BI Desktop？
• Power BI Desktop 的查询概述
• Power BI Desktop 中的数据类型
• 使用 Power BI Desktop 成型和合并数据
• Power BI Desktop 中的常见查询任务