Como continuación de una entrada anterior (ver aquí), donde exponíamos como replicar el comportamiento de las funciones BD, es decir, poder operar sobre una base de datos a partir de un rango de criterios, empleando lenguaje DAX dentro del modelo de datos de Power Pivot para Excel, y donde en primera instancia no encontré la opción de crear una Medida sin apoyarme en una columna calculada, hoy explicaré algo de lenguaje DAX avanzado que nos permitirá:
Crear una medida en el modelo de datos, sin pasar por columnas calculadas, y poder después usar esa medida con nuestras funciones CUBO.
Recordemos que un punto relevante de este problema era que nuestras tablas de trabajo NO están relacionadas (revisa el post anterior!).
He conseguido escribir una Medida que funciona adecuadamente (desconozco si será la manera más óptima, pero apuesto a que hay formas mejores...).
Pero en todo caso me parece interesante compartirla ya que se emplean algunas de las funciones DAX más curiosas respecto a trabajar con Tablas, Columnas, Filtros...
Veamos la fórmula de nuestra medida:
Recuerda que para crear una Medida debes acceder al Modelo de datos, al área de cálculo de una tabla, e insertar el nombre deseado seguido de := (en mi ejemplo S_CR:=).
Con la medida operativa, accederemos a la hoja de cálculo, y como explicaba en artículos anteriores, haciendo uso de la función VALORCUBO.
En concreto, para recuperar el Importe acumulado de todos los años, en la celda Q7 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[S_CR]")
que devuelve los importes correspondientes, sin discriminar por años, de aquellos registros que cumplen los criterios expuesto en el rango de CRITERIOS...
Observa como la función VALORCUBO llama a nuestra Medida 'S_CR'.
De igual forma en Q10 y sucesivas he insertado:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[S_CR]";N10)
Recuperando el valor de la medida, y cruzándola con el conjunto de años de N10.
Ten presente, que en estos casos, donde empleamos Medidas del modelo de datos, necesitaremos 'Actualizar datos' para ver el dato correcto.
El objetivo está conseguido ;-)
Pero vamos a explicar las funciones DAX empleadas para que nuestra medida funcione...
Me apoyaré en DAX Studio (https://daxstudio.org/) para verificar el paso a paso.
Listaré, en primer lugar, las funciones empleadas:
IF(prueba_lógica;valor_si_verdadero;[valor_si_falso])
el clásico condicional que comprueba la prueba lógica y devuelve una valor u otro.
COUNTROWS(tabla)
Cuenta el número de filas de la tabla (o de la expresión que genere una tabla).
CALCULATE(expresión; [filtro1]; [filtro2];...)
Evalúa una expresión (suele ser una medida) en el contexto de los filtros indicados.
FILTER(tabla;filtro_aplicado)
Nos devuelve una tabla, como subconjunto de aquellos registros que cumplan las condiciones de los filtros.
CALCULATETABLE(expresión_tabla; [filtro1]; [filtro2];...)
La función evalúa una tabla (tabla como tal o una función que devuelva una tabla) en el contexto de los filtros indicados.
ADDCOLUMNS(tabla; nombre1; expresión1; [nombre2]; [expresión2];...)
Nos permite añadir una columna calculada a la tabla indicada
VAR - RETURN expresiones DAX que permiten definir y almacenar variables en memoria para su uso posterior como argumento de otras expresiones o medidas. Aquí algún ejemplo (leer más).
Analicemos pues que obtendrímos en cada paso...
En primer lugar incorporaremos la expresión que ya utilizamos en la entrada anterior para cruzar las tablas con la información: Tabla1 versus TblCRITERIOS:
=VAR XX=
FILTER(TblCRITERIOS;
IF(TblCRITERIOS[Comercial]="";"1";TblCRITERIOS[Comercial]=(Tabla1[Comercial]))&&
IF(TblCRITERIOS[País]="";"1";TblCRITERIOS[País]=(Tabla1[País]))&&
IF(TblCRITERIOS[Producto]="";"1";TblCRITERIOS[Producto]=(Tabla1[Producto])))
RETURN
CALCULATE(COUNTROWS(TblCRITERIOS);XX)>0
Esa expresión es la que empleamos en la función ADDCOLUMNS para crear esa columna calculada 'virtual', donde incorporamos una nueva columna (llamada 'Criteria') con el cálculo conocido.
Cálculo que cruza tablas NO relacionadas buscando coincidencias... y devolviendo TRUE o FALSE si en nuestra 'Tabla1', de acuerdo al Comercial, País y Producto cruza con algunas de las opciones de criterios de la tabla TblCRITERIOS.
Se observa en la imagen como replica el comportamiento que veíamos realmente en nuestra tabla.
En el siguiente paso (no sería necesario!!), he preferido añadir la función CALCULATETABLE para asegurar que DAX entiende que es una tabla.
Podemos comprobar que el resultado es la misma tabla de antes... si bien, aquí podríamos haber aprovechado para aplicar algún filtro sobre algún otro campo... Por ejemplo de años:
No es nuestro caso, ya que en nuestro ejercicio he optado por aplicar las condiciones de filtro de año con un conjunto expresado en MDX.
Asi pues llegamos al final, donde producimos el cálculo de la medida deseada empleando SUMX:
SUMX(tabla,[Ventas]*[Criteria])
donde como curiosidad hacemos uso de la variable definida como 'tabla', sino que también empleamos nuestra columna 'virtual' '[Criteria]', en el cálculo.
El resultado, aplicado en el área de cálculo de nuestro Modelo de datos de Power Pivot en Excel, es el esperado...
Crear una medida en el modelo de datos, sin pasar por columnas calculadas, y poder después usar esa medida con nuestras funciones CUBO.
Recordemos que un punto relevante de este problema era que nuestras tablas de trabajo NO están relacionadas (revisa el post anterior!).
He conseguido escribir una Medida que funciona adecuadamente (desconozco si será la manera más óptima, pero apuesto a que hay formas mejores...).
Pero en todo caso me parece interesante compartirla ya que se emplean algunas de las funciones DAX más curiosas respecto a trabajar con Tablas, Columnas, Filtros...
Veamos la fórmula de nuestra medida:
S_CR:=VAR tabla =
CALCULATETABLE (
ADDCOLUMNS ( Tabla1;
"Criteria";
VAR XX =
FILTER ( TblCRITERIOS;
IF (TblCRITERIOS[Comercial] = "";"1";TblCRITERIOS[Comercial] = Tabla1[Comercial] ) &&
IF (TblCRITERIOS[País] = ""; "1"; TblCRITERIOS[País] = Tabla1[País] ) &&
IF (TblCRITERIOS[Producto] = "";"1";TblCRITERIOS[Producto] = Tabla1[Producto] ) )
RETURN
CALCULATE ( COUNTROWS ( TblCRITERIOS ); XX ) > 0
)
)
RETURN
SUMX(tabla;[Ventas]*[Criteria])
Recuerda que para crear una Medida debes acceder al Modelo de datos, al área de cálculo de una tabla, e insertar el nombre deseado seguido de := (en mi ejemplo S_CR:=).
Con la medida operativa, accederemos a la hoja de cálculo, y como explicaba en artículos anteriores, haciendo uso de la función VALORCUBO.
En concreto, para recuperar el Importe acumulado de todos los años, en la celda Q7 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[S_CR]")
que devuelve los importes correspondientes, sin discriminar por años, de aquellos registros que cumplen los criterios expuesto en el rango de CRITERIOS...
Observa como la función VALORCUBO llama a nuestra Medida 'S_CR'.
De igual forma en Q10 y sucesivas he insertado:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[S_CR]";N10)
Recuperando el valor de la medida, y cruzándola con el conjunto de años de N10.
Ten presente, que en estos casos, donde empleamos Medidas del modelo de datos, necesitaremos 'Actualizar datos' para ver el dato correcto.
El objetivo está conseguido ;-)
Pero vamos a explicar las funciones DAX empleadas para que nuestra medida funcione...
Me apoyaré en DAX Studio (https://daxstudio.org/) para verificar el paso a paso.
Listaré, en primer lugar, las funciones empleadas:
IF(prueba_lógica;valor_si_verdadero;[valor_si_falso])
el clásico condicional que comprueba la prueba lógica y devuelve una valor u otro.
COUNTROWS(tabla)
Cuenta el número de filas de la tabla (o de la expresión que genere una tabla).
CALCULATE(expresión; [filtro1]; [filtro2];...)
Evalúa una expresión (suele ser una medida) en el contexto de los filtros indicados.
FILTER(tabla;filtro_aplicado)
Nos devuelve una tabla, como subconjunto de aquellos registros que cumplan las condiciones de los filtros.
CALCULATETABLE(expresión_tabla; [filtro1]; [filtro2];...)
La función evalúa una tabla (tabla como tal o una función que devuelva una tabla) en el contexto de los filtros indicados.
ADDCOLUMNS(tabla; nombre1; expresión1; [nombre2]; [expresión2];...)
Nos permite añadir una columna calculada a la tabla indicada
VAR - RETURN expresiones DAX que permiten definir y almacenar variables en memoria para su uso posterior como argumento de otras expresiones o medidas. Aquí algún ejemplo (leer más).
Analicemos pues que obtendrímos en cada paso...
En primer lugar incorporaremos la expresión que ya utilizamos en la entrada anterior para cruzar las tablas con la información: Tabla1 versus TblCRITERIOS:
=VAR XX=
FILTER(TblCRITERIOS;
IF(TblCRITERIOS[Comercial]="";"1";TblCRITERIOS[Comercial]=(Tabla1[Comercial]))&&
IF(TblCRITERIOS[País]="";"1";TblCRITERIOS[País]=(Tabla1[País]))&&
IF(TblCRITERIOS[Producto]="";"1";TblCRITERIOS[Producto]=(Tabla1[Producto])))
RETURN
CALCULATE(COUNTROWS(TblCRITERIOS);XX)>0
Esa expresión es la que empleamos en la función ADDCOLUMNS para crear esa columna calculada 'virtual', donde incorporamos una nueva columna (llamada 'Criteria') con el cálculo conocido.
Cálculo que cruza tablas NO relacionadas buscando coincidencias... y devolviendo TRUE o FALSE si en nuestra 'Tabla1', de acuerdo al Comercial, País y Producto cruza con algunas de las opciones de criterios de la tabla TblCRITERIOS.
Se observa en la imagen como replica el comportamiento que veíamos realmente en nuestra tabla.
En el siguiente paso (no sería necesario!!), he preferido añadir la función CALCULATETABLE para asegurar que DAX entiende que es una tabla.
Podemos comprobar que el resultado es la misma tabla de antes... si bien, aquí podríamos haber aprovechado para aplicar algún filtro sobre algún otro campo... Por ejemplo de años:
EVALUATE
VAR tabla =
CALCULATETABLE(
ADDCOLUMNS ( Tabla1,
"Criteria",
VAR XX =
FILTER ( TblCRITERIOS,
IF (TblCRITERIOS[Comercial] = "","1",TblCRITERIOS[Comercial] = Tabla1[Comercial] ) &&
IF (TblCRITERIOS[País] = "", "1", TblCRITERIOS[País] = Tabla1[País] ) &&
IF (TblCRITERIOS[Producto] = "","1",TblCRITERIOS[Producto] = Tabla1[Producto] ) )
RETURN
CALCULATE ( COUNTROWS ( TblCRITERIOS ), XX ) > 0
)
,Tabla1[Año]>=2013, Tabla1[Año]<=2019)
RETURN
tabla
No es nuestro caso, ya que en nuestro ejercicio he optado por aplicar las condiciones de filtro de año con un conjunto expresado en MDX.
Asi pues llegamos al final, donde producimos el cálculo de la medida deseada empleando SUMX:
SUMX(tabla,[Ventas]*[Criteria])
donde como curiosidad hacemos uso de la variable definida como 'tabla', sino que también empleamos nuestra columna 'virtual' '[Criteria]', en el cálculo.
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.