Home

Dimensión período

La dimensión período es un conjunto de fechas agrupadas por períodos que permiten filtrar nuestros informes. En nuestro anterior post veíamos cómo elaborar el calendario de fechas en DAX y establecíamos una serie de columnas calculadas a las que llamábamos desvíos (offsets) y ya advertíamos que los desvíos nos servirían para establecer muchos cálculos dinámicos en función del día de hoy.

En este post, vamos a ver como elaborar la dimensión período, qué características tiene y como funciona en nuestros informes.

La primera vez que vi la utilización de la dimensión período fue en el blog de Chris Webb https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi-using-bidirectional-cross-filtering-and-m/ y me quedé maravillado con su potencial. Consistía en establecer una tabla dimensional de períodos temporales tales como el ejercicio actual, el trimestre actual, el mes actual, el día de ayer o el de hoy que podíamos usarlos como filtros en un informe, de manera que, con un solo cálculo de ventas y filtrado por esa dimensión, podíamos obtener el total de las ventas en el contexto temporal seleccionado y además, dinámicamente, según vaya cambiando el día de hoy, el período temporal va cambiando.

Chris Webb en ese post de referencia, explicaba cómo crear la dimensión período en lenguaje M. Yo en esta entrada voy a explicar cómo crearla en DAX. ¿Es mejor crearla en DAX o en M? Con la tabla de fechas pasa lo mismo, tenemos la opción de crearla en M o como expliqué en el anterior post, podemos crearla en DAX. No es mi propósito valorar cuál es la forma más adecuada de hacerlo, yo simplemente prefiero este tipo de tablas crearlas en DAX y por ello explico cómo hacerlo pero ambas formas son totalmente válidas. 

Nuestra tabla Período va a contener 3 campos: El campo Fecha, que incluirá todas las fechas que abarcan un período determinado, el campo NombrePeriodo, que será el nombre descriptivo del período y el Id, que podremos utilizar si queremos simplificar cálculos en los que utilicemos la dimensión período.

 Antes de comenzar a crear y a analizar nuestra tabla Periodo tendremos que crear una tabla auxiliar de fechas. Esto se debe a que si utilizamos la tabla de fechas directamente para crear la tabla de períodos, cuando posteriormente establezcamos la relación entre la tabla de fechas y la tabla de períodos se producirá una referencia circular. Eso lo salvamos creando una tabla auxiliar de fechas, que podremos ocultar ya que sólo nos servirá como base para la creación de la tabla período.

En el post anterior https://powerbisp.com/creacion-de-la-dimension-calendario-en-dax creábamos la tabla de fechas completa a partir de una variable de tabla realizada con el CALENDAR. Debemos coger el mismo rango de fechas que pusimos en esa variable para crear nuestra tabla de fechas auxiliar:

dFechasAux =  CALENDAR(DATE(2013,1,1);DATE(2021,12,31))

Una vez que tengamos nuestra tabla de Fechas Auxiliar, la relacionaremos con nuestra tabla de fechas en una relación que puede ser 1 a 1 en ambas direcciones, ya que en esencia, la tabla auxiliar contendrá las mismas fechas que la tabla de fechas. Ocultamos la Tabla de Fechas auxiliar y ya podemos empezar a construir nuestra dimensión Periodo.

Para la creación de nuestra tabla de Periodos utilizaremos tres fórmulas fundamentalmente:

UNION ya que vamos a ir creando cada periodo como una tabla independiente que uniremos con esa función.

SELECTCOLUMNS para seleccionar las tres columnas que queremos construir (Fecha, NombrePeriodo e Id)

CALCULATETABLE para seleccionar las fechas filtradas que engloban cada período y que nos servirá como la referencia de tabla del SELECTCOLUMNS.

Veámoslo paso a paso.

Si queremos construir el período Ejercicio Actual, en nuestra tabla de fechas teníamos un campo que denominábamos [desvio ejercicio]. Si el [desvio ejercicio]=0 estamos filtrando el ejercicio actual. Por tanto, para construir la tabla periodo del ejercicio actual la fórmula sería:

SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=0);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio Actual”;”Id”;1)

En esta fórmula le estamos diciendo que nos devuelva todas las filas del campo Fecha de la tabla filtrada dFechasAux donde el Desvio Ejercicio =0; una segunda columna “NombrePeriodo” que siempre será “Ejercicio Actual” y una tercera columna “Id” que siempre será 1

Para el Ejercicio Anterior o E-1 simplemente debemos cambiar el valor del “Desvío Ejercicio” por -1

SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=-1);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio E-1″;”Id”;2)

 Con el UNION unimos ambas tablas

UNION(SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=0);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio Actual”;”Id”;1); SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=-1);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio E-1″;”Id”;2))

De esta forma y utilizando los diferentes desvíos como filtro de la tabla de fechas, podemos hacer tantos períodos como podamos imaginar. He aquí un ejemplo de tabla más o menos completa de períodos:

dPeriodo =
UNION (
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio Actual",
        "Id", 1
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio E-1",
        "Id", 2
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio E-2",
        "Id", 3
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Ejercicio] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio Actual hasta hoy",
        "Id", 4
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Actual",
        "Id", 5
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Trimestre] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Actual hasta hoy",
        "Id", 6
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-1",
        "Id", 7
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-2",
        "Id", 8
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -3 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-3",
        "Id", 9
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes Actual",
        "Id", 10
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Mes] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes Actual hasta hoy",
        "Id", 11
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes M-1",
        "Id", 12
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes M-2",
        "Id", 13
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Semestre] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Semestre Actual",
        "Id", 14
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Semestre] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Semestre Actual hasta hoy",
        "Id", 15
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -364, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 365 Dias",
        "Id", 16
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -29, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 30 Dias",
        "Id", 17
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -6, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 7 Dias",
        "Id", 18
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Dia] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Hoy",
        "Id", 19
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Dia] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ayer",
        "Id", 20
    )
)
**Formateado con daxformatter.com y apto para pegar directamente en 
Power BI con el sistema de DAX angloamericano.

*** Ante el cambio de política de Microsoft PowerBI con la idea de unificar el código DAX y la recomendación de utilizar el sistema angloamericano(update mayo-2020), a partir de este post intentaré utilizar siempre el sistema angloamericano, con (,) como separador, aunque me pese ya que siempre he sido de la opinión de conservar el sistema europeo. En las explicaciones el código puede estar con «;» (la fuerza de la costumbre) y por temas de wordpress las «(comillas) no son compatibles por lo que si vais a copiar directamente en Power BI utilizad sólo el código que marque para ello o los ficheros de descarga que adjuntaré.

La dimensión periodo se relaciona con nuestra tabla de Fechas Aux con una relación de varios a 1 pero en ambas direcciones.

De esta manera, las relaciones entre las diferentes tablas de fecha y calendario quedaría con el siguiente esquema:

Por último es necesario realizar una puntualización en cuanto a los cálculos del modelo ya que cualquier cálculo temporal que queramos realizar, deberá contener el filtro ALL(dPeriodo)

Por ejemplo el cálculo normal de Ventas E-1 (Ventas del ejercicio anterior) sin dimensión periodo sería:

Ventas E-1 = CALCULATE(SUM(Sales[SalesAmount]),DATEADD(dFechas[Fecha],-1,YEAR))

 Con la dimensión período, si queremos que nos funcione cuando filtramos por cualquier período quedaría así:

 Ventas E-1 = CALCULATE(SUM(Sales[SalesAmount]),ALL(dPeriodo),DATEADD(dFechas[Fecha],-1,YEAR))

NUEVAS CONSIDERACIONES SOBRE LA DIMENSÍON PERÍODO

Este post lo escribí por primera vez en abril de 2018 y desde entonces he utilizado siempre la Dimensión período creada en DAX en mis modelos, sin embargo en los últimos tiempos me he decantado más por utilizar los desvíos de mi calendario directamente en las medidas o en los slicer para filtrar dinámicamente el tiempo, ambas técnicas están más que probadas y ya es cuestión de la utilización que queramos darle.

Si es cierto que esta técnica en cuanto a performance no es de las más optimas. No he tenido problemas en datasets grandes con ella, pero pueden tenerse.

En octubre de 2019 Phil Seamark hablaba sobre ello en un post: https://dax.tips/2019/10/09/row-based-time-intelligence/ basándose en la dimensión período creada en M que no necesita de la tabla auxiliar y planteando el siguiente esquema de relación como más óptimo:

Esquema propuesto por Phil Seamark en su artículo. Time Intelligence equivaldría a mi dimensión período, pero en esta caso creada en M

Ciertamente he de confesar que no he probado esta técnica propuesta por Phil en mi modelo con la Dimensión período en DAX, pero prometo en cuanto pueda hacerlo y ofrecer mis propias conclusiones. No obstante he de decir que el artículo de Phil es muy recomendable y que explica en gran medida los beneficios (y las desventajas) de una dimensión como ésta y me enorgullece que sea una práctica habitual en mis modelos desde hace muchísimo tiempo y aceptadas por la comunidad de Power BI aunque no conocidas por todo el mundo.

*Os recuerdo que el DAX Angloamericano es con la separación con «,» y europeo con «;» y que desde la versión de mayo de Power BI se puede elegir entre uno u otro.

Creación de la dimensión Calendario en DAX

Uno de los grandes “poderes” de los modelos analíticos en Power BI es la inteligencia temporal en los cálculos. Ello nos permite que calcular medidas como las ventas y compararlas con períodos de ventas anteriores resulte extremadamente sencillo y potente, pero la base fundamental para ello es disponer de una buena y completa tabla de calendario con todas las columnas calculadas que puedan servirnos para nuestros análisis.

Las tablas de calendario pueden hacerse de diversas formas, desde el editor de Power Query en M o desde el propio entorno de Power BI Desktop con el lenguaje DAX.

Después de usar ambos sistemas y ver las bondades de uno y otro, me quedo con la programación de la tabla de Calendario en DAX y he elaborado una tabla de fechas con todas las columnas calculadas que utilizo en mi día a día en un sólo cálculo de tabla.

A partir de la función de tabla CALENDAR y usándola como variable, puede en un mismo cálculo insertarse cuantas columnas calculadas necesitéis con la función SELECTCOLUMNS.

La tabla definitiva de calendario que he elaborada obedece a la siguiente fórmula, que puede copiarse tal cual en la barra de fórmulas tras presionar “nueva tabla”:

DimFechas =
VAR Fecha =
    CALENDAR ( DATE ( 201911 )DATE ( YEAR ( TODAY () ) + 11231 ) )
RETURN
    SELECTCOLUMNS (
        Fecha;
        «Fecha»; [Date];
        «IdFecha»YEAR ( [Date] ) * 10000
            MONTH ( [Date] ) * 100
            DAY ( [Date] );
        «IdFechaEntero»INT ( [Date] );
        «Ejercicio»YEAR ( [Date] );
        «Mes»UPPER ( FORMAT ( [Date]; «MMMM» ) );
        «MesNro»INT ( FORMAT ( [Date]; «M» ) );
        «NroDia»INT ( FORMAT ( [Date]; «d» ) );
        «Trimestre»«T»
            ROUNDUP ( MONTH ( [Date] ) / 30 );
        «NroTrimestre»ROUNDUP ( MONTH ( [Date] ) / 30 );
        «DiaSemana»WEEKDAY ( [Date]; 2 );
        «Semana»WEEKNUM ( [Date]; 2 );
        «Nombre Dia»UPPER ( FORMAT ( [Date]; «DDDD» ) );
        «EjercicioTrimestre»COMBINEVALUES (
            «-«;
            YEAR ( [Date] );
            «T»
                ROUNDUP ( MONTH ( [Date] ) / 30 )
        );
        «NºSemana ISO»ROUNDDOWN (
            (
                [Date]
                    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                    WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
            ) / 7;
            0
        );
        «MesCorto»UPPER ( FORMAT ( [Date]; «MMM» ) );
        «DiaEjercicio»UPPER ( FORMAT ( [Date]; «Y» ) );
        «EjercicioActual»YEAR ( TODAY () );
        «Mes Actual»MONTH ( TODAY () );
        «HoyEntero»INT ( TODAY () );
        «Trimestre Actual»ROUNDUP ( MONTH ( TODAY () ) / 30 );
        «Semestre»ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 );
        «EjercicioMes»COMBINEVALUES ( «-«YEAR ( [Date] )FORMAT ( [Date]; «MM» ) );
        «EjercicioISO»IF (
            OR (
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 53;
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 52
            )
                && MONTH ( [Date] ) = 1;
            YEAR ( [Date] ) – 1;
            IF (
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 1
                    && MONTH ( [Date] ) = 12;
                YEAR ( [Date] ) + 1;
                YEAR ( [Date] )
            )
        );
        «Desvio Ejercicio»YEAR ( [Date] ) – YEAR ( TODAY () );
        «Desvio Mes»IF (
            ( YEAR ( [Date] ) – YEAR ( TODAY () ) ) = 0;
            INT ( FORMAT ( [Date]; «M» ) ) – INT ( FORMAT ( TODAY ()«M» ) );
            INT ( FORMAT ( [Date]; «M» ) ) – INT ( FORMAT ( TODAY ()«M» ) )
                + (
                    12
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        );
        «Desvio Dia»INT ( [Date] ) – INT ( TODAY () );
        «HastaFecha»IF ( ( INT ( [Date] ) – INT ( TODAY () ) ) <= 0«PASADO»«FUTURO» );
        «Desvio Trimestre»IF (
            ( YEAR ( [Date] ) – YEAR ( TODAY () ) ) = 0;
            ( ROUNDUP ( MONTH ( [Date] ) / 30 ) )
                 ( ROUNDUP ( MONTH ( TODAY () ) / 30 ) );
            ( ROUNDUP ( MONTH ( [Date] ) / 30 ) )
                 ( ROUNDUP ( MONTH ( TODAY () ) / 30 ) )
                + (
                    4
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        );
        «Semestre Actual»ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 );
        «Desvio Semestre»IF (
            YEAR ( [Date] ) – YEAR ( TODAY () ) = 0;
            (
                ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 )
            )
                 (
                    ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 )
                );
            (
                ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 )
            )
                 (
                    ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 )
                )
                + (
                    2
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        )
    )

El código dax ha sido formateado en https://www.daxformatter.com/ herramienta que os recomiendo para mejor legibilidad del código y os lo podéis descargar en txt en el siguiente enlace:

Además de diferentes columnas muy interesantes de fechas como la Semana ISO o el Ejercicio ISO para calendarios gregorianos, muy usado en nuestras empresas agrícolas, establezco los denominados desvíos (offsests) que introdujo Avi Singh, gurú del power BI americano, que permiten realizar infinidad de cálculos dinámicos en función del día de hoy.

Las fechas de inicio y fin pueden cambiarse fácilmente modificando sólo la tabla Fecha que se define en la variable. A modo de ejemplo he puesto desde el 1/1/2019 al 31/12/2021.

Espero que os sea de utilidad y si tenéis alguna sugerencia de columna que pondríais en la tabla de fechas podéis decírnoslo en los comentarios.

Configurar mediante un tema personalizado los encabezados de objetos visuales

De vez en cuando me da por trastear las entrañas de power bi para descubrir cosas nuevas. Hoy me planteaban una duda, ¿de qué manera se pueden configurar los encabezados de objeto visual para que no aparezca ningún icono pero no se impida al usuario exportar los datos a excel, por ejemplo.

En cualquier objeto visual, existe la opción de Activar o desactivar el encabezado del objeto visual

Y una vez activa, se pueden habilitar unos u otros encabezados según convenga:

Puedo desactivar todos y dejar activo el Icono Más opciones.

Pero si quiero automatizar este proceso, en todos los objetos visuales es tremendamente laborioso.

Lo primero que pensé fue en el generador de temas de powerbitips.com pero ellos contemplan la posibilidad de activar o desactivar el Encabezado completo, pero no unos iconos sí y otros no.

Busqué a ver si encontraba la solución por algún lado y no la encontré, hasta que recordé como descubrí los nombres de los iconos cuando salió el formato condicional y que Matt Allington mencionó en su blog:

https://exceleratorbi.com.au/power-bi-icon-names/

Seguro que en mi querido layout estaba la solución. Puse mis encabezados como activos, desactivé todos los iconos y comencé a bucear en el layout del pbix con el que estaba trabajando para descubrir como generar el código y efectivamente, logré que funcionara.

Personalizando el visual header desde power bi tips conseguí el siguiente código:

{"name":"theme created by powerbi.tips https://themes.powerbi.tips",
"visualStyles":
{"": {"":{"visualHeader":[{"show":true]}}}}
}

Sólo me quedaba descubrir cómo desactivar unos u otros iconos a mi antojo y en el layout estaban sus nombres.

{"name":"personalizado","dataColors":["#c1a131","#374649","#FD625E","#F2C80F","#5F6B6D","#8AD4EB","#FE9666","#A66999",
"#3599B8","#DFBFBF","#4AC5BB","#5F6B6D","#FB8281","#F4D25A","#7F898A","#A4DDEE","#FDAB89","#B687AC","#28738A","#A78F8F",
"#168980","#293537","#BB4A4A","#B59525","#475052","#6A9FB0","#BD7150","#7B4F71","#1B4D5C","#706060","#0F5C55","#1C2325"],
"visualStyles":
{"*":{"*":
{"visualHeader":[{"show":true,
"showVisualInformationButton":false,
"showVisualWarningButton":false,
"showVisualErrorButton":false,
"showDrillRoleSelector":false,
"showDrillUpButton":false,
"showDrillToggleButton":false,
"showDrillDownLevelButton":false,
"showDrillDownExpandButton":false,
"showPinButton":false,
"showFocusModeButton":false,
"showSeeDataLayoutToggleButton":false,
"showFilterRestatementButton":false,
"showOptionsMenu":true}]}}}}

Con este código, poniendo true o false según nos interese, podemos ocultar o mostrar programáticamente vía tema personalizado los encabezados de los objetos visuales, actuando sobre todos por defecto de esa manera o, si solo queremos que actúe en un objeto visual concreto, simplemente debemos sustituir el segundo asterisco por el nombre que la visual tiene en inglés (p. ej. Pie Chart)

El tema aparece con extensión .pdf pero es un .json, sólo hay que cambiarle la extensión y funciona.

Píldoras Power Query : Agrupar por la línea más reciente

Una de las necesidades que pueden surgirnos en el tratamiento de la información es el que tengamos que quedarnos con una sola línea de un conjunto de datos agrupados. Por ejemplo, la línea de venta más reciente. En SQL por ejemplo, eso lo solventamos con ROW_NUMBER ()

select * from
(select * ,
row_number() OVER(Partition by Cliente order by Fecha desc) as Row
FROM [Tabla].[dbo].[Ejemplo]) h
where h.Row=1

Lo que estamos haciendo con esa sentencia es crear un número de orden en todas las lineas de venta para cada cliente, ordenándolas por fecha descendente y quedándonos solo con la que el row es 1, la más reciente.

Vamos a ver la forma de hacer lo mismo en Power Query.

Vamos a crear una tabla de ejemplo como esta:

Este sería el código para crearla:

let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjAyDHyNhUKVYHIWEEkzA2NUORMIZJmBhagCWM0I0yNLJEkUAYZWyEIgE3ytTUXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Fecha = _t, Importe = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Id", Int64.Type}, {"Fecha", type date}, {"Importe", Int64.Type}})
in
#"Tipo cambiado"

A partir de aquí podemos hacer dos cosas, crear una función que nos haga el proceso o hacerlo directamente. Vamos a ver aquí la opción de hacerlo directamente sin la función ya que es lo más sencillo.

1º Ordenamos por fecha descendente

2º Agrupamos la tabla por Id y en la columna de Agregación elegimos en la operación Todas las filas

Esto nos va a crear la siguiente tabla:

Y ahora agregamos la siguiente columna personalizada:

Si vemos la información de la tabla Recuento (pinchando en la celda pero no en la palabra Table) para el id 1 nos muestra tres líneas ordenadas por fecha

Si hacemos lo mismo en personalizado, vemos que esa tabla se ha quedado sólo con la primera línea, que al estar ordenada por fecha descendiente sería la de la última venta:

Eliminamos la Columa Recuento, expandimos la de Personalizado y ya lo tendríamos listo, la última venta de cada id.

Este sería el código completo del ejemplo

let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjAyDHyNhUKVYHIWEEkzA2NUORMIZJmBhagCWM0I0yNLJEkUAYZWyEIgE3ytTUXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Fecha = _t, Importe = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Id", Int64.Type}, {"Fecha", type date}, {"Importe", Int64.Type}}),
#"Filas ordenadas" = Table.Sort(#"Tipo cambiado",{{"Fecha", Order.Descending}}),
#"Filas agrupadas" = Table.Group(#"Filas ordenadas", {"Id"}, {{"Recuento", each _, type table [Id=number, Fecha=date, Importe=number]}}),
#"Personalizada agregada" = Table.AddColumn(#"Filas agrupadas", "Personalizado", each Table.FirstN([Recuento],1)),
#"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada",{"Recuento"}),
#"Se expandió Personalizado" = Table.ExpandTableColumn(#"Columnas quitadas", "Personalizado", {"Fecha", "Importe"}, {"Fecha", "Importe"}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Se expandió Personalizado",{{"Fecha", type date}, {"Importe", Int64.Type}})
in
#"Tipo cambiado1"

Creación y edición de mapas de formas

Últimamente, por necesidad, los proyectos siempre mandan, he tenido que aprender a editar mapas para crear mapas de formas personalizados. Incluso me he aventurado en proyectos de mapas de formas con drilldown a partir de una jerarquía administrativa con el custom visual map drilldown choropletic, pero empecemos por lo más sencillo, como crear un mapa de formas personalizado a partir de uno ya existente.

Vamos a necesitar varios elementos.

1º El mapa al nivel administrativo que lo necesitemos en formato shape o geojson. Podemos descargar los datos de los Municipios españoles por ejemplo desde este enlace:

https://data.opendatasoft.com/explore/dataset/espana-municipios%40public/export/?location=5,36.12825,-6.91667&basemap=jawg.sunny  en formato shapefile o geojson

2º El programa de edición Qgis que es software libre y podéis descargar desde su página oficial: https://www.qgis.org/es/site/forusers/download.html

3º Utilizaremos también el programa online Mapshaper (https://mapshaper.org/ ) para transformar nuestra creación al formato topojson admitido en el mapa de formas.

Descargamos el archivo españamunicipios en nuestro pc y lo descomprimimos en la carpeta c:/ para tenerla fácilmente localizada (o donde queráis)

Abrimos el programa Qgis. Yo tengo la versión 3.12. Vale cualquier versión.

Creamos un nuevo proyecto y cargamos en las capas el mapa en formato shape con el que queramos trabajar. En este caso voy a trabajar con el archivo de municipios de la península, Baleares y Canarias que hemos descargado

Sobre este mapa tal cual podríamos sin problema crear el archivo topojson completo desde la página mapshaper.org pero lo que vamos a aprender es a extraer partes más pequeñas por si necesitamos un mapa concreto. En este ejemplo vamos a extraer el mapa municipal Madrid.

Lo primero que vamos a hacer es identificar algún municipio de Madrid para ver cómo podemos realizar el filtro.

Hacemos un poco de zoom en el mapa y le damos al botón de identificar objetos espaciales, la i de info y pinchamos en el centro un municipio que pueda corresponder a la provincia de Madrid.

Se nos abre la siguiente pestaña de información

y podemos ver que hay un campo provincia por el que podremos filtrar

Damos a botón derecho sobre la capa y en el menú elegimos filtrar:

Y en la expresión de filtrado ponemos provincia=’Madrid’

Las comillas que utilizamos son las mismas que en los comandos en SQL

Le damos a Aceptar y ya tenemos el mapa filtrado:

Vovemos a dar a botón derecho sobre la capa y seleccionamos Exportar/Guardar Objetos como y lo exportamos como archivo GeoJSON manteniendo todos los campos de información que contiene el shape.

Podemos ya cerrar Qgis y nos vamos a https://mapshaper.org/

Seleccinamos el archivo GeoJSON guardado y lo importamos  y podemos ver el mapa de Madrid y la información que contiene el mapa

Le damos a exportar y lo convertimos en topoJson para que nos sirva para el mapa de formas de Power BI

Ahora ya con ese TopoJson en Power BI podemos utilizarlo como mapa de formas y utilizar el archivo json para extraer los datos de identificación de cada zona, que tendremos que casar de alguna forma con los datos del municipio que tengamos en nuestra base de datos bien por nombre o bien por los identificadores varios que pueden aparecer en la información del mapa, eso ya os lo dejo a vuestra imaginación.

He creado un proyecto en github donde poder subir todos los mapas de formas que vaya generando para mi trabajo. Y allí tengo un proyecto de mapa con drill down que si resulta interesante puedo plasmar en este blog como lo he realizado.

https://github.com/FMullor/TopoJson

Exportar un dataflow

Vamos a detallar los pasos a seguir para exportar un dataflow entre dos tenants diferentes o dentro de un mismo tenant entre dos espacios de trabajo diferentes, el caso es el mismo.

En este caso se trata de un dataflow cuyas conexiones son todas on cloud, por lo que no reguieren puerta de enlace.

En primer lugar hay que exportar el archivo .json que contiene todas las tablas del dataflow:

En segundo lugar, nos vamos al nuevo espacio de trabajo donde queremos importar el dataflow, le damos a crear dataflow y en la pantalla de creación le damos a Importar Modelo:

Seleccionamos el archivo .json exportado anteriormente y directamente nos va a crear el nuevo flujo de datos:

Editamos las credenciales y ya lo tenemos listo, así de simple.

Actualizamos el flujo de datos y así carga la información de las entidades.

Si en el dataflow tenemos alguna query nativa, nos dará un mensaje de error como el siguiente:

Para salvar este error es sencillo. Abrimos el archivo .json con un editor como por ejemplo Notepad++ y le decimos que busque y reemplace la siguiente cadena:

Pero solo debemos reemplazarla cuando venga predecida de “AllowNativeQuery”, el resto de trues que nos encontremos no hay que cambiarlos.

De esta forma ya se carga correctamente:

Con estos sencillos pasos podremos exportar e importar nuestros flujos de datos según lo necesitemos.

Vista de Linaje en el servicio de Power BI (vista preliminar)

A finales de octubre en el blog de Microsoft se publicó la entrada sobre la vista de linaje de datos en versión preliminar en el servicio de power BI:

https://powerbi.microsoft.com/es-es/blog/introducing-power-bi-lineage-view/

Hasta ahora no me había detenido a su evaluación, pero me parece una herramienta muy interesante para conocer todo el flujo de datos que interviene en un modelo analítico construido en power BI, ofreciéndonos una visión amplia de todos los elementos que  están subidos en un área de trabajo en el servicio y el árbol de su origen desde el origen de datos hasta el panel

Os muestro un ejemplo simple:

A partir de un origen de datos local, una carpeta, a la que se accede mediante una puerta de enlace, he construido un flujo de datos (Prueba power bi sp). A partir de ese flujo de datos he creado un conjunto de datos (Prueba dataflow model). A partir de éste un informe (01.Report Prueba), que yo siempre construyo conectado al modelo, mis reportes nunca contienen los conjuntos de datos sino una conexión a los mismos. Y a partir de éste he creado un panel (01. DashboardPrueba.

Desde esta vista linaje podemos acceder y realizar muy diversas tareas en cada uno de los elementos que contiene.

Si seleccionamos el flujo de datos, se nos abre la siguiente ventana a la derecha

Esta ventana nos va a mostrar las entidades que contiene el flujo de datos y vamos ir hasta su edición directamente.

Pinchando en los tres puntos   

Nos va a mostrar el siguiente menú:

Pudiendo navegar hasta cualquiera de los elementos que en el menú aparecen: Eliminar el flujo, editarlo, exportar el .json, Propiedades, Actualizar historial, Actualizar ahora, programar actualización y configuración.

Si pinchamos en el conjunto de datos nos va  a mostrar el nombre de las entidades que contiene:

Y pinchando en los 3 puntos nos ofrece el siguiente menú de funcionalidades:

Pinchando el informe, nos va a mostrar la siguiente pestaña a la derecha desde la que podemos acceder directamente al informe:

Y desde el los tres puntos nos va a ofrecer las siguientes funcionalidades:

Y pinchando el panel nos va a posibilitar acceder directamente al mismo:

Y desde los tres puntitos nos presenta las siguientes opciones:

Si existe algún error de actualización nos lo va a mostrar en un vistazo:

Y vamos a tener un mapa claro de cada uno de los flujos intervinientes en el modelo:

Personalmente creo que es un avance muy importante en el servicio para tener mayor control sobre todos y cada uno de los elementos intervinientes en un proyecto de BI con Power BI que en modelos complejos, con orígenes muy diversos, puede ser crucial para su documentación y su entendimiento.

Gran batería de novedades (+50) en los flujos de datos

Hace unos días Miguel Llopis anunciaba en el blog de Microsoft Power BI una batería de más de 50 novedades en los Flujos de Datos de este mes.

https://powerbi.microsoft.com/en-us/blog/50-new-features-in-dataflows-this-month/

Tengo pendiente explicaros detenidamente qué es y por qué utilizo en mi día a día un flujo de datos, pero de momento, aquí os dejo una explicación de todas las novedades que han introducido en la actualización del servicio en Noviembre.

Uno de los problemas que quienes hemos elegido los flujos de datos como herramienta principal de autoservicio de ETL nos hemos encontrado desde el principio ha sido la limitación que en el power query del servicio de power bi existía en cuanto al número de transformaciones programadas en la interface.

Esto lo han mejorado muchísimo en esta actualización con + de 50 nuevas transformaciones disponibles.

Quedan otras en el camino, que seguramente acabarán llegando hasta equiparar la automatización de la interface de power query en la nube con la interface de power query en el desktop, pero el paso que han dado es importantísimo.

Paso a detallar las novedades más importantes que me he encontrado y que echaba muchísimo en falta en la anterior versión de los dataflow:

1º Nuevos conectores a orígenes de datos

Este es el mapa de todos los orígenes que actualmente presentan los flujos de datos y he recuadrado en nuevo los 7 nuevos orígenes de esta actualización:

2º Nuevas transformaciones de datos

Todas las trasformaciones que voy a describir en este punto nos parecerán obvias su existencia a todos los que trabajamos ya con power query en power bi desktop, pero hasta ahora no estaban en la interface del power query de los flujos de datos. Reitero, no obstante, que el hecho de que no existiera en la interface la manera de hacerlo automáticamente no significa que el motor de power query no identificase el código M que se genera en esas transformaciones. Si realizábamos dichas transformaciones directamente escribiendo la línea de código en M o copiando y pegando desde power bi desktop, podíamos realizar las transformaciones sin problema alguno. No obstante, con esta actualización el uso directo de la interface de power query de los flujos de datos se simplifica y se refuerza, de manera que ya no será tan necesario, como hacíamos hasta ahora, general el código M en el desktop y copiarlo en los flujos de datos.

2.1 Combinar archivos

Algo tan sencillo como combinar automáticamente dos Excel con la misma estructura ubicados en una misma carpeta de sharepoint o local, que se realiza de manera automática habitualmente en el desktop, ahora es también posible desde los flujos de datos.

Elegimos como origen de datos una carpeta local en este caso:

Al hacer login(recordad que necesitamos una puerta de enlace instalada y conectada que tenga acceso a la carpeta) nos muestra todos los archivos que encuentra en la carpeta, en este caso dos Excel con la contabilidad de dos años diferentes pero con la misma estructura.

Le damos a transformar datos y una vez en el editor vamos a  …/combinar tablas /combinar archivos.

Le decimos qué archivo queremos que tome de ejemplo (o dejamos Primer archivo), seleccionamos el libro del Excel que queremos combinar y damos a aceptar. Power query se encarga de montar toda la estructura de parámetros, funciones y el resultado final combinado, igual que en el desktop.

2.2 Combinar consultas. Selección de tipo de unión visual.

Combinar consultas presenta ahora una descripción visual mediante diagrama de Venn para que el usuario no familiarizado con el uso de términos tales como inner join, left join, etc pueda escoger el tipo de relación que quiere establecer.

2.3 Transformaciones de número, fecha, fecha/hora a través de la barra de herramientas

Algo tan sencillo como este tipo de transformaciones que conocemos sobradamente de power query en power bi desktop no existía antes de esta actualización y debía hacerse utilizando código M directamente en la consulta.

2.4 Reducir filas, rellenar, mover

Funciones a las que estamos tan acostumbrados en power query para power bi desktop no estaban implementadas en la interface de power query en los flujos de datos, por lo que el salto en la realización de transformaciones mediante la barra de herramientas es impresionante.

Hasta ahora, las limitaciones eran tantas que salía más rentable realizar las transformaciones en el power bi desktop y copiar el código en una consulta en blanco. Con esta actualización y el gran avance que supone, un porcentaje muy alto de las transformaciones habituales está, por fin, cubierto.

3º Data profiling

Al igual que en el desktop, los flujos de datos incorporan ahora el perfil de columna y, lo mismo que en el desktop lo podemos configurar basado en las 1000 primeras filas o en el total de filas del conjunto de datos.

Lo podemos habilitar o deshabilitar en Opciones/Opciones globales.

Mi primera conclusión en su utilización es que es extremadamente lento en la carga, sobre todo si se activan todas las opciones.

Aquí os lo muestro sólo con las dos primeras pestañas seleccionadas.

Y aquí con todas las pestañas marcadas:

4º Creación de parámetros

Hasta ahora no existía en la interface la posibilidad de crear parámetros mediante un cuadro de diálogo. Ojo, esto no significa que no se pudieran utilizar. Yo los he utilizado sin problemas. Lo que significa es que no podías configurarlos mediante un cuadro de diálogo en la interface ni tenías un cuadro de diálogo para cambiar el parámetro, había que hacerlo manualmente en la línea de código. Antes, simplemente copiando el código de un parámetro de power bi desktop en una consulta en blanco del flujo de datos, tenías el parámetro listo para usarlo, pero con las limitaciones descritas de no poder modificarlo en un cuadro de diálogo.

Ahora ya existe en la interface un cuadro de diálogo para la creación y modificación de parámetros tal y como lo tenemos en el power bi desktop.

5º Creación de funciones

Tras esta actualización pueden crearse muy fácilmente funciones a partir de consultas que contengan algún parámetro

Y la probamos invocando las cuentas que sean del tipo GASTO:

6º Intellisense

Algo que nos parece ya tan normal como el Intellisense es totalmente nuevo en los flujos de datos.

7º Seleccionar tablas relacionadas

Otra característica que estamos acostumbrados a utilizar, la posibilidad de traernos al modelo tablas relacionadas del origen de datos, es también una novedad en los flujos de datos.

Aunque todo lo que ha llegado a los flujos de datos parecerán para los usuarios que manejan habitualmente power bi desktop cosas obvias y necesarias, no estaban hasta ahora en los flujos de datos, por lo que la manera óptima de realizar transformaciones en los flujos era o bien picar código M si se tenían los conocimientos adecuados, o bien crear la consulta en power bi desktop y copiarla y pegarla en el flujo de datos.

Con esta batería de más de 50 funcionalidades, ya casi se puede hacer todas las transformaciones que habitualmente realizamos en el desktop directamente en los flujos de datos. Echo en falta algo que esperaba en una actualización así, como la opción de agregar una columna, ya que en los flujos de datos de momento sólo se puede agregar una columna condicional o una columna de índice. Y por otro lado, ya que han incluido los parámetros y las funciones, no estaría mal poder agregar columna a través de la invocación de una función, funcionalidades estas que estoy seguro que llegarán en próximas actualizaciones.

Crear un botón de eliminación de filtros con Marcadores

Vamos a explicar en este post como hacer un botón de eliminación de filtros mediante un marcador.

Partimos del siguiente informe de ventas basado en el modelo de Adventureworks

El usuario puede establecer diferentes filtros de país y de vendedor para cambiar los contextos de análisis. Así, por ejemplo podríamos ver las ventas de JAE B PAK en Canadá en el 2019:

O las ventas de AMY E ALBERTS en Francia:

En la parte superior derecha he colocado una imagen de un embudo con una X para eliminar los filtros. Vamos a ver cómo creo un marcador que me elimine los filtros manteniéndome el Ejercicio 2019.

Primero elimino todos los filtros menos el del ejercicio que lo quiero mantener.

En segundo lugar creo un marcador, una imagen fija de ese momento, sin filtros salvo del del ejercicio 2019. En el panel de marcadores le doy a Agregar lo renombro por ejemplo a “SIN FILTROS” y le dos a los tres puntos(…)

Importante en el momento de darle a actualizar debemos tener marcado el check de Datos. Si desmarcásemos ese check, nos mantendría cualquier filtro que el usuario hubiera establecido en su navegación, sin embargo, manteniendo el check de Datos nos va a llevar a la imagen exacta de la visual tal y como la tenemos ahora, sin filtros de Pais o Vendedor y con el filtro del Ejercicio 2019. Si queremos que tampoco nos filtre de inicio el Ejercicio podemos hacerlo actualizando con el filtro del ejercicio desmarcado.

Una vez creado este marcador seleccionamos la imagen que hemos puesto para desmarcar los filtros:  

Nos vamos a formato de Imagen y allí activamos la acción:

Indicándole en TIPO: Marcador

En Marcador; EliminarFiltros (o como le hayamos llamado al marcador que hemos creado)

Y en información sobre herramientas lo que queramos que nos muestre el tooltip al pasar sobre la imagen.

De esta manera, podemos establecer los filtros que queramos en el informe y al presionar la imagen de eliminación de filtros (con ctrl+botón izq mouse en en el desktop) nos va a eliminar cualquier filtro que tengamos seleccionado ya que nos lleva al marcador que teníamos sin filtros.