Uso de variables en DAX – R Marketing

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: intermedio

La función variable en DAX ha existido durante más de un año y simplemente nunca he llegado a escribir un post al respecto; lo estoy cambiando hoy. Permítanme comenzar con información y sintaxis de la versión y después les mostraré un ejemplo de la vida real en el que he usado variables para impulsar mi fórmula.

Versiones

Las variables en DAX es una característica relativamente nueva y está habilitada en

  • Power BI Desktop
  • Excel 2016
  • SSAS Tabular 2016

Las variables no están disponibles en Excel 2013 o Excel 2010.

Sintaxis

Regularmente me refiero a las variables como «la sintaxis VAR». En resumen, hay 2 palabras clave nuevas que se usan juntas para hacer que sus fórmulas sean más legibles. Las palabras clave son VAR y REGRESAR. Así es como escribirías una fórmula típica.

myFormula = 
  VAR VariableName1 = <some valid DAX formula>
  VAR VariableName2 = <some other valid DAX formula>
  RETURN
      <another valid DAX formula that can use VariableName1 and VariableName2 as part of the expression>

Notas sobre la sintaxis

Es factible determinar valores escalares como variables, pero además es factible adjudicar tablas como variables. Vea los siguientes ejemplos válidos para adjudicar una variable dentro de una fórmula. Tenga en cuenta que los siguientes son solo ejemplos de la línea VAR. Siempre debe utilizar VAR y RETURN para escribir una fórmula DAX completa válida.

Ejemplo de valor escalar

VAR myScalarValue = SUM(Sales[Extended Amount])

Ejemplo de tabla

VAR myTable = FILTER(Customer, Customer[Post Code] = 50210)

La variable anterior myTable devuelve una tabla virtual que se puede utilizar en cualquier lugar donde se use una tabla en una fórmula DAX, como un filtro dentro de una función CALCULATE.

Otros puntos importantes

  • Cada una de las variables se evalúa una vez antes del comienzo de la parte RETORNO de la fórmula.
  • Las variables pueden referirse a otras variables.
  • Las variables se ejecutan en el filtro inicial y los contextos de fila.
  • Los resultados de los cálculos de variables se almacenan en caché para su reutilización dentro de la fórmula.
  • Una vez que se ha asignado un valor a la variable, ese valor no puede cambiar durante la ejecución de la parte RETURN de la fórmula. Desde esa perspectiva, las variables actúan más como constantes que como variables regulares en un contexto de lenguaje de programación tradicional.

Beneficios

Hay muchos beneficios de utilizar la sintaxis VAR, concretamente cuando además utiliza comentarios en línea.

  • Las fórmulas pueden ser más fáciles de escribir. Puede parecer que tomar más tiempo escribir la fórmula al principio, pero puede terminar ahorrando ese tiempo al llegar a una versión funcional de la fórmula más rápido, con menos reelaboración.
  • Las fórmulas pueden ser más fáciles de leer. Un lector puede comprender la lógica de la fórmula paso a paso y, por eso, puede ser más fácil averiguar para qué está diseñada la fórmula. Esto es concretamente cierto cuando el autor además utiliza comentarios, nuevas líneas y espacios.
  • Las fórmulas pueden ser más fáciles de mantener cuando se necesitan cambios, vea mi ejemplo a continuación.
  • Fórmulas que usan subcadenas repetitivas de DAX se puede escribir una vez y después usarse usualmente. Esto es bueno para la escritura de fórmulas, pero además para el rendimiento (aún cuando DAX ya tenía algunas buenas características de almacenamiento en caché antes de esta sintaxis).
  • Dado que las variables se ejecutan en el contexto de filtro inicial, esta sintaxis puede ser una alternativa más fácil al uso de la función EARLIER (cuando tiene contextos de fila anidados dentro de una fórmula compleja).

super-charge-power-bi-ad_1-1024x128-6595595

Ejemplo de la vida real

Estuve con un cliente la semana pasada construyendo un sistema de seguimiento de incentivos para la fuerza de ventas. Es muy común que la gerencia de ventas quiera medir el desempeño en una gama de impulsores comerciales diferentes para enfocar la fuerza de ventas en lo que es esencial. Como dice el refrán, «lo que se mide se hace». Power BI, Power Pivot y Power Query son herramientas excelentes para este tipo de informes. Las razones incluyen:

  • A menudo, las fuentes de datos son dispares y deben reunirse en un solo reporte.
  • El horizonte de tiempo para el seguimiento es regularmente corto y significa que el costo y el esfuerzo de una solución de BI de TI completa a menudo dificulta la justificación.
  • El esfuerzo para producir manualmente un reporte de este tipo en un ciclo regular (digamos mensualmente) puede ser alto y, por eso, el trabajo tiende a realizarse con menos regularidad (digamos trimestralmente o inclusive semestralmente). Las actualizaciones irregulares de tales herramientas de seguimiento anulan el propósito de todo el ejercicio.

En resumen, mi cliente tiene un premio anual al «equipo del año» que se basa en varios criterios diferentes en muchas fuentes de datos diferentes, incluido el rendimiento de las ventas, la eficacia de la ejecución, la eficacia del lanzamiento de nuevos productos, etc., cosas de ventas bastante estándar. Los equipos reciben una cantidad de puntos en cada área que se refleja en función del rendimiento; los puntos disponibles son relativos a la relevancia de cada impulsor comercial. Los equipos reciben puntos en todas las áreas según el rendimiento y después los puntos se pueden sumar para obtener una puntuación general y, por eso, el equipo ganador.

Asignación de puntos para el crecimiento de las ventas

Voy a compartir la fórmula que escribí para adjudicar puntos solo para una de las áreas: el crecimiento de las ventas. Las reglas comerciales para la asignación de puntos fueron las siguientes:

  • Si declina el año pasado, el equipo no obtiene puntos.
  • Por cada 0,1% de crecimiento entre 0 y 1% respecto al año pasado, el equipo recibe 100 puntos.
  • Por cada 0,1% de crecimiento por encima del 1% a lo largo del año pasado, el equipo recibe 200 puntos.

Primero una decisión de diseño

Lo primero que tuve que elegir fue cómo mantener estas reglas comerciales. Podría pensar en 3 opciones.

  1. Codifique las reglas de negocio en la propia fórmula.
  2. Cree una tabla de parámetros que contenga todos los números clave (a modo de ejemplo, cuántos puntos adjudicar, puntos de activación superiores e inferiores para obtener esos puntos, etc.).
  3. Escribe la fórmula usando la sintaxis VAR.

Rápidamente llegué a la conclusión de que la opción 3 era la mejor para este caso. El esfuerzo para cargar una tabla de parámetros y después extraer los valores para que puedan usarse es relativamente alto en comparación con el uso de la sintaxis VAR. Dado que el uso de las reglas comerciales se limitaba a esta única fórmula, sentí que la sintaxis VAR era la mejor opción.

Fórmula final

Aquí está la fórmula final que terminé escribiendo, pero como suele ser el caso, creo que el procedimiento de escribir la fórmula es mucho más interesante y útil que la fórmula final.

Growth Points =
VAR Points = 100 //Points
VAR DoubleBonusPointsLevel = 0.01 //extra points cut in above 1% growth
VAR Growth = 0.001 //0.1% growth for applying each 100 points
VAR GrowthRounded = 
    ROUNDDOWN([% Growth FYTD], 3) //rounddown to nearest 0.1%
RETURN
    IF(
       GrowthRounded > 0,
       //check to make sure the team is in growth
       GrowthRounded * Points //assign points for growth above 0
           /Growth
           + /* The next portion assigns a further 100 points
                for evrything above 1% growth
                making a total of 200 points for all sales above 1% */
           MAX(
               (GrowthRounded - Double BonusPointsLevel),
               0
           ) //max ensures the formula doesn't remove points if growth is less than 1%
             * Points
             / Growth
     )

Escribiendo la Fórmula

Las variables

Escribir fórmulas a menudo necesita algunas iteraciones y reelaboración; rara vez se trata de escribir la fórmula correcta completa de principio a fin. Para comenzar, escribí variables para cada una de las reglas comerciales que estaba haciendo para que fuera necesario aplicarlas (líneas 2 a 6 anteriores). Pero definitivamente no fue tan simple como escribir cada línea una tras otra. En realidad, terminé usando Excel para construir la fórmula primero para dividir el problema en sus partes componentes. Una vez que tuve la versión de Excel funcionando, fue mucho más fácil escribir el DAX.

La porción de RETORNO

Tenga en cuenta que la función DAX real comienza en la línea 7. En la línea 9 puede ver la primera referencia a una de las variables como entrada en la fórmula final (GrowthRounded para este caso). Al final resultó que, cambié la lógica de la forma en que se asignaron los puntos. Parecía más fácil adjudicar 100 puntos a todas las ventas superiores a 0 y después adjudicar otros 100 puntos a todas las ventas superiores al 1% (en lugar de adjudicar 100 puntos entre 0 y 1% y después adjudicar 200 puntos a las ventas superiores al 1%). Pero, desde luego, todo esto está claramente documentado en la fórmula, por lo que es claro qué se hizo y por qué.

Con suerte, puede ver en este ejemplo que la sintaxis VAR facilita la escritura de fórmulas que otros pueden leer, mantener y comprender.

Cómo evitar la función EARLIER

Como mencioné al principio, la función EARLIER puede ser un poco confusa cuando está aprendiendo DAX. EARLIER se utiliza para referirse a un valor en un contexto de fila anterior, déjame explicarte. Considere las siguientes 2 columnas calculadas en la tabla de clientes de Adventure Works. (tenga en cuenta que no estoy diciendo que deba escribir columnas como esta, solo las estoy usando para demostrar el uso de EARLIER).

Columna calculada 1

Cust Sales = [Total Sales]  // this adds the total lifetime sales for each customer to the customer table.

Columna calculada 2

Customer Lifetime Rank = 
CALCULATE(
     COUNTROWS(Customers),
     FILTER(Customers, Customers[Cust Sales] >= Earlier(Customers[Cust Sales]))
)

Tenga en cuenta el uso de EARLIER en la línea 4 anterior. Esta fórmula es una columna calculada y todas las columnas calculadas disponen un contexto de fila. La función FILTRO además tiene un contexto de fila, por lo que en realidad hay 2 contextos de fila anidados en esta fórmula.

Lo que en realidad dice la línea 4 es esto.

“Filtre la tabla de clientes y solo mantenga los clientes que tengan ventas mayores que las ventas de este cliente que estoy considerando hoy en día para esta fila de la columna calculada”. Se necesita la función EARLIER para poder «escapar» el contexto de la fila interior de la función FILTRO y vuelve a consultar el contexto de fila exterior creado por la columna calculada.

La fórmula se puede considerar así.

Start Calculated Column (row context 1 - outer)
   For each row in row context 1, do the following….
        Start FILTER function (row context 2 - inner)
            For each row in row context 2, do the following….
            Check if this customer has more sales than me
            Next row for row context 2 (ie go to the next customer)
        End FILTER function
   Next row for row context 1
End Calculated Column

Por eso, puede pensar en un contexto de fila como una iteración por medio de cada fila de la tabla para completar la misma tarea para cada fila. Y puede anidar tantos contextos de fila como necesite dentro de otros contextos de fila.

Como mencioné previamente en este post, es factible utilizar la sintaxis VAR como una alternativa a la función EARLIER debido a que las declaraciones VAR se evalúan en el filtro inicial y los contextos de fila. Por lo tanto aquí está la sintaxis alternativa para la columna calculada Categorizar cliente.

Customer lifetime rank =
VAR ThisCustomerSales = Customers[Cust Sales]
RETURN
    CALCULATE(
         COUNTROWS(Customers),
         FILTER(Customers, Customers[Cust Sales] >= ThisCustomerSales)
    )

Esta nueva fórmula arroja el mismo resultado, pero creo que estará de acuerdo en que es más fácil de leer y comprender. El motivo por la que funciona es debido a que la instrucción VAR en la línea 2 se evalúa antes de que la función FILTER cree el contexto de la segunda fila.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.