II Entrega

Vamos a seguir avanzando en el estudio de las particularidades de las funciones Window sobre las que realizamos un primer acercamiento en nuestro post anterior:
Primer acercamiento a las funciones WINDOW – (powerbisp.com)

Antes de entrar en casos de uso concretos vamos a ver con mayor detenimiento los elementos que la componen con varios ejemplos de utilidad.

Recordemos la sintaxis:

• Funcion(
  <selección de fila>(obl),

<tabla>(opc),
<orden>(opc),
<blancos>(opc),
<partición>(opc)
<coincidencias>(opc))

Sobre la selección de fila ya vimos el funcionamiento y las opciones: INDEX, OFFSET y WINDOW

Vamos a ver ejemplos de como funciona el parámetro tabla, que también podréis ver con el nombre de Relación. Este elemento, aunque opcional recomiendo siempre fijarlo para no llevarnos sorpresas y corresponde a la tabla sobre la que vamos a elegir las filas de las que realizaremos la selección en el contexto en el que se evalue.

Tiene una característica fundamental y que a veces nos dará algún quebradero de cabeza: debe ser una tabla con elementos únicos. Por ello, siempre que utilicemos una tabla dimensional para nuestros cálculos no tendremos problemas, ya que las dimensiones se entienden que van a ser elementos únicos.

Veamos un sencillo ejemplo:

Tenemos la tabla de ventas por color tal cual se muestra en la imagen:

Si quisiéramos por algún requerimiento traer el importe de ventas del color anterior, utilizaríamos la función OFFSET(-1), pero veamos ejemplos de construcción y qué fallos o excepcionalidades podemos encontrarnos con la función.

Calculo sobre la tabla de colores:

Si calculamos el Offset – 1 de las Ventas sobre la tabla de colores VALUES(Productos[Color]) el resultado es el que vemos en la imagen. Para cada color, devuelve el resultado de BLANK(). Esto es muy sencillo de explicar, cuando utilizamos como tabla cualquiera de los elementos dimensionales de la visual la subtabla que nos va a devolver para cada fila, es la subtabla de las ventas de ese color, ya que hay un filtro de fila que impide que evalue los colores restantes. Es decir el OFFSET -1 del color Blue, en este caso, no existe ya que solo tenemos la fila del contexto blue para evaluar ahí. Sin embargo en el total si vemos que nos arroja cantidad, porque en el contexto del total, donde tenemos todos y cada uno de los colores, el OFFSET -1 viene determinado por todos los colores menos el último y es por ello que nos arroja 24.457.250 como resultado que es la suma de todos los colores menos el último.

Por ello, otra cosa que debemos tener en cuenta es que la tabla de relación tiene que incluir un alterador del contexto que en casi todos los casos, será ALLSELECTED, si queremos que tenga en cuenta otros filtros que puedan existir en el contexto. Si usásemos ALL, estaríamos eliminando absolutamente todos los filtros. Veamos cómo queda con ALLSELECTED:

Con el ALLSELECTED, como podemos observar, ya si nos aparecen las cantidades en cada color pero nos encontramos una pequeña sorpresa, aparecen colores como Grey y como Silver/Black que no tienen ventas, pero que sin embargo forman parte de la tabla de colores.

Vamos a utilizar un pequeño truco para desgranar más que está haciendo la función OFFSET.

Las funciones WINDOW siempre devuelven una tabla, por lo que si en una medida utilizásemos directamente la parte del OFFSET nos devolvería un error en la visual:

Para poder ver en más detalle el comportamiento de OFFSET vamos a utilizar la función TOJSON para ver la tabla que nos devuelve por cada línea:

Aquí podemos analizar exactamente el comportamiento:

Para la fila Black, como el OFFSET -1 no existe (recordemos que no hemos establecido ningún tipo de ordenación y por tanto la ordenación que va a usar por defecto es la alfabética de la tabla en sentido ascendente) mientras que para el color Blue, lo que devuelve es Black. Por ello, en la medida con CALCULATE([Ventas]), al establecer el OFFSET -1 como filtro dentro del calculate, nos devuelve para el color Blue el total de ventas del color Black, que es el filtro que estamos estableciendo en el OFFSET.

Por otro lado, en la fila de totales, podemos observar que el OFFSET -1 devuelve todos los colores menos el último “Yellow” y es por ello que va a devolvernos en el total el total de ventas menos el total de ventas del color yellow como antes hemos comentado.

Esta opción de usar TOJSON para chequear qué nos devuelve la función de ventana puede resultar muy útil cuando queramos analizar el comportamiento de funciones de ventana complejas y que no sepamos por qué nos está devolviendo cualquier resultado.

Pues bien, estábamos en el punto de como tratar o arreglar los valores en blanco que teníamos:

Ojo, igual es este el resultado que pretendemos y ya estaría perfecto, pero vamos a ver otras opciones que nos devolverían otros escenarios diferentes.

Si nos limitamos a no realizar el cálculo en caso de que no existan Ventas, el comportamiento sería como sigue:

Nos hemos limitado a condicionar que haga la medida sólo si existen ventas en el color, pero esto no quita del contexto en el que se evalúa el OFFSET los  colores que no tienen venta, por lo que aunque nos elimina los colores Grey y Silver/Black, que no tienen ventas, sin embargo para Multi y para White si nos está devolviendo el importe en blanco ya que lo que devuelve para ambos casos es el total de ventas del color anterior en la tabla aunque no haya tenido ventas.

Es por ello, que donde tenemos que filtrar la tabla con la que queremos evaluar el OFFSET es en el parámetro de tabla o relación de la medida, siendo de esa tabla de la que tenemos que eliminar de la evaluación los colores que no contienen venta. Para ello debemos proceder como sigue:

Como vemos, la tabla que nos traemos dentro del parámetro relación es la tabla filtrada de colores por venta en las que la venta es >0

FILTER(
                ADDCOLUMNS(
                               SUMMARIZE(ALLSELECTED(Ventas), Productos [Color]),
                               “@Ventas”, [Total Ventas])
                                               [@Ventas]>0 )

De esta forma ya tenemos los OFFSET -1 por color sin tener en cuenta los colores que no han tenido venta.

Hemos dicho al principio que por lo general usaremos ALLSELECTED como modificador del contexto, pero cual sería el comportamiento si usamos ALL?

Con la tabla completa, el resultado es exactamente el mismo:

Sin embargo podemos advertir cambios cuando filtramos para analizar solo algunos colores:

Con ALL, nos va a traer la cantidad del color anterior como si no hubiéramos realizado ningún filtro sobre la tabla, mientras que el ALLSELECTED va a usar solo los colores filtrados para devolver el OFFSET correspondiente. Será la necesidad la que determinará el uso de una u otra opción, lo importante es tener claro qué pasa con cada una de ellas.

Definir la tabla sobre la que queremos extraer las filas es el cometido más importante y más complejo del uso de funciones WINDOW por lo que he querido dedicar un post exclusivamente para hablar de ello mediante este ejemplo. En próximas semanas trataremos varios casos de uso muy buenos para seguir entendiendo y usando las potentísimas funciones de ventana de DAX.