Início rápido: Aprenda os fundamentos de DAX em 30 minutos

Tarefa: criar uma fórmula simples para uma coluna calculada

1. Se você ainda não estiver na janela Power Pivot, no Excel, na faixa de Power Pivot, clique em Power Pivot Janela.

2. Na janela Power Pivot, clique na tabela FactSales (guia).

3. Role para a coluna mais à direita e, em seguida, no cabeçalho da coluna, clique em Adicionar Coluna.

4. Clique na barra de fórmulas na parte superior da janela do designer de modelos.

   

   Seu cursor agora aparece na barra de fórmulas. A barra de fórmulas é onde você pode digitar uma fórmula para uma coluna calculada ou um campo calculado.

   Agora vamos observar os três botões à esquerda da barra de fórmulas.

   

   Quando o cursor está ativo na barra de fórmulas, esses três botões ficam ativos. O botão mais à esquerda, o X, é simplesmente um botão de cancelamento. Continue e clique nele. Seu cursor não aparece mais na barra de fórmulas e o botão de cancelamento e o botão de marca de seleção não aparecem mais. Prossiga e clique na barra de fórmulas novamente. O botão de cancelamento e o botão de marca de seleção reaparecem agora. Isto significa que você está pronto para iniciar a inserção de uma fórmula.

   O botão da marca de seleção é o botão da fórmula de verificação. Ele não faz muita coisa até você inserir uma fórmula. Nós voltaremos a isto mais à frente.

   Clique no botão Fx. Você verá que uma nova caixa de diálogo é exibida: a caixa de diálogo Inserir Função. A caixa de diálogo Inserir Função é o modo mais fácil de começar a inserir uma fórmula DAX. Vamos adicionar uma função a uma fórmula quando criarmos uma medida um pouco mais tarde, mas, por agora, não precisa de adicionar uma função à fórmula de coluna calculada. Continue e feche a caixa de diálogo Inserir Função.

5. Na barra de fórmula, digite um sinal de igual = e, em seguida, digite um colchete de abertura [. Você verá uma janela pequena com todas as colunas na tabela FactSales. Este é o IntelliSense em ação.

   Porque as colunas calculadas são sempre criadas na tabela ativa em que você está, não há necessidade de preceder o nome da coluna com o nome da tabela. Continue e role para baixo e, em seguida, clique duas vezes em [SalesQuantity]. Você também pode rolar para o nome de coluna desejado e, em seguida, pressione Tab.

   Seu cursor agora está ativo à direita de [SalesQuantity].

6. Digite um espaço, digite um operador de subtração - (um sinal de subtração) e digite outro espaço.

7. Agora, digite outro colchete de abertura [. Desta vez, selecione a coluna [ReturnQuantity] e pressione Enter.

   Se você receber um erro, observe com atenção a sua sintaxe. Se necessário, compare-a com a fórmula na coluna calculada Margem descrita anteriormente.

   Depois que você pressionar Enter para concluir a fórmula, a palavra Calculando aparecerá na barra de status na parte inferior da janela do Power Pivot. Isso ocorre rapidamente, embora você tenha acabado de calcular novos valores para mais de três milhões de linhas.

8. Clique com o botão direito do mouse no cabeçalho da coluna e renomeie a coluna como NetSales.

Isso é isto! Você há pouco criou um simples contudo fórmula DAX muito avançada. Para cada linha na tabela FactSales, a fórmula de NetSales calcula um valor subtraindo o valor na coluna [ReturnQuantity] do valor na coluna [SalesQuantity]. Observe como falamos “Para cada linha”. Essa é uma noção de outro conceito muito importante no DAX: contexto de linha. Você aprenderá mais sobre contexto de linha posteriormente.

Uma coisa realmente importante para entender ao digitar um operador em uma fórmula DAX é o tipo de dados nos argumentos que você está usando. Por exemplo, se você digitar a seguinte fórmula = 1 & 2, o valor retornado será um valor de texto de "12." Isto ocorre porque o operador de E comercial (&) é para concatenação de texto. O DAX interpreta esta fórmula dessa maneira: calcule um resultado utilizando o valor 1 como texto e adicione o valor 2 como texto. Agora, se você digitar = 1 + 2, o DAX lerá esta fórmula dessa maneira: calcule um resultado utilizando o valor numérico 1 e adicionando o valor numérico 2. O resultado é obviamente "3", um valor numérico. O DAX calcula os valores resultantes dependendo do operador na fórmula, não baseado no tipo de dados de colunas usado no argumento. Os tipos de dados no DAX são muito importantes, mas fora do escopo deste Início Rápido. Para saber mais sobre tipos de dados e operadores em fórmulas DAX, veja a Referência de DAX (http://go.microsoft.com/fwlink/?LinkId=239769&clcid=0x409) nos Manuais Online.

Vamos tentar outro. Desta vez, irá criar uma medida ao escrever a fórmula e ao utilizar o IntelliSense. Não se preocupe muito se você não entender a fórmula completamente. A ideia importante aqui é saber a criar uma fórmula usando vários elementos juntos na sintaxe correta.

Tarefa: Criar uma fórmula de medida

1. Na tabela FactSales, clique em qualquer célula vazia na Área de Cálculo. Esta é a área de células vazias bem abaixo de uma tabela na janela do Power Pivot.

1. Na barra de fórmulas, digite o nome Vendas do Trimestre Anterior.

2. Digite um sinal de igual = para começar a fórmula de cálculo.

3. Digite as primeiras letras CAL e clique duas vezes na função que você deseja usar. Nesta fórmula, você deseja usar a função CALCULATE.

4. Digite um parêntese de abertura ( para começar os argumentos a serem transmitidos à função CALCULATE.

   Observe que, depois de digitar o parêntese de abertura, o IntelliSense mostra os argumentos necessários para a função CALCULATE. Você aprenderá sobre os argumentos em breve.

5. Escreva as primeiras letras da tabela FactSales e, em seguida, na lista pendente, faça duplo clique em FactSales[Vendas].

6. Digite uma vírgula (,) para especificar o primeiro filtro, digite PRE e clique duas vezes na função PREVIOUSQUARTER.

   Depois de selecionar a função PREVIOUSQUARTER, outro parêntese de abertura aparece, indicando que outro argumento é necessário, dessa vez, para a função PREVIOUSQUARTER.

7. Digite as primeiras letras e clique duas vezes em DimDate[DateKey].

8. Feche os dois argumentos que estão sendo passados para a função PREVIOUSQUARTER e a função CALCULATE digitando dois parênteses de fechamento )).

   Sua fórmula agora deve ter esta aparência:

   Previous Quarter Sales:=CALCULATE(FactSales[Sales], PREVIOUSQUARTER(DimDate[DateKey]))

9. Clique no botão de fórmula de verificação na barra de fórmulas para validar a fórmula. Se você obtiver um erro, verifique cada elemento da sintaxe.

Você conseguiu! Acabou de criar uma medida com o DAX e não é fácil. O que esta fórmula fará é calcular as vendas totais durante o trimestre anterior, dependendo dos filtros aplicados em uma Tabela Dinâmica ou Gráfico Dinâmico.

Você acabou de ser apresentado a vários aspectos importantes de fórmulas DAX. Primeiro, esta fórmula incluiu duas funções. Repare que a função PREVIOUSQUARTER está aninhada como um argumento transmitido para a função CALCULATE . As fórmulas DAX podem conter até 64 funções aninhadas. É improvável que uma fórmula alguma vez contivesse tantas funções aninhadas. Na realidade, essa fórmula seria muito difícil de criar e depurar, e provavelmente não seria muito rápida.

Nesta fórmula, você também usou filtros. Os filtros restringem o que será calculado. Neste caso, você selecionou um filtro como um argumento, que é de fato outra função. Você aprenderá mais sobre filtros posteriormente.

Finalmente, você usou a função CALCULATE. Esta é uma das funções mais avançadas no DAX. À medida que você cria modelos de dados e fórmulas mais complexas, provavelmente usará esta função muitas vezes. Discutir a função CALCULATE está fora do escopo deste Início rápido, mas à medida que seu conhecimento de DAX cresce, preste atenção especial a isso.

Tarefa de Crédito Adicional: criar uma fórmula de medida com a funcionalidade Soma Automática

1. Na tabela FactSales, role para a coluna ReturnQuantity e, em seguida, clique no cabeçalho de coluna para selecionar a coluna inteira.

2. No separador Base , no friso , no grupo Cálculos, clique no botão Soma Automática .

Clique na seta para baixo junto a Soma Automática e, em seguida, clique em Média (repare nas outras funções de agregação padrão que também pode utilizar).

Imediatamente, é criada uma nova medida com o nome Average of ReturnQuantity: seguido da fórmula =AVERAGE([ReturnQuantity]).

Não foi fácil? Claro que, nem todas as fórmulas que você criar serão tão simples. Mas, com o recurso AutoSoma, você pode criar fórmulas rápidas e fáceis usando cálculos de agregação padrão.

Isto deve dar a você um entendimento bom da sintaxe usada em fórmulas DAX. Você também foi apresentado a alguns recursos realmente interessantes como IntelliSense e AutoSoma para ajudá-lo a criar fórmulas rápidas, fáceis e precisas. Claro que há muito mais que você pode aprender sobre sintaxe. Um local bom para saber mais é a Referência de DAX ou Manuais Online do SQL.

QuickQuiz de sintaxe

1. O que faz este botão na barra de fórmulas?
   

2. O que sempre envolve um nome de coluna em uma fórmula DAX?

3. Como escreveria uma fórmula para o seguinte:
   na tabela DimProduct, para cada linha na coluna calculada UnitMargin, calcularia um valor ao subtrair valores na coluna UnitCost dos valores na colunaUnitPrice?

As respostas são fornecidas no final deste tópico.

Tarefa: adicione uma função a uma fórmula usando Inserir Função

1. Na tabela FactSales, desloque-se para a coluna mais à direita e, em seguida, no cabeçalho da coluna, clique em Adicionar Coluna.

2. Na barra de fórmulas, digite um sinal de igual, =.

3. Clique no botão Inserir Função . Esta ação abre a caixa de diálogo Inserir Função .

4. Na caixa de diálogo Inserir Função , clique na caixa de listagem Selecionar uma categoria . Por predefinição, Está selecionado Tudo e todas as funções na categoria Todos estão listadas abaixo. São muitas funções e você terá que filtrar as funções para facilitar a localização do tipo de função que você está procurando.

5. Para esta fórmula, você deseja retornar alguns dados que já existem em outra tabela. Para isso, você vai usar uma função na categoria Filtro. Clique na categoria Filtro e, em seguida, em Selecionar uma função, desloque-se para baixo e faça duplo clique na função RELATED. Clique em Ok para fechar a caixa de diálogo Inserir Função .

6. Use o IntelliSense para ajudá-lo a localizar e selecionar a coluna DimChannel[ChannelName].

7. Feche a fórmula e pressione Enter.

8. Depois que você pressionar Enter para concluir a fórmula, a palavra Calculando aparecerá na barra de status na parte inferior da janela do Power Pivot. Agora você criou uma nova coluna na tabela FactSales com informações de canal da tabela DimChannel.

9. Renomeie a coluna Canal.

   Sua fórmula deve ter esta aparência: =RELATED(DimChannel[ChannelName])

Acabou de ser apresentado a outra função muito importante no DAX, a função RELATED . A função RELATED retorna valores de outra tabela. Você pode usar RELATED contanto que haja uma relação entre a tabela em que você está no momento e a tabela que contém os valores que você quer obter. Claro que a função RELATED tem inúmeras possibilidades. Neste caso, agora você pode incluir o canal de vendas para cada venda na tabela FactSales. Agora você pode ocultar a tabela DimChannel da lista de campos da Tabela Dinâmica, facilitando a navegação e a consulta das informações mais importantes de que você realmente precisa. Assim como a função CALCULATE descrita anteriormente, a função RELATED é muito importante e você provavelmente a usará muitas vezes.

Como você pode ver, as funções no DAX podem ajudar a criar fórmulas muito avançadas. Nós realmente só mencionamos as funções básicas. À medida que suas habilidades com o DAX melhorarem, você criará fórmulas usando muitas funções diferentes. Um dos melhores locais para aprender detalhes sobre todas as funções DAX está na Referência da DAX (Data Analysis Expressions).

QuickQuiz de funções

1. O que uma função sempre referencia?

2. Uma fórmula pode conter mais de uma função?

3. Quais categorias de funções você usaria para concatenar duas cadeias de caracteres de texto em uma cadeia de caracteres de texto?

As respostas são fornecidas no final deste tópico.

Contexto de linha

O Contexto de linha é mais facilmente considerado como "a linha atual". Por exemplo, você se lembra da coluna calculada Margem que você viu anteriormente quando aprendeu sobre sintaxe? A fórmula =[SalesAmount] - [TotalCost] calcula um valor na coluna Margem para cada linha na tabela. Os valores para cada linha são calculados de valores em duas outras colunas, [SalesAmount] e [TotalCost] na mesma linha. O DAX pode calcular os valores para cada linha na coluna Margem porque tem o contexto: para cada linha, ele utiliza os valores na coluna [TotalCost] e os subtrai dos valores na coluna [SalesAmount].

Na célula selecionada mostrada abaixo, o valor $49,54 na linha atual foi calculado subtraindo o valor $51,54 na coluna [TotalCost] do valor $101,08 na coluna [SalesAmount].

Contexto de linha apenas não se aplica a colunas calculadas. Contexto de linha também se aplica sempre que uma fórmula tem uma função que aplica filtros para identificar uma única linha em uma tabela. A função aplicará um contexto de linha inerentemente para cada linha da tabela sobre a qual está filtrando. Este tipo de contexto de linha aplica-se mais frequentemente a medidas.

Contexto de filtro

Contexto de Filtro é um pouco mais difícil de entender que contexto de linha. Você pode entender contexto de filtro facilmente como: um ou mais filtros aplicados em um cálculo que determina um resultado ou valor.

O contexto de filtro não existe no lugar do contexto de linha; ele se aplica além do contexto de linha. Por exemplo, para restringir ainda mais os valores para incluir em um cálculo, você pode aplicar um contexto de filtro que não apenas especifica o contexto de linha, mas também especifica apenas um valor específico (filtro) naquele contexto de linha.

O contexto de filtro é visto facilmente em Tabelas Dinâmicas. Por exemplo, quando você adiciona TotalCost à área Valores e, em seguida, adiciona Ano e Região à Linha ou Colunas, você está definindo um contexto de filtro que seleciona um subconjunto de dados com base em um determinado ano e região.

Por que contexto de filtro é tão importante para o DAX? Uma vez que, embora o contexto de filtro possa ser aplicado mais facilmente ao adicionar etiquetas de colunas e linhas e segmentações de dados numa tabela dinâmica, o contexto de filtro também pode ser aplicado numa fórmula DAX ao definir um filtro através de funções como ALL, RELATED, FILTER, CALCULATE, por relações e por outras medidas e colunas. Por exemplo, vamos ver a seguinte fórmula numa medida denominada StoreSales:

Claramente esta fórmula é mais complexa que algumas das outras fórmulas que você viu. Porém, para entender melhor esta fórmula, nós poderemos analisá-la, como faríamos com outras fórmulas.

A fórmula inclui os seguintes elementos de sintaxe:

1. O nome da medida StoreSales, seguido de dois pontos :.

2. O operador do sinal de igual (=) indica o início da fórmula.

3. A função CALCULATE avalia uma expressão, como um argumento, em um contexto que é modificado pelos filtros especificados.

4. Parênteses () envolvem um ou mais argumentos.

5. Uma medida [Vendas] na mesma tabela que uma expressão. A medida Vendas tem a fórmula: =SOMA(FactSales[SalesAmount]).

6. Uma vírgula (,) separa cada filtro.

7. A coluna referenciada e um valor específico, DimChannel[ChannelName] =”Store”, como um filtro.

Esta fórmula irá garantir que apenas os valores de vendas, definidos pela medida Vendas, como um filtro, são calculados apenas para linhas na coluna DimChannel[ChannelName] com o valor "Store", como um filtro.

Como pode imaginar você, poder definir contexto de filtro dentro de uma fórmula é um recurso poderoso e avançado. Poder referenciar somente um valor específico em uma tabela relacionada é apenas um exemplo disso. Não se preocupe se você não entender totalmente contexto de primeira. À medida que você criar suas próprias fórmulas, entenderá melhor contexto e por que é tão importante no DAX.

QuickQuiz de contexto

1. O que são os dois tipos de contexto?

2. O que é contexto de filtro?

3. O que é contexto de linha?

As respostas são fornecidas no final deste tópico.