Power BI Desktop の動的 M クエリ パラメーター

  • [アーティクル]

この記事では、Power BI Desktop で動的 M クエリ パラメーターを作成して操作する方法について説明します。 動的 M クエリ パラメーターを使用すると、モデル作成者は、レポート ビューアーで M クエリ パラメーターに使用できるフィルターまたはスライサーの値を構成できます。 動的 M クエリ パラメーターを使用すると、モデル作成者は、DirectQuery ソース クエリに組み込むフィルターの選択をさらに細かく制御できます。

モデル作成者は、フィルターの意図されたセマンティクスを理解しており、多くの場合、データ ソースに対して効率的なクエリを記述する方法を知っています。 動的 M クエリ パラメーターを使用すると、モデル作成者は、フィルターの選択を適切な時点でソース クエリに組み込み、最適なパフォーマンスで目的の結果を達成できます。 動的 M クエリ パラメーターは、クエリ パフォーマンスの最適化に特に役立ちます。

次のビデオで、Sujata による動的 M クエリ パラメーターの説明と使い方をご覧になり、ご自分でも試してみてください。

注意

このビデオでは、以前のバージョンの Power BI Desktop または Power BI サービスが使用されている可能性があります。

前提条件

これらの手順を行うには、1 つ以上の DirectQuery テーブルを使用する有効な M クエリ が必要です。

動的パラメーターを作成して使用する

次の例では、1 つの値をパラメーターに動的に渡します。

パラメーターを追加する

  1. Power BI Desktop で、 [ホーム]>[データの変換]>[データの変換] を選択して、Power Query エディターを開きます。

  2. Power Query エディターで、リボンの [パラメーターの管理] の下にある [新しいパラメーター] を選択します。

    Screenshot that shows the Ribbon menu.

  3. [パラメーターの管理] ウィンドウで、パラメーターに関する情報を入力します。 詳細については、「パラメーターの作成」を参照してください。

    Screenshot that shows parameter information.

  4. [新規] を選択して、他のパラメーターを追加します。

    Screenshot that shows New to create another parameter.

  5. パラメーターの追加を完了したら、[OK] を選択します。

M クエリでパラメーターを参照する

  1. パラメーターを作成したら、それらを M クエリで参照できます。 M クエリを変更するには、クエリを選択した状態で、[詳細エディター] を開きます。

    Screenshot that shows opening the Advanced Editor.

  2. M クエリでパラメーターを参照します。次の図では黄色で強調表示されています。

    Screenshot that shows referencing the parameter.

  3. クエリの編集を完了したら、[完了] を選択します。

値のテーブルを作成する

フィルターの選択に基づいて動的に設定できる値を提供する列を使用して、各パラメーターのテーブルを作成します。 この例では、StartTime パラメーターと EndTime パラメーターを動的にする必要があります。 これらのパラメーターには Date/Time パラメーターが必要であるため、パラメーターの日付を動的に設定するために使用できる入力を生成します。

  1. Power BI Desktop リボンの [モデリング] で、[新しいテーブル] を選択します。

    Screenshot that shows selecting New table.

  2. StartTime パラメーターの値のテーブルを作成します。次に例を示します。

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

    Screenshot that shows the first table.

  3. EndTime パラメーターの値の 2 番目のテーブルを作成します。次に例を示します。

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

    Screenshot that shows the second table.

    Note

    実際のテーブルに含まれていない列名を使用します。 実際のテーブルの列と同じ名前を使用する場合、選択した値がクエリのフィルターとして適用されます。

フィールドをパラメーターにバインドする

Date フィールドを含むテーブルが作成されたので、各フィールドをパラメーターにバインドできます。 フィールドをパラメーターにバインドすることは、選択したフィールド値が変更されると、値がパラメーターに渡され、パラメーターを参照するクエリが更新されることを意味します。

  1. フィールドをバインドするには、Power BI Desktop の [モデル] ビューで、新しく作成したフィールドを選択し、[プロパティ] ペインで [詳細設定] を選択します。

    注意

    列のデータ型は、M パラメーターのデータ型と一致する必要があります。

    Screenshot that shows binding the field to a parameter.

  2. [Bind to parameter](パラメーターにバインド) の下のドロップダウンを選択し、フィールドにバインドするパラメーターを選択します。

    Screenshot that shows binding the parameter to the field.

    この例ではパラメーターを単一の値に設定するため、[複数選択] を [いいえ] に設定したままにします。これは既定値です。

    Screenshot that shows multi-select set to No.

    マップされた列の [複数選択] を [いいえ] に設定した場合、スライサーで単一選択モードを使用するか、フィルター カードで単一選択を要求する必要があります。

    ユース ケースで複数の値を 1 つのパラメーターに渡す必要がある場合、コントロールを [はい] に設定し、M クエリが複数の値を受け入れるように設定されていることを確認します。 複数の値を許可する RepoNameParameter の例を次に示します。

    Screenshot that shows a multivalue example.

  3. 他のパラメーターにバインドするフィールドが他にもある場合は、これらの手順を繰り返します。

    Screenshot that shows configuring more parameters.

以上で、このフィールドをスライサーで、またはフィルターとして参照できるようになりました。

Screenshot that shows referencing the fields.

[すべて選択] を有効にする

次の例の Power BI Desktop モデルには、Country という名前のフィールドがあります。これは countryNameMParameter という名前の M パラメーターにバインドされている国および地域の一覧です。 このパラメーターは [複数選択] に対して有効になっていますが、[すべて選択] に対しては有効になっていません。 スライサーまたはフィルター カードで [すべて選択] オプションを使用できるようにするには、次の追加の手順を行います。

Screenshot that shows an example of a multiselect M parameter.

Country に対して [すべて選択] を有効にするには、次の手順を行います。

  1. Country の [詳細設定] プロパティで、[すべて選択] のトグルを有効にします。これにより、[すべての値を選択] の入力が有効になります。 [すべての値を選択] を編集するか、既定値をメモします。

    Screenshot that shows Select all for an M parameter.

    [すべての値を選択] は、定義した値を含むリストとしてパラメーターに渡されます。 このため、この値を定義するか、既定値を使用する場合は、値が一意であり、パラメーターにバインドされたフィールドには存在しないことを確認してください。

  2. Power Query エディターを起動し、クエリを選択して、[詳細エディター] を選択します。 [すべての値を選択] を使用して [すべてを選択] オプションを参照するように M クエリを編集します。

    Screenshot that shows an M query.

  3. [詳細エディター] で、パラメーターが [複数選択] に対して有効になっており、[すべての値を選択] が含まれている場合に true と評価され、それ以外の場合は false を返すブール式を追加します。

    Screenshot that shows an example Boolean expression for Select all.

  4. [すべて選択] のブール式の結果をソース クエリに組み込みます。 この例では、前の手順で取得したブール式の結果に設定された includeAllCountries というソース クエリに、ブール型のクエリ パラメーターがあります。 このパラメーターは、クエリのフィルター句で使用できます。たとえば、ブール値が false の場合、選択された国または地域の名前にフィルターが適用され、true の場合は実質的にフィルターが適用されません。

    Screenshot that shows the Select all Boolean used in the source query.

  5. 新しい [すべての値を選択] を考慮するように M クエリを更新すると、スライサーまたはフィルターで Select all 関数を使用できるようになります。

    Screenshot that shows Select all in a slicer.

参考までに、前の例の完全なクエリを次に示します。

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 Data Explorer には、インジェクション攻撃から保護するように設計された組み込みのクエリ パラメーター機能があります。

これらの軽減方法の例をいくつか次に示します。

  • 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 クエリ パラメーターを使用する場合、いくつかの考慮事項と制限事項があります。

  • 1 つのパラメーターを複数のフィールドにバインドすることはできず、その逆もできません。
  • 動的 M クエリ パラメーターでは、集計はサポートされていません。
  • 動的 M クエリ パラメーターでは、行レベル セキュリティ (RLS) はサポートされていません。
  • パラメーター名は、Data Analysis Expressions (DAX) の予約語にすることも、スペースを含めることもできません。 パラメーター名の末尾に Parameter を追加して、この制限を回避することができます。
  • テーブル名にスペースまたは特殊文字を含めることはできません。
  • パラメーターが Date/Time データ型である場合、M クエリ内で DateTime.Date(<YourDateParameter>) としてキャストする必要があります。
  • SQL ソースを使用する場合、パラメーター値が変更されるたびに確認ダイアログが表示されることがあります。 このダイアログは、セキュリティ設定 [新しいネイティブ データベース クエリには、ユーザーの承認が必要] が原因です。 Power BI Desktop の [オプション] の [セキュリティ] セクション内でこの設定を確認し、オフにすることができます。
  • Excel でセマンティック モデルにアクセスするときに、動的 M クエリ パラメーターが機能しない場合があります。
  • 動的 M クエリ パラメーターは、Power BI Report Server ではサポートされていません。

すぐに使用できるパラメーターの型でサポートされていないもの

  • Any
  • Duration
  • True/False
  • Binary

サポートされていないフィルター

  • 相対時間スライサーまたはフィルター
  • 相対日付
  • 階層スライサー
  • 複数フィールドの一致フィルター
  • 除外フィルター / Not フィルター
  • クロス強調表示
  • ドリルダウン フィルター
  • クロス詳細フィルター
  • 上位 N のフィルター

サポートされていない操作

  • および
  • 内容
  • より小さい
  • より大きい
  • 次で始まる
  • 次で始まらない
  • 次ではない
  • [次の値を含まない]
  • [空白である]
  • 空白でない

関連するコンテンツ

Power BI Desktop の機能の詳細については、次のリソースを参照してください。