Parámetros de consulta M dinámicos en Power BI Desktop

Con los parámetros de consulta M dinámicos, los autores de modelos pueden permitir que los visores de informes usen filtros o segmentaciones para establecer los valores de un parámetro de consulta M, lo que puede ser especialmente útil para las optimizaciones del rendimiento de las consultas. Con los parámetros de consulta M dinámicos, los autores de modelos tienen más control sobre cómo se incorporan las selecciones de filtro en las consultas de origen de DirectQuery.

Cuando los autores del modelo entienden la semántica prevista de sus filtros, a menudo saben cómo escribir consultas eficaces en su origen de datos y, por tanto, pueden garantizar que las selecciones de filtro se incorporen en las consultas de origen en el punto adecuado para lograr los resultados deseados con un mejor rendimiento.

Vea Explicar y usar parámetros de consulta M dinámicos y pruébelo usted mismo.

Nota

Es posible que en este vídeo se usen versiones anteriores de Power BI Desktop o del servicio Power BI.

Creación de parámetros de consulta M dinámicos

Como requisito previo para esta característica, debe haber creado un parámetro de consulta M válido al que se haga referencia en una o varias tablas de Direct Query.

Vamos a examinar un ejemplo acerca del paso de un valor único a un parámetro dinámicamente:

  1. En Power BI Desktop, seleccione Inicio>Transformar datos>Transformar datos para abrir el Editor de Power Query. Seleccione Nuevos parámetros debajo del botón Administrar parámetros de la cinta de opciones.

    Ribbon menu

  2. A continuación, rellene la siguiente información sobre el parámetro.

    Parameter information

  3. Vuelva a hacer clic en Nuevo si tiene más parámetros que agregar.

    Create another parameter

  4. Una vez que haya creado los parámetros, puede hacer referencia a ellos en la consulta M. Para modificar la consulta M, abra el editor avanzado mientras tiene seleccionada la consulta que desea modificar:

    open Advanced Editor

  5. A continuación, haga referencia a los parámetros de la consulta M, resaltados en amarillo en la siguiente imagen.

    Reference the parameter

  6. Ahora que ha creado los parámetros y ha hecho referencia a ellos en la consulta M, tendrá que crear una tabla con una columna que proporcione los posibles valores disponibles para ese parámetro. Esto permitirá que los parámetros se establezcan dinámicamente en función de la selección del filtro. En este ejemplo, queremos que los parámetros StartTime y EndTime sean dinámicos. Dado que estos parámetros requieren un parámetro de fecha y hora, deseo generar entradas de fecha que se pueden usar para establecer la fecha del parámetro. Para empezar, creamos una nueva tabla:

    Create a new table

  7. Esta es la primera tabla que creé para los valores del parámetro StartTime:

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

    create a table

  8. Esta es la segunda tabla que creé para los valores del parámetro EndTime:

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

    create the second table

    Nota

    Se recomienda usar un nombre de columna diferente que no esté en una tabla real. Si tienen el mismo nombre, el valor seleccionado se aplicará como filtro a la consulta real.

  9. Ahora que se han creado las tablas con el campo Date, podemos enlazar cada campo a un parámetro. Enlazar el campo a un parámetro significa esencialmente que a medida que cambia el valor seleccionado para el campo, el valor se pasará al parámetro y se actualizará la consulta donde se hace referencia al parámetro. Para enlazar el campo, vaya a la pestaña Modelado, seleccione el campo recién creado y, a continuación, vaya a las Propiedades avanzadas:

    Nota

    El tipo de datos de la columna debe coincidir con el tipo de parámetro de M.

    bind the field to a parameter

  10. Seleccione la lista desplegable en Bind to parameter (Enlazar al parámetro) y seleccione el parámetro que desea enlazar al campo:

    bind the parameter to the field

    Dado que este ejemplo es para un valor de selección única (estableciendo el parámetro en un valor único), querrá mantener Selección múltiple en No, que es el valor predeterminado:

    multi-select set to off

    Si los casos de uso requieren selección múltiple (paso de varios valores a un solo parámetro), debe cambiar el modificador a y asegurarse de que la consulta M está configurada correctamente para aceptar varios valores en la consulta M. Este es un ejemplo de RepoNameParameter, que permite varios valores:

    multi-value example

  11. Puede repetir estos pasos si tiene otros campos para enlazar a otros parámetros:

    configure additional parameters

  12. Por último, puede hacer referencia a este campo en una segmentación o como un filtro:

    reference the fields

Si la columna asignada se establece en No para la selección múltiple, debe usar un modo de selección única en la segmentación o requerir una selección única en la tarjeta de filtro.

Habilitación de Seleccionar todo

Hay pasos adicionales para que los usuarios finales puedan usar la opción Seleccionar todo de la tarjeta de segmentación o filtrado. Vamos a usar el siguiente escenario como ejemplo. En la pestaña Modelo de Power BI Desktop, puede ver que hay un campo de nombre País (lista de países) enlazado a un parámetro M denominado countryNameMParameter:

Example of multi-select M Parameter

También puede ver que este parámetro tiene habilitada la Selección múltiple, pero no la opción Seleccionar todo. Al habilitar el botón de alternancia Seleccionar todo, se ve una entrada habilitada de nombre Valor Seleccionar todo:

Select all settings for M parameter

El valor Seleccionar todo se usa para referirse a la opción Seleccionar todo de la consulta M. Este valor se pasa al parámetro como una lista que contiene el valor definido de Seleccionar todo. Por lo tanto, cuando defina este valor o use el predeterminado, tiene que asegurarse de que es único y no existe en el campo enlazado al parámetro. Una vez que haya establecido el valor o usado el predeterminado para Seleccionar todo, tiene que actualizar la consulta M para tener en cuenta este valor Seleccionar todo.

M query screenshot

Para editar la consulta M, primero debe iniciar el Editor de Power Queryy luego seleccionar Editor avanzado en la sección Consulta:

Advanced editor entry point in ribbon

En el Editor avanzado es necesario agregar una expresión booleana que se evalúe en true si el parámetro tiene habilitada la Selección múltiple y contiene el valor Seleccionar todo (de lo contrario, devuelve false). En este ejemplo, este sería el aspecto:

Example Boolean expression for Select all

A continuación, es necesario incorporar el resultado de esta expresión booleana de Seleccionar todo a la consulta de origen. En este ejemplo hay un parámetro de consulta booleano en la consulta de origen denominado includeAllCountries que se establece en el resultado de la expresión booleana del paso anterior. A continuación, se usa este parámetro en una cláusula de filtro de la consulta, de modo que false en el valor booleano filtre por los nombres de país seleccionados y true no aplique en realidad ningún filtro:

Select all Boolean used in Source query

Como referencia, esta es la consulta completa que se ha usado:

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"

Una vez que haya actualizado la consulta M para tener en cuenta este nuevo valor Seleccionar todo, ya puede usar la función Seleccionar todo en segmentaciones o filtros:

Select all in slicer

Riesgo potencial de seguridad

Cuando permite que los lectores de informes establezcan dinámicamente los valores de los parámetros de consulta M, es posible que puedan tener acceso a datos adicionales o desencadenar modificaciones en el sistema de origen mediante ataques de inyección, en función de cómo se haga referencia a los parámetros en la consulta M y qué valores se pasen a ese parámetro.

Por ejemplo, supongamos que tiene una consulta parametrizada Kusto construida como la siguiente:

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

Es posible que no tenga ningún problema con un usuario sin malas intenciones que pase un valor adecuado para el parámetro, por ejemplo Games:

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

Sin embargo, es posible que un atacante pueda pasar un valor que modifique la consulta para obtener acceso a más datos, por ejemplo, 'Games' // :

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

En este ejemplo, el atacante puede obtener acceso a información sobre los juegos que todavía no se ha publicado cambiando parte de la consulta a un comentario.

Mitigación del riesgo

Para mitigar el riesgo de seguridad, es mejor evitar la concatenación de cadenas de valores de parámetros de M dentro de la consulta. En su lugar, utilice esos valores de parámetro en operaciones de M que se plieguen en la consulta de origen, de modo que el conector y el motor de M construyan la consulta final. O bien, si está disponible, haga uso de un mecanismo de paso de parámetros integrado en el lenguaje de consulta de origen y los conectores. Por ejemplo, Azure Data Explorer tiene capacidades de parámetro de consulta integradas que están diseñadas para protegerse frente a ataques por inyección.

A continuación se muestran algunos ejemplos:

  • Ejemplo de uso de las operaciones de filtrado de consulta M:

    Table.SelectRows(Source, (r) =\&gt; r[Columns] = Parameter)
    
  • Ejemplo de declaración del parámetro en la consulta de origen (o paso del valor del parámetro como entrada a una función de consulta de origen):

    declare query\_parameters (Name of Parameter : Type of Parameter);
    

Consideraciones y limitaciones

Hay algunas consideraciones y limitaciones que se deben tener en cuenta al usar parámetros de consulta M dinámicos:

  • Un parámetro único no se puede enlazar a varios campos ni viceversa.

  • La característica no admite las agregaciones.

  • La seguridad de nivel de fila (RLS) no se admite con la característica

  • Los nombres de parámetro no pueden ser palabras reservadas de DAX ni contener espacios. La anexión de "Parámetro" al final del nombre del parámetro puede ayudar a evitar esta limitación.

  • Si el parámetro es del tipo de datos Fecha y hora, deberá convertirlo dentro de la consulta M como DateTime.Date(<YourDateParameter>)

  • Si usa orígenes de SQL, puede observar un cuadro de diálogo de confirmación cada vez que cambia el valor del parámetro. Esto se debe a una configuración de seguridad: requerir la aprobación del usuario para las nuevas consultas de base de datos nativas. Puede encontrar y desactivar esta configuración en la pestaña Seguridad del cuadro de diálogo Opciones en Power BI Desktop.

  • Los tipos de parámetros no integrados son los siguientes:

    • Any
    • Duration
    • Verdadero/Falso
    • Binary
  • Filtros no admitidos:

    • Filtro o segmentación de tiempo relativo
    • Fecha relativa
    • Segmentación de jerarquía
    • Filtro de inclusión de varios campos
    • Filtro de exclusión/ausencia de filtros
    • Resaltado cruzado
    • Filtro de exploración en profundidad
    • Filtro de exploración cruzada
    • N filtro principal
  • Operaciones no admitidas:

    • And
    • Contiene
    • Menor que
    • Mayor que
    • Empieza por
    • No empieza con
    • No es
    • No contiene
    • Está en blanco
    • No está en blanco

Pasos siguientes

Puede hacer todo tipo de cosas con Power BI Desktop. Para obtener más información sobre sus capacidades, consulte los siguientes recursos: