Funciones
DAX P á g i n a 1 | 55
INDICE .................................................................................................. 4 Función DATE .................................................................................................................. 5 Funciones DAY, MONTH, YEAR, HOUR, MINUTE, SECOND ...................... ............................. ............... ............. ..... 6 Función EDATE ................................................................................................................ 9 Función NOW y TODAY .................................................................................................. 9 Función WEEKDAY .......................................................................................................... 10 Función WEEKNUM ........................................................................................................ 11 ............................................................................................................... 13 Función BLANK B LANK ................................................... ......................... .................................................... ..................................................... ................................... ........ 14 Función CONCATENATE ................................................................................................. 14 Función EXACT ................................................................................................................ 15 Función FIND FIN D.................................................. ........................ ..................................................... ..................................................... ...................................... ............ 16 Funciones LEFT, RIGHT y MID ........................................................................................ 17 Función LEN .................................................................................................................... 18 Funciones LOWER y UPPER ............................................................................................ 18 Función REPT .................................................................................................................. 19 Función SUBSTITUTE ...................................................................................................... 19 Función TRIM .................................................................................................................. 20 Función VALUE VAL UE ................................................... ......................... .................................................... ..................................................... ................................... ........ 21 ....................................................................................................... 22 Función CURRENC CU RRENCY Y..................................................... ........................... .................................................... .................................................... .......................... 23 Función DIVIDE ............................................................................................................... 23 Funciones INT y TRUNC .................................................................................................. 24 Función ROUND .............................................................................................................. 25 Función SUM SU M.................................................. ........................ ..................................................... ..................................................... ...................................... ............ 25 Funciones Lógicas ........................................................................................................... 27 Función AND y OR ........................................................................................................... 28 Función IF ........................................................................................................................ 28 Función IFERROR ............................................................................................................ 29 Función SWITCH ............................................................................................................. 30 Funciones de Filtro ......................................................................................................... 31 ......... ..................................................... .......................... ..................................................... ...................................... ............ 32 Contexto de la fila ........................................................................................................... 32 Contexto de filtro............................................................................................................ 33 Interacciones y el contexto de filtro .............................................................................. 33 Funciones “X” .................................................................................................................. 34 Funciones RELATED / RELATEDTABLE ........................................................................... 38 Función COUNTROWS .................................................................................................... 41 Función FILTER ................................................................................................................ 44 Función DISTINCT ........................................................................................................... 46
P á g i n a 2 | 55
INDICE .................................................................................................. 4 Función DATE .................................................................................................................. 5 Funciones DAY, MONTH, YEAR, HOUR, MINUTE, SECOND ...................... ............................. ............... ............. ..... 6 Función EDATE ................................................................................................................ 9 Función NOW y TODAY .................................................................................................. 9 Función WEEKDAY .......................................................................................................... 10 Función WEEKNUM ........................................................................................................ 11 ............................................................................................................... 13 Función BLANK B LANK ................................................... ......................... .................................................... ..................................................... ................................... ........ 14 Función CONCATENATE ................................................................................................. 14 Función EXACT ................................................................................................................ 15 Función FIND FIN D.................................................. ........................ ..................................................... ..................................................... ...................................... ............ 16 Funciones LEFT, RIGHT y MID ........................................................................................ 17 Función LEN .................................................................................................................... 18 Funciones LOWER y UPPER ............................................................................................ 18 Función REPT .................................................................................................................. 19 Función SUBSTITUTE ...................................................................................................... 19 Función TRIM .................................................................................................................. 20 Función VALUE VAL UE ................................................... ......................... .................................................... ..................................................... ................................... ........ 21 ....................................................................................................... 22 Función CURRENC CU RRENCY Y..................................................... ........................... .................................................... .................................................... .......................... 23 Función DIVIDE ............................................................................................................... 23 Funciones INT y TRUNC .................................................................................................. 24 Función ROUND .............................................................................................................. 25 Función SUM SU M.................................................. ........................ ..................................................... ..................................................... ...................................... ............ 25 Funciones Lógicas ........................................................................................................... 27 Función AND y OR ........................................................................................................... 28 Función IF ........................................................................................................................ 28 Función IFERROR ............................................................................................................ 29 Función SWITCH ............................................................................................................. 30 Funciones de Filtro ......................................................................................................... 31 ......... ..................................................... .......................... ..................................................... ...................................... ............ 32 Contexto de la fila ........................................................................................................... 32 Contexto de filtro............................................................................................................ 33 Interacciones y el contexto de filtro .............................................................................. 33 Funciones “X” .................................................................................................................. 34 Funciones RELATED / RELATEDTABLE ........................................................................... 38 Función COUNTROWS .................................................................................................... 41 Función FILTER ................................................................................................................ 44 Función DISTINCT ........................................................................................................... 46
P á g i n a 2 | 55
Contexto de Fila y Contexto de Filtro ............................................................................ 48 Contexto de Fila .............................................................................................................. 48 Contexto de Filtro ........................................................................................................... 48 Función CALCULATE ....................................................................................................... 49 Función VALUES VALU ES ................................................. ....................... .................................................... ..................................................... ................................... ........ 54
P á g i n a 3 | 55
FUNCIONES DE FECHA Y HORA. Muchas de las funciones de fecha y hora en DAX son muy similares a las funciones de fecha y hora de Excel. No obstante, las funciones DAX pueden usar los valores de una columna como argumento. DAX también incluye un conjunto de funciones de inteligencia de tiempo que nos permiten manipular datos mediante períodos de tiempo, incluidos días, meses, trimestres y años y, a continuación, compilar y comparar cálculos durante esos períodos. En éste video se estudiarán las siguientes funciones:
DATE DAY MONTH YEAR HOUR MINUTE SECOND EDATE NOW TODAY WEEKDAY WEEKNUM
P á g i n a 4 | 55
Función DATE
Devuelve la fecha especificada pasados como argumentos el año, el mes y el día como números enteros. =DATE(año;mes;día) año
Número que representa el año. Puede incluir de uno a cuatro dígitos. Siempre que sea posible, debe usar cuatro dígitos para evitar resultados no deseados. Por ejemplo, al usar 07 se devuelve 1907 como el valor de año. Se admiten las fechas que comienzan con el 1 de marzo de 1900.
mes
Número que representa el mes o un cálculo según las siguientes reglas: Si escribe un entero mayor que 12, se produce el siguiente cálculo: la fecha se calcula sumando el valor de mes al año. Por ejemplo, si tiene DATE(2014, 18, 1), la función devuelve un equivalente del valor del 1 junio de 2015, porque se suman 18 meses al principio de 2014, 20 14, lo que da como resultado el valor de junio de 2015. Si escribe un entero negativo, se produce el siguiente cálculo: se calcula la fecha restando el valor de mes al año. Por ejemplo, si tiene DATE(2014, -6, 15), la función devuelve el equivalente del valor del 15 de junio de 2013, porque cuando se restan seis meses de principios de 2014, el resultado es junio de 2013.
día
Número que representa el día o un cálculo según las siguientes reglas: Si escribe un entero mayor que el último día del mes dado, se produce el siguiente cálculo: la fecha se calcula sumando el valor de día al mes. Por ejemplo, en la fórmula DATE( 2014, 3, 32), devuelve un valor equivalente al 1 de abril de 2014, porque se suman 32 días al primero de marzo, lo que da como resultado el 1 de abril. Si escribe un entero negativo, se produce el siguiente cálculo: se calcula la fecha restando el valor de dia al mes. Por ejemplo, en la fórmula DATE( 2014, 5, -15), the DATE function devuelve un valor equivalente al 15 de abril de 2014, porque se resta 15 días del primero de mayo de 2014, lo que da como resultado abril de 2014.
P á g i n a 5 | 55
Funciones DAY, MONTH, YEAR, HOUR, MINUTE, SECOND
Devuelven el día, mes, año, hora, minutos y segundos de una fecha. DAY(fecha) MONTH(fecha) YEAR(fecha) HOUR(fecha) MINUTE(fecha) SECOND(fecha)
P á g i n a 6 | 55
P á g i n a 7 | 55
Evidentemente todas estas funciones se pueden utilizar como argumento de funciones lógicas, se verán más adelante, como por ejemplo en un IF:
P á g i n a 8 | 55
Función EDATE
Devuelve la fecha, que corresponde al número indicado de meses antes o después de la fecha de inicio. Use EDATE para calcular las fechas de vencimiento que tienen lugar el mismo día del mes que la fecha de emisión. =EDATE(fecha_inicio; meses) fecha_inicio Fecha que representa la fecha de inicio. meses
Entero que representa el número de meses antes o después de fecha_inicio.
En el ejemplo siguiente se devuelve la fecha tres meses posterior a la fecha de actualización del pedido, que está almacenada en la columna [UpdateDate] de la tabla [DimProduct]:
Funciones NOW y TODAY
Devuelven la fecha y hora actuales. La función NOW devuelve la hora exacta mientras que TODAY devuelve el valor de hora 00:00:00 para todas las fechas.
P á g i n a 9 | 55
Si sabe que una persona nació en 1963, puede usar la fórmula siguiente para buscar la edad de dicha persona a partir del cumpleaños de este año: =YEAR(TODAY()) - 1963
Función WEEKDAY
Devuelve un número de 1 a 7 para identificar el día de la semana de una fecha. De forma predeterminada, los días están comprendidos entre 1 (domingo) y 7 (sábado). WEEKDAY(fecha; tipo) P á g i n a 10 | 55
fecha
Fecha.
tipo
Un número que determina el valor devuelto: 1 La semana comienza en domingo (1) y termina el sábado (7). Es el valor predeterminado. 2 La semana comienza en lunes (1) y termina el domingo (7). 3 La semana comienza en lunes (0) y termina el domingo (6).
El ejemplo siguiente obtiene el número del día de la semana de la columna [UpdateDate] de la tabla [DimProduct], comenzando en Lunes: =WEEKDAY([UpdateDate];2)
Función WEEKNUM
Devuelve el número de semana de una fecha según el valor de tipo. WEEKNUM(fecha; tipo) fecha
Fecha.
tipo
Un número que determina el valor devuelto: 1 La semana comienza el domingo. Valor predeterminado. 2 La semana comienza el lunes.
De forma predeterminada, la función WEEKNUM usa una convención de calendario en la que la semana que contiene el 1 de enero se considera que es la primera del año. No obstante, el estándar de calendario ISO 8601, que se usa ampliamente en Europa, define P á g i n a 11 | 55
que la primera semana es aquella en la que la mayoría de los días (cuatro o más) están en el nuevo año. Esto significa que en los años en los que hay tres o menos días en la primera semana de enero, la función WEEKNUM devuelve números de semana que son diferentes de la definición ISO 8601. En el ejemplo siguiente se devuelve el número de semana de la fecha almacenada en la columna [UpdateDate] de la tabla [DimProduct], empezando en Lunes. =WEEKNUM([UpdateDate];2)
P á g i n a 12 | 55
FUNCIONES DE TEXTO. Las Expresiones de análisis de datos (DAX) incluyen un conjunto de funciones de texto que está basado en la biblioteca de funciones de cadenas de Excel, pero modificadas para trabajar con tablas y columnas. En éste video se estudiarán las siguientes funciones:
BLANK CONCATENATE / & EXACT FIND LEFT / RIGHT / MID LEN LOWER / UPPER REPT SUBSTITUTE TRIM VALUE
P á g i n a 13 | 55
Función BLANK
Devuelve un valor en blanco. =BLANK() Los valores en blanco no equivalen a valores nulos. Algunas funciones DAX tratan las celdas en blanco de un modo algo distinto a como lo hace Microsoft Excel. Los valores en blanco y lo cadenas vacías ("") no siempre son equivalentes, pero algunas operaciones pueden tratarlos como tales. Por ejemplo, si el campo [TotalSalesAmount] de la tabla [DimStore[ > 22.000.000, devolvemos “OK”, en caso contrario, devolvemos un valor en blanco.
=IF([TotalSalesAmount]>22000000;"OK";BLANK())
Función CONCATENATE
Combina dos cadenas de texto en una sola. =CONCATENATE(texto1; texto2) texto1; Cadenas de texto que se van a combinar en una única cadena de texto2 texto. Las cadenas pueden incluir texto o números. También puede utilizar referencias de columna.
P á g i n a 14 | 55
La función CONCATENATE de DAX acepta solo dos argumentos, mientras que la función CONCATENATE de Excel acepta hasta 255 argumentos. Si necesita concatenar varias columnas, puede crear una serie de cálculos o, mejor, utilizar el operador de concatenación (&) para unir todos ellos en una expresión más sencilla. Si desea usar cadenas de texto directamente, en lugar de usar una referencia de columna, cada cadena se debe escribir entre comillas dobles.
Función EXACT
Compara dos cadenas de texto y devuelve TRUE si son exactamente iguales y FALSE si no es así. EXACT distingue entre mayúsculas y minúsculas, pero omite las diferencias de formato. =EXACT(texto1; texto2)
P á g i n a 15 | 55
Función FIND
Devuelve la posición inicial de una cadena de texto dentro de otra. FIND distingue mayúsculas de minúsculas. =FIND(texto_a_buscar; texto_destino; comienzo; no_encuentra) texto_a_buscar Texto que desea buscar. Use las comillas dobles (el texto vacío) para hacer coincidir el primer carácter de texto_destino. Puede usar caracteres comodín, el signo de interrogación (?) y asterisco (*), en texto_a_buscar. Un signo de interrogación devuelve cualquier carácter individual; un asterisco devuelve cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco real, escriba la tilde (~) antes del carácter. texto_destino
Texto que contiene el texto que desea buscar.
comienzo
(Opcional) Carácter en el que se iniciará la búsqueda; si se omite, comienzo = 1. El primer carácter de texto_destino es el número de carácter 1.
no_encuentra
(Opcional) el valor que se devuelve cuando la operación no encuentra una subcadena coincidente, normalmente 0, -1 o BLANK().
Por ejemplo: vamos a devolver la posición del texto “line” dentro de la columna
[ChannelName] de la tabla [DimChannel]: =FIND("line";[ChannelName];1;blank())
P á g i n a 16 | 55
Funciones LEFT, RIGHT y MID
Devuelven el número de caracteres especificado desde el inicio de una cadena de texto, el último carácter o caracteres en una cadena de texto o una cadena de caracteres de la zona media de una cadena de texto, dada la posición inicial y la longitud, según el número de caracteres que especifique, respectivamente =LEFT(texto; caracteres) =RIGHT(texto; caracteres) =MID(texto; posición; caracteres) Por ejemplo, de la tabla [DimStore] vamos a sacar información parcial sobre la columna [StorePhone]:
P á g i n a 17 | 55
Función LEN
Devuelve el número de caracteres de una cadena de texto. =LEN(texto) texto
Texto cuya longitud desea encontrar o una columna que lo contiene. Los espacios cuentan como caracteres.
Funciones LOWER y UPPER
Convierten en minúsculas y mayúsculas, respectivamente todas las letras de una cadena de texto. =LOWER(texto) =UPPER(texto)
P á g i n a 18 | 55
Función REPT
Repite el texto un número determinado de veces. Utilice REPT para llenar una celda con varias instancias de una cadena de texto. =REPT(texto; número) texto
Texto que desea repetir.
número
Número positivo que especifica el número de veces que repetir el texto.
Función SUBSTITUTE
Reemplaza el texto existente por otro en una cadena de texto. =SUBSTITUTE(texto; texto_antiguo; texto_nuevo; veces) texto
Texto en el que desea sustituir caracteres o una referencia a una columna que contenga texto.
texto_antiguo Es el texto existente que desea reemplazar. texto_nuevo
Texto con el que desea reemplazar texto_antiguo.
veces
(Opcional) La aparición de texto_antiguo que desea reemplazar. Si se omite, se reemplaza cada aparición de texto_antiguo.
P á g i n a 19 | 55
Por ejemplo, crear una columna que sobre la columna [ProductName] de la tabla [DimProduct], reemplace “MP3” por “MP4”:
Función TRIM
Quita todos los espacios del texto salvo el espacio entre una palabra y otra. =TRIM(texto) Utilice TRIM en el texto que ha recibido de otra aplicación que puede tener un espaciado irregular.
P á g i n a 20 | 55
Función VALUE
Convierte en un número una cadena de texto que representa un número. =VALUE(texto)
P á g i n a 21 | 55
FUNCIONES MATEMÁTICAS Las funciones matemáticas de las Expresiones de análisis de datos (DAX) son muy parecidas a las funciones matemáticas y trigonométricas de Excel. En éste video se estudiarán las siguientes funciones:
CURRENCY DIVIDE INT / TRUNC ROUND SUM
P á g i n a 22 | 55
Función CURRENCY
Evalúa el argumento y devuelve el resultado como tipo de datos de moneda. =CURRENCY(valor)
Función DIVIDE
Realiza la división y devuelve un resultado alternativo o BLANK() al dividirlo entre 0. =DIVIDE(numerador; denominador; alternativa) numerador
Dividendo o número que se divide.
denominador Divisor o número por el que se divide. alternativa
(Opcional) El valor devuelto cuando la división entre cero da como resultado un error. Cuando no se proporciona, el valor predeterminado es BLANK().
P á g i n a 23 | 55
Funciones INT y TRUNC
INT redondea un número al entero más cercano mientras que TRUNC trunca un número en un entero al quitar la parte decimal, o fraccionaria, del número. =INT(número) =TRUNC(número) TRUNC e INT son parecidos porque ambos devuelven enteros. TRUNC quita la parte fraccionaria del número. INT redondea los números al entero más cercano basado en el valor de la parte fraccionaria del número. INT y TRUNC solo son diferentes cuando se utilizan números negativos: TRUNC(-4.3) devuelve -4, pero INT(-4.3) devuelve -5 porque -5 es el valor más bajo.
P á g i n a 24 | 55
Función ROUND
Redondea un número al número de dígitos especificado. =ROUND(número; dígitos) número
Número que desea redondear.
dígitos
Número de dígitos a los que desea redondear. Un valor negativo redondea los dígitos a la izquierda del separador decimal; un valor de cero redondea al entero más cercano. Si dígitos es mayor que 0 (cero), el número se redondea al número especificado de posiciones decimales. Si dígitos es 0, el número se redondea al entero más cercano. Si dígitos es menor que 0, entonces se redondea a la izquierda del separador decimal.
Función SUM
Suma todos los números de una columna. =SUM(columna) columna
Columna que contiene los números que se suman.
Si desea filtrar los valores que está sumando, puede usar la función SUMX y especificar una expresión para sumar. P á g i n a 25 | 55
P á g i n a 26 | 55
FUNCIONES LÓGICAS Las funciones lógicas actúan sobre una expresión para devolver información acerca de los valores o conjuntos de la expresión. Por ejemplo, puede utilizar la función IF para comprobar el resultado de una expresión y crear resultados condicionales. En éste video se estudiarán las siguientes funciones:
AND / OR IF IFERROR SWITCH
P á g i n a 27 | 55
Funciones AND y OR
AND comprueba si ambos argumentos son TRUE y devuelve TRUE en ese caso. OR comprueba si uno de los argumentos es TRUE para devolver TRUE. La función devuelve FALSE si ambos argumentos son FALSE. =AND(expresion1; expresion2) =OR(expresion1; expresion2) Ambas funciones sólo aceptan dos argumentos. Si necesitamos realizar operaciones con varias expresiones, utilizaremos el operador AND (&&) o el operador OR (||) para unirlos todos en una expresión más sencilla. Por ejemplo, en la tabla [DimPromotion] creamos una columna calculada que devuelva TRUE o FALSE cuando el año de la columna [StartDate] está comprendida entre los años 2003 y 2006:
Función IF
Comprueba si se cumple la condición proporcionada como primer argumento. Devuelve un valor si la condición es TRUE y devuelve otro valor si la condición es FALSE. =IF(condición; verdadero; falso) Por ejemplo si el valor de la columna [TotalSalesAmount] de la tabla [DimProduct] es menor que 300.000 devolverá “BAJO”. Si es menor que 500.000 devolverá “MEDIO” y “ALTO” para todos los demás valores.
P á g i n a 28 | 55
Función IFERROR
Evalúa una expresión y devuelve un valor especificado si la expresión devuelve un error; en caso contrario devuelve el valor de la expresión. =IFERROR(valor; valor_si_error) Por ejemplo, en la tabla [DimPromotion] dividimos la columna [ETLLoadID] entre [DiscountPercent]. Como al dividir por 0, devuelve un error, devolvemos 0.
P á g i n a 29 | 55
Función SWITCH
Evalúa una expresión con lista de valores y devuelve una de las varias expresiones de resultado posibles. =SWITCH(expresión; valor; resultado [; valor; resultado]) Por ejemplo, crear una columna en la tabla [DimStore] que devuelva el nombre del día de la semana de la columna [UpdateDate]:
P á g i n a 30 | 55
FUNCIONES DE FILTRO. Las funciones del filtro y valor en DAX son las más complejas y eficaces, y difieren mucho de las funciones de Excel. Las funciones de búsqueda usan tablas y relaciones, como una base de datos. Las funciones de filtrado permiten manipular el contexto de los datos para crear cálculos dinámicos. En éste video se estudiarán las siguientes funciones:
Funciones de agregado – funciones “X” RELATED / RELATEDTABLE COUNTROWS FILTER DISTINCT CALCULATE VALUES
P á g i n a 31 | 55
Introducción al contexto – contexto de fila y contexto de filtro
Uno de los conceptos DAX que es importante entender es el concepto de "contexto" en que se evalúa cada expresión de DAX. Este tema será examinado en más detalle más adelante en este documento (después se describen algunas funciones DAX, así que pueden ser demostrados significativos ejemplos), pero por ahora, basta con tener en cuenta que cada fórmula puede evaluarse en el contexto de una fila determinada de datos de la tabla (un "contexto de la fila") o en el contexto de un conjunto específico de filtros (un "contexto de filtro"). Por ejemplo, se evaluará la fórmula para una columna calculada para cada fila de una tabla, utilizando el "contexto de la fila". Asimismo, un campo calculado que se introduce en una tabla dinámica se evaluará para cada celda en el área de valores, y cada una de esas celdas tiene su propio "contexto de filtro" que es la combinación de las etiquetas de fila de la celda, etiquetas de columna y filtros de informe. Contexto de la fila
P á g i n a 32 | 55
Contexto de filtro
Por ejemplo, la celda señalada tiene un contexto de filtro con dos filtros: País = Dinamarca y Año = 2008. Es decir para calcular Sales se utilizan dos filtros. De ahí el viene el nombre de contexto de filtro.
Las interrelaciones y el contexto de filtro
Las interrelaciones entre tablas son del tipo uno a muchos. En el lado muchos está la clave foránea y en el lado del uno, la clave primaria. Cuando hay una interrelación y se aplica un filtro a una tabla en el lado uno, también se filtrará la tabla del lado muchos. FactSales FactInventory
SalesKey DateKey
DimChannel
InventoryKey DateKey
channelKey StoreKey
DimPromotion
DimDate DimStore
StoreKey
ProductKey
ProductKey
PromotionKey
CurrencyKey
CurrencyKey
DimGeography
OnHandQuantity
UnitCost
OnOrderQuantity
UnitPrice
SafetyStockQuantity
SalesQuantity
DimProduct
UnitCost
ReturnQuantity
DaysInStock
ReturnAmount
MinDayInStock
DiscountQuantity
DimProductSubcategory
MaxDayInStock
DiscountAmount
Aging
TotalCost
ETLLoadID
SalesAmount ETLLoadID LoadDate UpdateDate
DimProductCategory
LoadDate
P á g UpdateDate i n a 33 | 55
Así si filtramos [DimProducts] para un único producto, entonces [FactSales] también se filtrará para que aparezcan sólo las transacciones de ese producto. Sin embargo, aplicar un filtro a la tabla [FactSAles] no tendría ningún impacto sobre la tabla [DimProducts]. Funciones de agregado – Funciones
“X”
Como ya sabemos, las consultas con totales son la base teórica de las tablas dinámicas y estas, a su vez, utilizan funciones de agregado. Estas funciones ya existen en Excel. Son, entre otras, SUM, AVG, MIN, MAX, COUNT. A diferencia de Excel, en DAX se han añadido algunas nuevas funciones de agregación que admiten como argumento la tabla y una expresión:
SUMX (tabla, expresión) AVERAGEX (tabla, expresión) COUNTAX (tabla, expresión) MINX (tabla, expresión) MAXX (tabla, expresión)
Así SUMX permite realizar la suma de un campo numérico aplicándole diferentes filtros que vendrán dados por la expresión especificada en el segundo argumento. Por ejemplo: =SUMX (FactSales; [UnitPrice] * [SalesQuantity]) Esta fórmula dice que deberíamos empezar con la tabla FactSales, y para cada fila de la tabla deberíamos evaluar la expresión [UnitPrice] * [SalesQuantity]. A continuación, los resultados deben todos ser sumados porque estamos usando la función SUMX. Si hubiéramos usado AVERAGEX, nos tomaría el promedio de todos los resultados.
P á g i n a 34 | 55
La tabla FactSales está relacionada con DimChannel a través del campo ChannelKey (Tienda)
Si en la tabla dinámica “Ventas relativas a tiendas”, creamos el campo anterior:
P á g i n a 35 | 55
La función anterior cogerá cada una de las tiendas (Channelkey) de la tabla DimChannel, y en la tabla FactSales calculará la suma de UnitPrice * SalesQuantity. Obtendremos el subtotal para las ventas pertenecientes a un determinado año y tienda ya que la tabla dinámica tiene en filas la tienda y en columnas el año. El campo calculado, en la tabla DimChannel se muestra:
P á g i n a 36 | 55
Que coincide con el total de la tabla dinámica:
P á g i n a 37 | 55
Funciones RELATED / RELATEDTABLE
DAX introduce las funciones RELATED y RELATEDTABLE para seguir las relaciones y recuperar datos relacionados de otra tabla. Esto es más poderoso que BUSCARV en Excel por dos motivos. En primer lugar, BUSCARV devuelve sólo la primera ocurrencia – no hay ninguna promesa de integridad referencial y no hay garantía de que no había otra fila que también habría igualado la búsqueda. En segundo lugar, no depende de la colocación de las columnas en la tabla de búsqueda, ya que la función BUSCARV no permite devolver hacia la izquierda. =RELATED(columna) RELATED sigue una relación de uno_a_muchos y devuelve el valor relacionado de la otra tabla. En la tabla FactSales, vamos a añadir dos columnas calculadas usando estas fórmulas: =RELATED(DimStore[StoreName]) =RELATED(DimGeography[ContinentName]) Estas fórmulas agregará dos columnas a la tabla FactSales, la primera que contiene el nombre de cada tienda y el segundo que contiene el nombre del continente. El primero de estos ejemplos sigue una sola relación FactSales a DimStore, mientras que la segunda fórmula debe navegar dos relaciones: de FactSales a DimStore y luego de DimStore a DimGeography. Estas fórmulas sólo requieren conocer el nombre de la columna que desea devolver el valor.
P á g i n a 38 | 55
P á g i n a 39 | 55
=RELATEDTABLE(tabla) RELATEDTABLE (tabla) sigue una relación en ambos sentidos (uno_a_muchos y muchos_a_uno) y devuelve una tabla que contiene todas las filas que están relacionadas con la fila actual de la tabla especificada. Esto es muy útil cuando necesitamos encontrar todas las transacciones asociadas con una fila determinada de una tabla relacionada. Tenga en cuenta que esta función devuelve una tabla y no un valor escalar. Esto significa que esta función no puede utilizarse por sí misma para definir una columna calculada. En cambio esta función puede utilizarse para proporcionar un resultado intermedio que a su vez es un argumento para otra función, como una función de agregación. Por ejemplo: =SUMX(RELATEDTABLE(FactSales); FactSales[SalesAmount])
Esta fórmula dice que queremos construir primero una tabla que contiene las filas de FactSales que se relacionan con la fila actual. RELATEDTABLE(FactSales) asume que tenemos una fila actual y que existe una relación entre la tabla y la tabla FactSales. Una vez tenemos la tabla que contiene las filas relacionadas de las transacciones de ventas, se coge la cantidad de ventas de cada fila [SalesAmount] y luego suma todas estas cantidades. Por ejemplo, si ponemos esta fórmula en las tablas [DimProduct] y [DimStore] podemos conocer el total de las transacciones de ventas desglosadas por cada producto y por cada tienda. P á g i n a 40 | 55
NOTA IMPORTANTE.- Recordamos que en la versión 2010, los parámetros se separan por comas, mientras que en la versión 2013 se separan por puntos y comas.
Función COUNTROWS
La función COUNTROWS cuenta el número de filas de la tabla especificada o de una tabla definida por una expresión. P á g i n a 41 | 55
=COUNTROWS(tabla) Esta función se puede utilizar para contar el número de filas de una tabla base, pero se suele usar con más frecuencia para contar el número de filas que es el resultado de filtrar una tabla o de aplicar contexto a una tabla. En los ejemplos siguientes se demuestra cómo utilizar COUNTROWS con un contexto de fila. En este escenario, la tabla [DimProduct] está relacionada con la tabla [FactSales] a través de la columna [ProductKey]:
P á g i n a 42 | 55
Esta expresión nos devuelve el número de registros de la tabla [FactSales] pero no nos devuelve el número de registros relacionados de cada producto.
Esta expresión nos devuelve el número de registros relacionados de cada fila a través del campo relacionado [ProductKey]. Para comprobar la primera línea, el [ProductKey] es el código 1. Si filtramos manualmente la tabla [FactSAles] por el [ProductKey] = 1 y observamos el resultado:
Hay 1.819 registros que coincide con lo que nos dice la expresión:
P á g i n a 43 | 55
Función FILTER
FILTER es una función DAX que devuelve una tabla de resultados, dependiendo de una condición. =FILTER (tabla; condición) Dado que devuelve una tabla, no puede introducirse directamente en un campo calculado. En su lugar se utilizará como argumento de otras funciones, normalmente las funciones de agregación. Por ejemplo, la expresión: =FILTER(FactSales;RELATED(DimGeography[CityName])="Baltimore") devolverá una tabla que contiene todas las filas de FactSales que tuvo lugar en la ciudad de Baltimore. Para obtener las ventas totales de la ciudad de Baltimore, simplemente la usaríamos como primer parámetro de la función SUMX: =SUMX (FILTER(FactSales;RELATED(DimGeography[CityName])="Baltimore");[SalesAmount])
La tabla [DimChannel] está relacionada con la tabla [FactSales]. Si creamos una columna calculada en la tabla [DimChannel] obtendremos el mismo valor para todos los registros ya que está utilizando el contexto de fila, pero si creamos un campo calculado y una tabla dinámica, observaremos la diferencia de resultados:
P á g i n a 44 | 55
Por motivo de los datos de las tablas implicadas y relacionadas, observamos que solo hay ventas en el canal “Store”, pero tenemos las ventas segmentadas por años y el total general coincide con la columna calculada de la tabla [DimChannel].
P á g i n a 45 | 55
Función DISTINCT
Devuelve una tabla de una columna que contiene los valores distintos de la columna especificada. Se trata pues, de otro filtro que nos permite obtener una columna con valores únicos. =DISTINCT(columna) Por ejemplo si queremos saber cuántas tiendas distintas intervienen en las transacciones, haríamos: =COUNTROWS(DISTINCT(FactSales[StoreKey]))
P á g i n a 46 | 55
P á g i n a 47 | 55
Contexto de Fila y Contexto de Filtro Ahora que muchas de las funciones básicas de DAX se han descrito, echemos un vistazo a algunos ejemplos de lo que entendemos por "Contexto de Fila" y "Contexto de Filtro" porque representan conceptos que debemos entender para poder trabajar con algunas de las funciones más avanzadas de DAX como CALCULATE, descrito más adelante. Contexto de Fila Contexto de Fila es considerado como la "fila actual". Esto es más evidente en los casos donde una fórmula está siendo introducida en una columna calculada. Por ejemplo, dentro de la tabla FactSales, podemos crear una fórmula para una columna calculada:
= [SalesQuantity] * [UnitCost]
En este escenario, estamos tomando los valores de la [SalesQuantity] y el [UnitCost] las columnas de la fila actual y multiplicar los dos números juntos. Esto parece tanto obvio e intuitivo porque el mismo enfoque funciona en Excel. Pero es importante observar que DAX toma el nombre de una columna ([UnitCost]) y lo interpreta como un valor único cuando hay un contexto de fila. Contexto de Filtro El contexto de filtro es más complejo que el contexto de fila y puede describirse más fácilmente como el conjunto de filtros asociados con una de las celdas en el área de los valores de una tabla dinámica. Considere la siguiente tabla dinámica:
P á g i n a 48 | 55
Esta tabla dinámica tiene solamente un campo calculado Sales, con la fórmula =SUM(FactSales[SalesAmount]). Esta fórmula única está siendo evaluado 72 veces distintas (número de etiquetas de fila x número de etiquetas de columna), con 72 resultados diferentes, y cada uno de esos 72 resultados tiene un distinto Contexto de filtro. La celda que está resaltada tiene el siguiente contexto de filtro: [RegionCountryName] = "Spain" [CalendarYear] = 2009 [ChannelName] = "Store" [ContinentName] = "Europa" Esto puede ser pensado como aplicar cuatro filtros a la tabla FactSales antes de evaluar la fórmula que es simplemente la suma de los valores de [SalesAmount] para las filas que quedan después de aplicar esos cuatro filtros. Esto es de donde viene el nombre "Contexto de Filtro".
Función CALCULATE
La función CALCULATE es muy potente y muy útil, pero es un poco más difícil que cualquiera de las funciones DAX descritas anteriormente. =CALCULATE(expresión; filtro1; filtro2;……)
P á g i n a 49 | 55
CALCULATE permite cualquier expresión de DAX a evaluarse en un contexto de filtro especificado. Esto es equivalente a definir un campo calculado en una tabla dinámica. CALCULATE hace lo siguiente:
Usando los argumentos de filtro, modifica el contexto de filtro. Si hay un contexto de fila, lo utiliza como contexto de filtro. Evalúa la expresión en el contexto de filtro recién modificado.
Estos son algunos ejemplos que ilustran cómo funciona. Ejemplo 1.- CALCULATE sin argumentos de filtro
Como primer ejemplo de utilización, escriba una fórmula en una columna calculada de DimStore la fórmula: =COUNTROWS(DimGeography)
Esto nos dará el mismo valor en todas las filas de 674 registros que son los registros que tiene la tabla DimGeography.
P á g i n a 50 | 55
Sin embargo, si ahora escribimos en otra columna calculada: =CALCULATE(COUNTROWS(DimGeography))
Encontrará que en todas las filas aparece un 1. La consecuencia es el contexto de filtro que se ha aplicado debido a la interrelación entre las dos tablas: solo un registro de la tabla DimGeography está relacionado con el registro de la tabla DimStore. Otro ejemplo: Las ventas del libro que estamos tratando se pueden realizar de cuatro canales distintos: por catálogo, online, por distribuidos y por tienda (Store).
P á g i n a 51 | 55
Mediante el campo ChannelKey en la tabla FactSales se identifica el canal utilizado en la transacción.
Supongamos que necesitamos realizar el análisis de ventas pero solo utilizando el canal de tienda (Store). La expresión: =CALCULATE([Sales]; DimChannel[ChannelName] = “Store”) hará que en la tabla dinámica “Ventas por país” solo se tengan en cuenta las ventas que se
han hecho por tienda. La expresión anterior se podría haber escrito también: =CALCULATE([Sales]; DimChannel[ChannelKey] = “1”)
P á g i n a 52 | 55
Observe el resultado ya que según los datos almacenados, las ventas en Dinamarca o Alemania son diferentes en función del canal que analicemos. Luego observe la diferencia que hay entre [Sales] y [Ventas por tienda].
P á g i n a 53 | 55
Función VALUES
La función VALUES devuelve todos los valores válidos para esta columna en el actual contexto de filtro. =VALUES(columna) Aunque la tabla devuelta tenga un solo valor (o incluso ningún valor), seguirá siendo una tabla. Con esta función podremos conocer fácilmente el contexto de filtro actual. Esta función se utilizará a menudo dentro de una función IF. Imaginemos un escenario donde necesitamos hacer un cálculo cuando el país es España y un cálculo diferente cuando el país no es España. Esto puede lograrse mediante: =VALUES(DimGeography[RegionCountryName]) = "Spain" Por supuesto puede haber una tabla dinámica donde no se especifica el país, o una situación donde varios países han sido seleccionados y sólo podemos comparar una tabla a un solo valor cuando la tabla tiene un solo valor. Esto nos obliga a escribir algo como esto para determinar lo que es el país actual: = IF (COUNTROWS(VALUES(DimGeography[RegionCountryName])) = 1; VALUES(DimGeography[RegionCountryName]); "Ningún país seleccionado") Esta fórmula dice que si hay sólo un país seleccionado en el contexto actual, devuelva el nombre de ese país, y, en otro caso, devuelva la cadena "Ningún país seleccionado". El resultado de la colocación de este campo calculado en una tabla dinámica donde el país está en las etiquetas de fila se ve así:
P á g i n a 54 | 55
