Parâmetros de consulta Dynamic M no Power BI Desktop

Este artigo descreve como criar e trabalhar com parâmetros de consulta M dinâmicos no Power BI Desktop. Com parâmetros de consulta M dinâmicos, os autores de modelos podem configurar os valores de filtro ou segmentação de dados que os visualizadores de relatório podem usar para um parâmetro de consulta M. Os parâmetros de consulta M dinâmicos dão aos autores de modelos mais controle sobre as seleções de filtros a serem incorporadas nas consultas de origem do DirectQuery.

Os autores de modelos entendem a semântica pretendida de seus filtros e, muitas vezes, sabem como escrever consultas eficientes em sua fonte de dados. Com parâmetros de consulta M dinâmicos, os autores do modelo podem garantir que as seleções de filtros sejam incorporadas às consultas de origem no ponto certo para alcançar os resultados pretendidos com o melhor desempenho. Os parâmetros de consulta Dynamic M podem ser especialmente úteis para a otimização do desempenho da consulta.

Veja Sujata explicar e usar parâmetros de consulta M dinâmicos no vídeo a seguir e, em seguida, experimente-os você mesmo.

Nota

Este vídeo pode usar versões anteriores do Power BI Desktop ou do serviço do Power BI.

Pré-requisitos

Para trabalhar com esses procedimentos, você deve ter uma consulta M válida que use uma ou mais tabelas DirectQuery.

Criar e usar parâmetros dinâmicos

O exemplo a seguir passa um único valor para um parâmetro dinamicamente.

Adicionar parâmetros

1. No Power BI Desktop, selecione Página Inicial>Transformar dados>Transformar dados para abrir o Editor do Power Query.

2. No Editor do Power Query, selecione Novos Parâmetros em Gerir Parâmetros no friso.

   

3. Na janela Gerenciar parâmetros, preencha as informações sobre o parâmetro. Para obter mais informações, consulte Criando um parâmetro.

   

4. Selecione Novo para adicionar mais parâmetros.

   

5. Quando terminar de adicionar parâmetros, selecione OK.

Referenciar os parâmetros na consulta M

1. Depois de criar os parâmetros, você pode fazer referência a eles na consulta M. Para modificar a consulta M, enquanto tiver a consulta selecionada, abra o Editor Avançado.

   

2. Faça referência aos parâmetros na consulta M, conforme destacado em amarelo na imagem a seguir:

   

3. Quando terminar de editar a consulta, selecione Concluído.

Criar tabelas de valores

Crie uma tabela para cada parâmetro com uma coluna que forneça os valores possíveis disponíveis para serem definidos dinamicamente com base na seleção de filtros. Neste exemplo, você deseja que os StartTime parâmetros e EndTime sejam dinâmicos. Como esses parâmetros exigem um Date/Time parâmetro, você gera as entradas possíveis para definir dinamicamente a data para o parâmetro.

1. Na faixa de opções do Power BI Desktop, em Modelagem, selecione Nova tabela.

   

2. Crie uma tabela para os valores do StartTime parâmetro, por exemplo:

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

   

3. Crie uma segunda tabela para os valores do EndTime parâmetro, por exemplo:

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

   

   Nota

   Use um nome de coluna que não esteja em uma tabela real. Se você usar o mesmo nome de uma coluna de tabela real, o valor selecionado será aplicado como um filtro na consulta.

Vincular os campos aos parâmetros

Agora que você criou as tabelas com os Date campos, pode vincular cada campo a um parâmetro. Vincular um campo a um parâmetro significa que, à medida que o valor do campo selecionado muda, o valor passa para o parâmetro e atualiza a consulta que faz referência ao parâmetro.

1. Para vincular um campo, no modo de exibição Modelo do Power BI Desktop, selecione o campo recém-criado e, no painel Propriedades, selecione Avançado.

   Nota

   O tipo de dados da coluna deve corresponder ao tipo de dados do parâmetro M.

   

2. Selecione a lista suspensa em Vincular ao parâmetro e selecione o parâmetro que você deseja vincular ao campo:

   

   Como este exemplo serve para definir o parâmetro como um único valor, mantenha Multi-select definido como No, que é o padrão:

   

   Se você definir a coluna mapeada como Não para Seleção múltipla, deverá usar um único modo de seleção na segmentação de dados ou exigir uma única seleção no cartão de filtro.

   Se os casos de uso exigirem a passagem de vários valores para um único parâmetro, defina o controle como Sim e verifique se a consulta M está configurada para aceitar vários valores. Aqui está um exemplo para RepoNameParametero , que permite vários valores:

   

3. Repita estas etapas se tiver outros campos para vincular a outros parâmetros.

   

Agora você pode fazer referência a este campo em uma segmentação de dados ou como um filtro:

Ativar Selecionar tudo

Neste exemplo, o modelo do Power BI Desktop tem um campo chamado País, que é uma lista de países/regiões vinculados a um parâmetro M chamado countryNameMParameter. Este parâmetro está habilitado para Seleção múltipla, mas não está habilitado para Selecionar tudo. Para poder usar a opção Selecionar tudo em uma segmentação de dados ou cartão de filtro, execute as seguintes etapas adicionadas:

Para ativar Selecionar tudo para País:

1. Nas propriedades avançadas de País, ative a opção Selecionar tudo, que habilita a entrada de valor Selecionar tudo. Edite o valor Selecionar tudo ou anote o valor padrão.

   

   O valor Select all passa para o parâmetro como uma lista que contém o valor definido. Portanto, ao definir esse valor ou usar o valor padrão, verifique se o valor é exclusivo e não existe no campo vinculado ao parâmetro.

2. Inicie o Editor do Power Query, selecione a consulta e, em seguida, selecione Editor Avançado. Edite a consulta M para usar o valor Selecionar tudo para fazer referência à opção Selecionar tudo .

   

3. No Editor Avançado, adicione uma expressão booleana que avalie true se o parâmetro está habilitado para Multi-select e contém o valor Select all e, caso contrário, retorna false:

   

4. Incorpore o resultado da expressão Select all Boolean na consulta de origem. O exemplo tem um parâmetro de consulta booleano na consulta de origem chamada includeAllCountries que é definido como o resultado da expressão booleana da etapa anterior. Você pode usar esse parâmetro em uma cláusula de filtro na consulta, de modo que false para os filtros booleanos para os nomes de país ou região selecionados, e true efetivamente não aplica nenhum filtro.

   

5. Depois de atualizar sua consulta M para levar em conta o novo valor Selecionar tudo, você pode usar a função Selecionar tudo em segmentações de dados ou filtros.

   

Para referência, aqui está a consulta completa para o exemplo anterior:

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"

Potencial risco de segurança

Os leitores de relatórios que podem definir dinamicamente os valores para parâmetros de consulta M podem acessar mais dados ou disparar modificações no sistema de origem usando ataques de injeção. Essa possibilidade depende de como você referencia os parâmetros na consulta M e quais valores você passa para os parâmetros.

Por exemplo, você tem uma consulta Kusto parametrizada construída da seguinte maneira:

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

Não há problemas com um usuário amigável que passa um valor apropriado para o parâmetro, por exemplo, Games:

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

No entanto, um invasor pode passar um valor que modifica a consulta para obter acesso a mais dados, por exemplo: 'Games'//

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

Neste exemplo, o invasor pode obter acesso a informações sobre jogos que ainda não foram lançados alterando parte da consulta em um comentário.

Mitigar o risco

Para reduzir o risco de segurança, evite a concatenação de cadeia de caracteres de valores de parâmetros M na consulta. Em vez disso, consuma esses valores de parâmetro em operações M que se dobram para a consulta de origem, para que o mecanismo M e o conector construam a consulta final.

Se uma fonte de dados oferecer suporte à importação de procedimentos armazenados, considere armazenar sua lógica de consulta lá e invocá-la na consulta M. Como alternativa, se disponível, use um mecanismo de passagem de parâmetros integrado à linguagem de consulta de origem e aos conectores. Por exemplo, o Azure Data Explorer tem recursos internos de parâmetros de consulta projetados para proteger contra ataques de injeção.

Eis alguns exemplos destas atenuações:

• Exemplo que usa as operações de filtragem da consulta M:

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

• Exemplo que declara o parâmetro na consulta de origem ou passa o valor do parâmetro como uma entrada para uma função de consulta de origem:

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

• Exemplo de chamada direta de um procedimento armazenado:

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

Considerações e limitações

Há algumas considerações e limitações a serem levadas em conta ao usar parâmetros de consulta M dinâmicos:

• Um único parâmetro não pode ser vinculado a vários campos nem vice-versa.
• Os parâmetros de consulta Dynamic M não suportam agregações.
• Os parâmetros de consulta Dynamic M não suportam segurança em nível de linha (RLS).
• Os nomes de parâmetros não podem ser palavras reservadas DAX (Data Analysis Expressions) nem conter espaços. Você pode acrescentar Parameter ao final do nome do parâmetro para ajudar a evitar essa limitação.
• Os nomes das tabelas não podem conter espaços ou caracteres especiais.
• Se o parâmetro for o Date/Time tipo de dados, você precisará convertê-lo na consulta M como DateTime.Date(<YourDateParameter>).
• Se você usar fontes SQL, poderá obter uma caixa de diálogo de confirmação sempre que o valor do parâmetro for alterado. Esta caixa de diálogo é devido a uma configuração de segurança: Exigir aprovação do usuário para novas consultas de banco de dados nativo. Você pode encontrar e desativar essa configuração na seção Segurança das Opções do Power BI Desktop.
• Os parâmetros de consulta M dinâmicos podem não funcionar ao acessar um modelo semântico no Excel.
• Os parâmetros de consulta Dynamic M não são suportados no Servidor de Relatório do Power BI.
• Não há suporte para alternar fontes de dados usando parâmetros de consulta M dinâmicos no serviço do Power BI. Consulte fontes de dados dinâmicas e de atualização para obter informações adicionais.

Tipos de parâmetros prontos para uso não suportados

• Qualquer
• Duração
• True/False
• Binário

Filtros não suportados

• Segmentação de dados ou filtro de tempo relativo
• Data relativa
• Segmentação de dados hierárquica
• Filtro de inclusão de vários campos
• Excluir filtros / Não filtros
• Realce cruzado
• Filtro detalhado
• Filtro de perfuração cruzada
• Filtro Top N

Operações sem suporte

• And
• Contains
• Menor que
• Maior que
• Começa com
• Não começa com
• Não é
• Não contém
• Está em branco
• Não está em branco

Conteúdos relacionados

Para obter mais informações sobre os recursos do Power BI Desktop, confira os seguintes recursos:

• DirectQuery no Power BI
• O que é o Power BI Desktop?
• Descrição geral do Query no Power BI Desktop
• Tipos de dados no Power BI Desktop
• Tutorial: Formatar e combinar dados no Power BI Desktop
• Executar tarefas de consulta comuns no Power BI Desktop