Home

Automatización de las actualizaciones de dataflows y datasets

A principios de octubre, el genio de los dataflows Matthew Roche publicaba en su blog que ya estaba disponible la posibilidad de automatizar el refresco de los datasets dependientes de dataflows cuando terminara la actualización de los dataflows.

Esto se debía a una modificación en la REST API de Power BI que propiciaba un end-point para ello.

Pero el mismo Matthew indicaba en su post que la única forma que había disponible de momento era a través del código de powershell que habían creado y que el equipo de desarrollo de Microsoft está trabajando en poder facilitar a los usuarios la automatización de ese proceso, aunque aun no hay fecha oficial para este desarrollo.

Me puse entonces a investigar y trabajar con el script de powershell para intentar modificarlo en el sentido de poder automatizar ese proceso sin tener que abrir powershell y lo he conseguido.

Vayamos a su explicación, desde el principio.

1.      Descarga de los cmdlets de powershell

Lo primero que tenemos que hacer es tener preparado nuestro powershell para realizar el proceso y verificar que funciona perfectamente a través de powershell.

Todos los componentes e infinidad de ejemplos podemos encontrarlos en Git Hub en el siguiente repositorio:

https://github.com/microsoft/powerbi-powershell

Instalaremos todos los módulos de powershell para power bi:

La forma de instalarlos es muy simple:

1º Abrimos powershell en nuestra máquina en modo administrador

2º se nos abre la siguiente consola:

3º En el repositorio de github, vamos pinchando en cada uno de los links de la galería de powershell, copiando el código para su instalación y ejecutándolo en la consola:

Así con cada uno de los módulos.

Cuando terminamos de instalar todos podemos listarlos para comprobar que los tenemos bien con el siguiente comando:

Get-Module MicrosoftPowerBIMgmt* -ListAvailable

Y nos devolverá el siguiente listado:

Todo listo en cuanto a instalación de componentes genéricos del cmdlet de power Shell para la API rest de Power BI

2.      Instalación de los componentes específicos de Powershell para dataflows.

En el mismo repositorio de GitHub tenemos los componentes específicos para la actualización de los dataflow que vamos a necesitar y tendremos que descargárnoslos a nuestro equipo a través del siguiente enlace:

https://github.com/microsoft/powerbi-powershell/tree/master/examples/dataflows

Descargamos todos los ficheros existentes en ese repositorio y los copiamos en la carpeta c:\windows\system32

Esto nos va a permitir directamente poder ejecutar en Powershell el proceso de actualización como muestro en el siguiente mini video:

El primer reto ya está logrado. Actualizarlo a través de PowerShell.

Si esto nos funciona perfectamente como en el vídeo ya estamos preparados para saltar al paso 2 que es crear un archivo de power bi que haga automáticamente todo este proceso para que no tengamos que abrir en ningún momento la consola de powershell para su ejecución.

Para ello el primer reto que tenía que solventar era la eliminación del cuadro de diálogo de la autentificación que salta en powershell ya que si lo que queremos es automatizarlo, no podemos estar pendientes de poner la contraseña, sino que debe estar contemplada directamente en el código.

Esto es relativamente sencillo de realizar con el siguiente código que deberemos incluir en el script:

$password = «XXXXNuestroPasswordXXXX» | ConvertTo-SecureString -asPlainText -Force

$username = «XXXnuestroususarioPowerBIXXX»

$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential

Si ejecutamos ese código en la consola, directamente nos loguea PowerShell en nuestro servicio de PowerBI

Viendo que funciona, simplemente tendremos que hallar el lugar adecuado donde ponerlo en nuestro archivo RefreshModel.ps1. Esto no me fue fácil ya que mis conocimientos de Powershell son limitados, pero al final lo logré:

Simplemente hay que cambiar la línea del código original del archivo que pone:

LoginPowerBi($Enviroment) por nuestro código:

En la parte del código de Process:

                $password = «Nuestropassword» | ConvertTo-SecureString -asPlainText -Force

                $username = «nuestrousuario»

                $credential = New-Object System.Management.Automation.PSCredential($username, $password)

                Connect-PowerBIServiceAccount -Credential $credential

Por tema de permisos no nos dejará editarlo directamente en la carpeta System32 por lo que tendremos que copiarlo a otra carpeta, modificarlo y sustituirlo en la carpeta system32. En todo caso os dejaré un archivo con los archivos finales para que solo tengáis que introducir usuario y contraseña.

Ejecutamos de nuevo la sentencia que probamos en el video para comprobar que funciona sin necesidad de introducir las credenciales:

Pues ya solo nos queda crear un archivo pbix que sea capaz de ejecutar la sentencia de powershell sin abrir powershell. Para ello recurriremos a un script de R

Veamos cómo crearlo con RStudio

Ya tenemos nuestro script de R listo, pues simplemente nos lo llevamos a power BI para crear un archivo pbix que nos sirva para llamar a powershell y ejecutar la actualización.

Éste va a ser el código resultante de ejecución de R que tenemos que pegar en M simplemente cambiando “Pruebas Fran” por vuestro workspace y “ConsolidacionFehas” por vuestro dataset.

let
    Origen = R.Execute("actualizar<- system('powershell -command ""RefreshModel.ps1 -Workspace """"""Pruebas Fran"""""" -Dataflow """"""ConsolidacionFechas"""""" -RefreshDatasets""', intern=FALSE)#(lf)datos <- data.frame(""OK"")"),
    datos = Origen{[Name="datos"]}[Value],
    #"Personalizada agregada" = Table.AddColumn(datos, "Fecha Ultima Actualizacion", each DateTime.LocalNow()),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Personalizada agregada",{{"Fecha Ultima Actualizacion", type datetime}})
in
    #"Tipo cambiado"

Le he agregado una columna con la fecha del sistema para saber cuando hemos ejecutado la actualización.

Subimos este PBix al servicio y cada vez que lo actualicemos, nos iniciará el proceso de actualización 1º del dataflow y después de los datasets dependientes.

NOTA:  Al ser un script de R, para que actualice debemos crear una puerta de enlace personal en la misma máquina que tengamos R instalado y powershell con todos los componentes que en este post he ido explicando que teníamos que instalar.

El resultado final es el video que ayer colgué y como veis no tenía trampa ni cartón.

Materiales:

Contiene todos los archivos que debéis pegar en la carpeta c./windows/system32 En el archivo RefreshModel.ps1 es necesario agregar la contraseña y el usuario de power bi que aparece como XXXTUCONTRASEÑAXXX y XXXUSUARIOXXX.

Consolidación de tablas de calendario en Dataflows

Como todos sabéis todos mis proyectos de Power BI los desarrolo a partir de ETL en Dataflows. Hasta ahora siempre utilizaba mis tablas de calendario, tiempo y período en DAX, pero he decidido consolidarlas en Dataflow para así poder comenzar todos mis proyectos a partir de las tablas de Fechas, Períodos y Tiempo consolidadas en un dataflow.

Qué ventajas nos va a proporcionar esto, pues principalmente que cada mejora o cambio que hagamos en nuestras tablas dimensionales de tiempo, calendario o períodos podremos tenerlas automáticamente reflaejadas en todos nuestros datasets sin necesidad de tener que ir uno a uno modificándolos.

Os adjunto el archivo .json que simplemente importándolo a un flujo de datos podreis disponer para vuestros proyectos de:

1º Una tabla de fechas estandarizada con las columnas más relevantes y como no los desvíos.

2º Una tabla de Período completa con los períodos más relevantes siguiendo la técnica de Chris Webb para su creación en M

  • Hoy
  • Ayer
  • Semana actual hasta hoy
  • Semana actual
  • Mes actual hasta hoy
  • Mes actual
  • Mes anterior
  • Año actual hasta hoy
  • Año actual
  • Año anterior
  • Últimos 7 días
  • Últimos 30 días
  • Últimos 365 días.

3º La dimensión tiempo al minuto

4º La dimensión tiempo al segundo.

Os recomiendo para conocer como debe tratarse la dimensión tiempo en nuestros modelos un magnífico webinar que mi compañero en Quolutions Víctor Lozano impartió para Power Platform España:

Aquí os dejo el archivo .json con el que podréis crear simplemente importándolo el dataflow que consolida todas estas tablas y que podréis modificar a vuestro gusto.

Es necesario renombrar el archivo a .json

Y para saber como funciona todo el tema de calendarios en Power BI os recomiendo, como no mi serie de Inteligencia Temporal donde hay varios vídeos que os ayudarán si queréis vosotros mismos crear vuestros propios calendarios.

Sábados de Power BI (II)

Hoy tuve el placer de hablar durante dos horas en este evento que organizamos 1 vez al mes sobre uno de los temas que más me gusta de Power BI. Los flujos de datos como herramienta de ETL.

Os dejo el video para quienes no os hayáis podido conectar.

Materiales recomendados sobre dataflows:

Documentación oficial Microsoft

https://docs.microsoft.com/es-es/power-bi/transform-model/dataflows/dataflows-create

El gran Mathew Roche

Y Reza Rad en Radacad

https://radacad.com/dataflow-vs-dataset-what-are-the-differences-of-these-two-power-bi-components

Drill through en tarjetas y simulándolo en botones

No soy mucho de postear sobre visualizaciones, pero esto si me parece bastante interesante ya que me he topado con ello recientemente en un requerimiento de negocio.

Desde la actualización de septiembre se puede hacer drill through (obtención de detalles) en las tarjetas utilizando para ello medidas en el drill en lugar de dimensiones.

Esta actualización ha pasado bastante desapercibida aunque la mencionó Daniil Maysluk en su blog:

https://xxlbi.com/blog/card-drill-through-power-bi/

El método es sencillo, en tu página de detalle introduces en la «Obtención de detalles la misma medida que introduces en la tarjeta y te va a aparecer la opción en la tarjeta de obtener detalles.

Nótese que por defecto aparecerá marcado «Mantener todos los filtros» y no se podrá desactivar, porque nos va a traer a la página de detalle todos los filtros que afecten a la tarjeta.

En el ejemplo siguiente hago drill marcando un filtro en un slicer (Francia) y un filtro en el gráfico circular «Bikes»

Y aterrizamos en la página de detalle con los filtros de la página anterior:

Pero hace unos días un cliente me dijo que los usuarios no terminaban de acostumbrarse a la opción del drill que si no existía la posibilidad de acceder a la página de detalle mediante un botón y que el botón informase del total de ventas que iban a ver en el detalle. Pues efectivamente podemos hacer drill con un botón y sobre una medida por dos vías diferentes y cada una tiene sus limitaciones.

Vía 1. Desde hace un tiempo se puede ir hacia la página de detalle directamente a través de un botón. La desventaja que tiene esta opción es que necesitas un doble clic, primero tienes que marcar un filtro que afecte al drill y luego apretar el botón. En el caso que nos ocupa, drill sobre una medida, cualquier filtro que marquemos en el canvas activará el drill, pero no podremos ir a la pñagina de detalles sin establecer ningún filtro que lo active. Veamos visualmente como se configura:

1º Insertamos un botón

2º En la acción del botón seleccionamos «Obtener detalles» y en el destino «Ventas Detalle Drill»

El handicap de este botón es que sólo se activa cuando hay un filtro marcado en una visual. No vale un filtro de un slicer

Deshabilitado
Habilitado al seleccionar un mes de la visual

Esta vía por tanto podríamos utilizarla siempre que quisiéramos ver el detalle de cualquier elemento dimensional al filtrarlo.

VIA 2: Sin embargo si quisiéramos ver el detalle de datos segmentados por slicers yo optaría por una segunda vía que no es el drill through en una tarjeta, sino que lo vamos a simular mediante un botón.

Prescindimos de la tarjeta y de la página de detalle con el filtro en la medida venta. Simplemente creamos una página de detalle en la que sincronizamos todos los slicers que tengamos activos en la página principal. En este caso vamos a sincronizar el slicer del país.

Simulamos ahora la tarjeta con un botón y en el texto del botón vamos a seleccionar una medida creada para ese botón formateando a texto la medida que queremos que represente el botón. En este caso, la tarjeta contenía la medida de Ventas, pues creamos la medida txt_Ventas con la siguiente sintaxis:

txt_Ventas=   FORMAT([Ventas],"#,### €")
Creamos el botón
En el texto del botón seleccionamos la fx

Y elegimos nuestra medida txt_ventas

En la acción de nuestro botón le indicamos que navegue hacia la página de detalle en donde tenemos todos los filtros de la página inicial sincronizados.

De esta manera si elegimos el país Francia, por ejemplo, nos va a informar del total de ventas en Francia en el botón que simula la tarjeta y al pulsar el botón nos llevará a las ventas de Francia:

Sin embargo, el handicap de esta solución es que si filtramos mediante cualquier visual, por ejemplo Bikes

Nos va a mostrar en la tarjeta las ventas sólo Bikes, pero al pulsar el botón el filtro de Bikes no nos lo mantendrá ya que es un filtro de visual que no se puede propagar mediante este sistema

En el detalle al pulsar en ventas Francia y Bikes no nos mantiene el filtro Bikes que es un filtro en la visual que no se puede sincronizar

Podemos concluir por tanto:

1º Que desde la actualización de septiembre podemos utilizar obtención de detalles sobre una medida a través de una tarjeta.

2º Que la obtención de detalles puede configurarse a través de un botón, pero que para su activación necesitaremos siempre un filtro en una visual

3º Que una tercera vía es prescindir de la tarjeta y del drill y simular la tarjeta en un botón en el que podremos dinamizar la medida, siempre en formato texto y utilizar ese botón para navegar al detalle, pero sin mantener los filtros visuales, aunque si podemos mantener los filtros de los slicers sincronizándolos en ambas páginas

Dataflows admite cuadro de dialogo en la consulta nativa de SQL pero provocando de momento un error crítico si usas parámetros

Lo que esta mañana parecía una grandísima noticia, la llegada de los cuadros de diálogo en las consultas nativas de SQL en dataflows se ha convertido en una auténtica pesadilla para mi.

Está ya en manos del equipo de soporte de Microsoft (que por cierto no tenían ha nadie para atenderme en castellano y ha sido difícil hacerles entender mi problema) y esperemos una pronta solución, pero de momento he encontrado un atajo para parchearlo, aunque me queda un día duro de trabajo cambiando queries.

EL PROBLEMA

Esta mañana me encontraba con una importante novedad que justo ayer en un webinar comentaba que no entendía que no hubieran solucionado, no existía cuadro de diálogo para las consultas nativas en SQL y por fin ya lo hay:

Sin embargo, empezaban a aparecerme correos en mi bandeja de actualizaciones de dataflows que habían fallado esta misma mañana en el proyecto en el que trabajo. Chequeo y algunas consultas nativas habían desaparecido.

Me pongo a profundizar en el problema y no habían desaparecido todas, únicamente aquellas que contenían parámetros.

Si introduces una consulta nativa en el cuadro de dialogo, éste no admite parámetros, pero si podías incluir los parámetros manualmente en M de esta forma:

En el desktop, se rompe el cuadro de diálogo pero se traga el parámetro sin problema

¿Qué pasa en el nuevo cuadro de diálogo en los flujos de datos? Pues simplemente que no rompe el cuadro de diálogo sino que desaparece la Consulta Nativa:

Introduces el parámetro en el código M
La consulta nativa desaparece porque prevalece el cuadro de diálogo que no se rompe no admitiendo el parámetro

Podemos concluir, por tanto que de momento las consultas nativas en dataflows no admiten parámetros.

Solución provisional

Mientras que el equipo de soporte lo soluciona, si lo soluciona, no me queda más remedio que hacer lo siguiente:

a) Convierto la consulta con la query nativa en una función

b) Me traigo el parámetro en un Origen, lo convierto en tabla e invoco la función creada sobre el parámetro

Es una chapuza que de momento em funciona, porque la otra opción es dejar el parámetro fijo y cambiarlo manualmente cada día.

No sé si soy la única persona del mundo a este nivel de uso de dataflows pero para mi ha sido un verdadero problema crítico en mi proyecto

Introducción al scripting de medidas con Tabular Editor. Creación automática de medidas para tarjetas suprimiendo el “en blanco”

Tabular Editor es simplemente apasionante. Sin duda desde la aparición de los marcadores para mi no ha ocurrido nada tan relevante en Power BI como la integración de las herramientas externas, sobre todo por la integración de Tabular Editor.

Que sí, que antes podíamos hacer todo esto y no lo hacíamos, totalmente de acuerdo con esta afirmación, pero ahora es que está ahí, mucho más a mano, soportado y mucho más confiable.

Se pueden hacer muchísimas cosas con tabular editor para Power BI pero yo destacaría 3:

  • Automatización de la creación de métricas con scripting
  • Creación de KPIs (los auténticos, los del semáforo de Excel)
  • Creación y mantenimiento de Grupos calculados.

Sobre todos iremos posteando en una serie específica para tabular editor que voy a crear, pero quería empezar por una breve reseña de scripting para solventar un tema muy recurrente, la eliminación del “En blanco” en la visualización de tarjeta.

Hace mucho tiempo que plantee ese problema en mi blog

Después de eso apareció la función COALESCE y ahora mejor que +0 es utilizar el coalesce en la medida = COALESCE([Medida],0), pero como ya planteaba en su día hay que tener mucho cuidado con una medida así ya que si insertamos esa medida en una tabla nos va a devolver valores para todos y cada uno de los elementos dimensionales que estemos analizando, si no tienen valor no aparecerían en la visual pero si forzamos la aparición de 0 aparecería para todos y podría complicar muchísimo el rendimiento de la visualización e incluso darnos errores de memoria y no representarla.

Es por ello que mi consejo siempre es tener una medida normal y una medida que devuelva 0 en caso de blanco para las visuales de tarjeta. Es en este punto cuando cobra sentido la utilización de scripting y de tabular editor para poder crear de forma automática y sin esfuerzo cuantas medidas con 0 queramos.

Yo no domino C# que es el lenguaje que utiliza el scripting de Tabular editor, pero mi buen amigo y compañero del grupo de Telegram @PowerBIEspañol Ricardo Rincón me ayudó con este script y subió el ejemplo que vamos a explicar a su github:

https://github.com/nexus150/Tabular-Editor-Scripts

Este es el código que vamos a analizar y vamos a cambiar en función de mi estilo y necesidad:

// Crear versión de cada medida con coalesce para colocar en tarjetas
foreach (var m in Selected.Measures)
    {
        string medida = m.Name;
        Model.Tables["Tabla"].AddMeasure
(medida + " for card", 
"Coalesce([" +medida+ "],0)", "Coalesce");
    }

Vamos a ver el escenario:

Tengo 4 medidas en mi canvas que me devuelve en las 4 el (En blanco) que tan poco gusta a todo el mundo

Lo que vamos a hacer es crear estas cuatro medidas automáticamente con la fórmula COALESCE(Medida,0). A mi a esa medida me gusta llamarla Lab0_Medida y es como voy a llamarla en el Script pero podéis cambiar en el script para nombrarla como queráis.

El script es el siguiente:

// Crear versión de cada medida con coalesce para colocar en tarjetas

    foreach (var m in Selected.Measures)

        {

            string medida = m.Name;

            Model.Tables[«Medidas»].AddMeasure(«Lab0_»+medida, «Coalesce([« +medida+ «],0)», «Coalesce»)

        ;}
La modificación que he hecho con respecto al código de Ricardo es simplemente a nivel del Nombre de la medida. Podríamos también cambiar el nombre de la carpera «Coalesce» por «Etiquetas0» por ejemplo.

¿Que hacemos con este script?

Pues para cada medida seleccionada antes de la ejecución del script vamos a crear una nueva medida en la tabla Medidas, dentro de la carpeta Coalesce en el que el nombre de la medida va a ser “Lab0”+medidapadre y la métrica va a ser COALESCE([medidaseleccionada],0)

Vamos a verlo paso a paso ya que una imagen vale más que mil palabras….

Partiendo de la imagen primera con las tarjetas en blanco vamos a seguir los siguientes pasos:

Abrimos tabular editor desde las herramientas externas:

Vamos a la pestaña de advanced scripting y pegamos allí el código:

Seleccionamos en la tabla de medidas aquellas medidas que queremos replicar con el COALESCE(medida,0)

Y ejecutamos el script(F5)

Al ejecutar el script se nos crean las medidas en tabular:

Guardamos los cambios (Ctrl+S)

Actualizamos nuestra tabla de Medidas en Power BI:

Y ya tenemos nuestras medidas creadas automáticamente:

Las introducimos en nuestras visuales y listo:

Como podéis observar, conserva el formato de la medida original.
Y por último, podéis guardar el script y utilizarlo en otros modelos sin ningún problema:

Una vez me dijeron, «no hay que saberlo todo, simplemente conocer a quien lo sabe». Muchísimas gracias Ricardo Rincón ya que este aporte es tuyo.

Sábados de Power BI (I). Contextos de evaluación en DAX

Estoy enormemente emocionado por la respuesta que ha tenido esta iniciativa. Casi no he podido ver el número final de asistentes, pero más de 50 personas hemos llegado a alcanzar un sábado por la mañana para hablar de contextos. Gente incluso de LATAM a altas horas de la madrugada allí.

!!MIL GRACIAS!!

Materiales:

Imprescindible ver este video de Marco Russo

https://www.sqlbi.com/tv/deep-dive-into-dax-evaluation-context/

PDF,S sobre el tema:

https://quolutions-my.sharepoint.com/:f:/p/francisco_mullor/EqbBnIyq6RZGvp-AZ_2P8D0B1i3uf9-n-Idko6X8P2sWlA?e=vWm6ES

Enlace proporcionado por Oscar Katsinis sobre ejercicios de contextos:

https://community.powerbi.com/t5/Data-Stories-Gallery/So-You-Think-You-Know-DAX/td-p/325132

Video de la sesión:

Simular un lookupvalue cuando el resultado es más de un valor.

Ante una consulta como siempre en el grupo de Telegram que me pareció bastante atractiva de resolver, estas fueron mis conclusiones sobre la simulación de un LOOKUPVALUE que pueda devolver más de un elemento.

Dice la documentación oficial de LOOKUPVALUE() lo siguiente:

“Si varias filas coinciden con los valores de búsqueda y en todos los casos los valores de result_column son idénticos, se devuelve ese valor. Pero si result_column devuelve valores distintos, se devuelve un error o alternateResult, si se proporciona”

Visto de modo práctico:

Tenemos la siguiente tabla ORIGEN:

Y la siguiente de DESTINO donde queremos usar LOOKUPVALUE para traernos los nombres.

Si creamos una columna calculada en el destino nos ocurre esto:

Nos devuelve un error debido a que el resultado en alguno de los campos es más de un valor.

Modos de solucionarlo:

1º Proponiendo que nos devuelva un resultado alternativo cuando haya error para saltárselo:

Si añadimos “Varios” como resultado alternativo va a mostrarnos Varios en aquellos campos donde el resultado es más de uno.

2º vamos a hacer un poco de Magia con DAX y vamos a simular un LOOKUPVALUE en una columna calculada, pero para que nos devuelva el resultado de todos los elementos concatenados en caso de que haya más de un valor:

Tenemos que crear una tabla virtual con los valores que corresponden para cada tipo que introducimos en una variable

    VAR __TablaValores = CALCULATETABLE( VALUES(LookupValue_Origen[Nombre]) ,

                                TREATAS({Lookupvalue_Destino[Tipo]}, LookupValue_Origen[Tipo])

                                        )

Y devolvemos la concatenación de los elementos de cada tabla:

    RETURN

    CONCATENATEX(__TablaValores,LookupValue_Origen[Nombre], «, «)  

Como lo estamos haciendo en una columna calculada, a nivel de cada línea, va a analizar el tipo de cada una de las líneas y lo va a relacionar gracias a la función TREATAS con todos los valores devueltos para ese tipo que vamos a concatenar con la función CONCATENATEX

Podemos incluso controlar el número de valores que queremos mostrar utilizando la función TOPN. Imaginad que queremos que nos muestre sólo los dos primeros valores y el número de valores que contiene, pues simplemente arreglamos nuestra fórmula de esta manera:

Siempre hay que tener en cuenta en este tipo de columnas su performance, puede no ser óptimo su rendimiento con un número elevado de filas, hay que tener en cuenta que estamos usando dos prácticas poco recomendadas:

1º El uso de columnas calculadas.

2º El uso de relaciones débiles con la función TREATAS

Evitar las entidades calculadas cuando utilizas un flujo de datos como origen de otro flujo de datos.

Mediante este menú podemos agregar entidades vinculadas. Reza en su menú: “La vinculación a entidades de otros flujos de datos reduce la duplicación y ayuda a mantener la coherencia en toda la organización.”

Si pinchamos en él nos lleva directamente a conectar un flujo de datos en un nuevo flujo de datos con el mismo conector que usamos para conectar un flujo de datos a un dataset

Nos logueamos con la cuenta de nuestra organización y podemos conectarnos a otro flujo de datos

Pero nos indica el siguiente mensaje:

Las entidades vinculadas necesitan de una capacidad premium.

Sin embargo, siguiendo la misma premisa de la combinación y la anexión en los flujos de datos que podéis ver en uno de mis post antiguos:

Hoy descubrí casi por casualidad que si deshabilitamos la carga de la entidad vinculada y la referenciamos, la nueva entidad copia de la entidad vinculada deshabilitada no es una entidad vinculada:

Como podemos ver no da el warning de entidad vinculada

El nuevo dataflow actualiza sin problema evitando la capacidad Premium.

DAX. ¿Todos los caminos conducen a Roma?

Ayer retomé la buena costumbre de impartir una formación. Cuando doy una formación ganan las personas a las que va dirigida pero también siempre gano yo. El formador siempre aprende en sus clases y de mis formaciones han surgido muchos posts que luego han ayudado a la comunidad.

Ayer además fue muy especial, una clase particular de manera altruista y desinteresada para una persona que tiene muchísimas ganas de aprender, que se pelea sola contra los problemas que le van surgiendo en DAX como yo cuando empecé en este mundo y que vi que necesitaba que la centrasen un poco explicándole un tema clave en DAX como es el de los contextos.

La clase fue un vis a vis virtual, muy amena y distendida y 100% improvisada a partir de un modelo realizado sobre Adventure Works y en un momento dado me dijo: vengo del mundo de la programación en el que lo normal es que todas las cosas se hagan de la misma forma siempre y sin embargo en DAX puedes conseguir lo mismo de varias maneras y nunca se cual utilizar. Cuando utilizar un CALCULATE con un SUM o un SUMX o un FILTER o KEEPFILTER, o el filtro directamente sin función…

Se nos ocurrió entonces a partir de un ejemplo de “The Definitive Guide to DAX” analizar las diferentes medidas que nos podían llevar a realizar el cálculo de Ventas de la familia “Bikes” y decidir cual es la opción óptima.

ESCENARIO:

Como he comentado utilizamos el modelo de Adventure Works y la primera elección debemos hacerla en el modelo, cuando estamos en fase de creación de la tabla de hechos en Power Query podemos resolver muchos problemas posteriores.

¿Por qué comento esto? Pues porque lo normal es tener un campo de cantidad y otro de precio unitario. Si nosotros en Power Query creamos la columna VentaBruta (Cantidad * Precio) luego simplemente tendremos que hacer un SUM, si no la creamos tendremos que pasar al plan B crear una columna calculada para usar el SUM o utilizar SUMx

¿Cuál es la mejor opción? Todas son absolutamente válidas pero pueden afectar en mayor o menor medida a la performance del modelo. Mi recomendación es por este orden

1º Si se puede crear la columna en power query, hacerlo para utilizar el SUM

2º Si no podemos tener por cualquier motivo la columna en power query (imaginad un modelo que nos viene dado y no podemos tocar) es mejor utilizar SUMX que crear la columna calculada.

Pero para gustos colores, si el modelo tira y la performance es asumible existen esas tres vías totalmente válidas.

MEDIDAS EN DAX. Diferentes formas de conseguir la medida Ventas de Bicicletas.

Vamos a trabajar con el escenario más amplio, tenemos la columna cantidad, tenemos la columna PrecioUnitario y tenemos la columna cantidadxprecio, pero vamos a ver las fórmulas que usaríamos también si no la tuviéramos. Y se nos presenta la segunda elección, como aplicamos el filtro “Bikes”

Caso1 (presuponemos que no tenemos la columna cantidad*Precio) =


SUMX (
    FILTER ( fVentas, RELATED ( dProductos[Familia] ) = «Bikes» ),
    fVentas[Cantidad] * fVentas[PrecioUd]
)

Lo que hacemos en este caso es iterar fila a fila por una tabla de ventas filtrada por la familia “Bikes” y dado que el nombre de la familia lo tenemos en la tabla de productos tenemos que acceder a el mediante la función RELATED

Caso2 (presuponemos que no tenemos la columna cantidad*Precio) =

CALCULATE (
    SUMX ( fVentas, fVentas[Cantidad] * fVentas[PrecioUd] ),
    KEEPFILTERS ( dProductos[Familia] = «Bikes» )
)

En esta ocasión, en lugar de filtrar la tabla sobre la que hacemos el SUMX, filtramos directamente el SUMX con un CALCULATE. En este caso es indiferente utilizar KEEPFILTER o utilizar FILTER(dProductos, dProductos[Familia]=»Bikes») el resultado es el mismo aunque KEEPFILTER como se puede ver, si se conoce este aspecto es mucho más entendible y simple.

Caso3 (presuponemos que no tenemos la columna cantidad*Precio) =

CALCULATE (
    SUMX ( fVentas, fVentas[Cantidad] * fVentas[PrecioUd] ),
    dProductos[Familia] = «Bikes»
)

Hacemos lo mismo que en el caso 2 pero sin el KEEPFILTERS. Esto es equivalente a la fórmula:

CALCULATE (
    SUMX ( fVentas, fVentas[Cantidad] * fVentas[PrecioUd] ),
    FILTER ( ALL ( dProductos ), dProductos[Familia] = «Bikes» )
)

y luego observaremos las implicaciones que tiene.

Caso 4 (utilizamos ya la columna cant*precio) =

CALCULATE (
    SUM ( fVentas[CantxPre] ),
    KEEPFILTERS ( dProductos[Familia] = «Bikes» )
)


Como estamos utilizando ya la columna calculada, no necesitamos iterar por cada línea con el SUMX para realizar la multiplicación en la iteración. Es matemática simple, no podemos hacer sum(cantidad)* Sum(ventas) el resultado no sería el esperado ya que hay que hacer un SUM(cantidad*venta) y esto no se puede hacer con la función SUM que solo admite una columna (no una expresión) como argumento.

Esta medida con KEEPFILTERS igual que comentamos anteriormente es análoga a la medida

CALCULATE (
    SUM ( fVentas[CantxPre] ),
    FILTER ( dProductos, dProductos[Familia] = «Bikes» )
)

ANÁLISIS y ELECCIÓN

Ya tenemos todas las posible métricas planteadas. Sabiendo que KEEPFILTERS equivale a FILTER(Table,expresión) y el filtro directo equivale a FILTER(ALL(table),expresión) toca elegir y para ello debemos de analizar que ocurre en diferentes contextos.

Pero tenemos claro lo que es el CONTEXTO en DAX, hay amigos, ese es el tema.

Mirad esta imagen:

Son las 5 métricas que hemos utilizado en el desarrollo en un mismo contexto de evaluación, a nivel de producto. Las 5 devuelven el mismo resultado y en un modelo muy pequeño como es este caso del ejemplo con apenas 120.000 filas en la tabla de hechos la diferencia de performance es inapreciable. Habría que irse a un modelo más complejo con millones de filas para apreciar realmente cuál es la óptima y decantarnos por ella. Evidentemente va a depender también como comentábamos de si tenemos o no la columna (cantidad*precio). En todo caso, a lo que quiero llegar en este punto es en cómo afecta el contexto de evaluación a éstas métricas. En este caso en el contexto de producto no afecta para nada.

Si introducimos en la visual la familia, el contexto de evaluación permanece inalterable. Seguimos evaluando la métrica al nivel de producto y por tanto dado los filtros, no nos aparecen datos de otras familias:

¿Pero qué creéis que pasa si eliminamos el producto de la visual y cambiamos el contexto de evaluación al nivel familia?

OMG! Qué pasó!!!Aparecieron el resto de familias para las metricas 3 y 5 en las que utilizamos el filtro directo que equivale a FILTER(ALL(Tabla),Expresión)

¿A qué se debe esto? Esto se debe a la primera y más importante regla de la aplicación de los contextos en las medidas DAX.

1º se evalúa el contexto de consulta. ¿Cuál es el contexto de consulta? Pues el contexto que se introduce mediante las dimensiones en la visual o mediante filtros de slicer. En este caso nuestro contexto de consulta es el nivel familia

2º se evalúa el contexto de filtro que contiene la medida. En los casos 1,2 y 4 el contexto del filtro es muy concreto, evalúame la medida para el contexto Familia=”Bikes” Si sacamos de la visual las medidas 3 y 5 nos devuelve sólo las ventas para Bikes

Y otra regla primordial, en caso de conflicto entre los contextos de consulta y de filtro, prevalece el contexto de filtro que aparece en el calculate, por tanto aunque el contexto de consulta es familia, prevalece el contexto de filtro del calculate para solo mostrar Familia=Bikes.

En los casos 3 y 5 sin embargo el contexto de filtro que se establece es el resultado la evaluación del Resultado Familia=”Bikes” pero en el contexto de Familia ya que con el ALL(Tabla) estamos estableciendo como contexto la Familia, no la Familia=”Bikes” Por eso al evaluar la métrica y para cualquier Familia de productos el resultado que nos devuelve es el de Ventas de Bicicletas. En el contexto de Familia=”Accesories” se vendieron 95.145.657,13 $ en bicicletas.

Esta particularidad es la que necesitaremos conocer a la hora de decantarnos por una medida u otra En la mayoría de los contextos las 5 nos arrojarán el mismo resultado, pero cuando estemos en el contexto de Familia, ojo aquí es cuando debemos pararnos a valorar cual necesitamos, si la que nos devuelve datos de ventas de bicicletas para todas las familias o las que nos devuelve datos de ventas de bicicletas sólo para la familia Bicicletas.

CONCLUSIONES:

En DAX todos los caminos llevan a Roma, pero se hace necesario conocer bien los contextos en los que queremos trabajar para elegir la mejor opción.