Guia de Introdução: Noções Básicas sobre a linguagem DAX em 30 Minutos

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

1. Se ainda não estiver na janela Power Pivot, no Excel, no friso Power Pivot, clique em Power Pivot Janela.

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

3. Desloque-se 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 estruturador de modelos.

   

   O cursor aparece agora na barra de fórmulas. A barra de fórmulas é onde pode escrever uma fórmula para uma coluna calculada ou um campo calculado.

   Vamos analisar 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 cancelar. Clique no mesmo. O cursor já não aparece na barra de fórmulas e o botão cancelar e o botão de marca de verificação já não aparecem. Clique novamente na barra de fórmulas. O botão cancelar e o botão de marca de verificação reaparecem agora. Isto significa que está pronto para começar a introduzir uma fórmula.

   O botão de marca de verificação é o botão verificar fórmula. Não faz muito até introduzir uma fórmula. Voltaremos a isso daqui a pouco.

   Clique no botão Fx . Verá que é apresentada uma nova caixa de diálogo; a caixa de diálogo Inserir Função. A caixa de diálogo Inserir Função é a forma mais fácil de começar a introduzir 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. Feche a caixa de diálogo Inserir Função.

5. Na barra de fórmulas, escreva um sinal de igual =e, em seguida, escreva um parêntese reto de abertura [. Verá que é apresentada uma pequena janela com todas as colunas na tabela FactSales. Este é o IntelliSense em ação.

   Uma vez que as colunas calculadas são sempre criadas na tabela ativa em que se encontra, não é necessário preceder o nome da coluna com o nome da tabela. Vá em frente e desloque-se para baixo e, em seguida, faça duplo clique em [QuantidadeDeVendas]. Também pode deslocar para o nome da coluna que pretende e, em seguida, premir a Tecla de Tabulação.

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

6. Escreva um espaço e, em seguida, escreva um operador de subtração - (um sinal de subtração) e, em seguida, escreva outro espaço.

7. Agora, escreva outro parêntese reto de abertura [. Desta vez, selecione a coluna [ReturnQuantity] e, em seguida, prima Enter.

   Se receber um erro, observe cuidadosamente a sintaxe. Se necessário, compare-a com a fórmula na coluna calculada Margem descrita anteriormente.

   Depois de premir Enter para concluir a fórmula, a palavra Calcular aparece na barra de estado ao longo da parte inferior da janela Power Pivot. Vai rapidamente, apesar de calcular novos valores para mais de três milhões de linhas.

8. Clique com o botão direito do rato no cabeçalho da coluna e mude o nome da coluna NetSales.

Já está! Acabou de criar uma fórmula DAX simples, mas muito poderosa. Para cada linha na tabela FactSales, a fórmula NetSales calcula um valor ao subtrair o valor na coluna [ReturnQuantity] do valor na coluna [SalesQuantity]. Repare como acabamos de dizer "Para cada linha". Este é um vislumbre de outro conceito muito importante no DAX; contexto de linha. Irá saber mais sobre o contexto de linha mais tarde.

Algo realmente importante a compreender ao escrever um operador numa fórmula DAX é o tipo de dados nos argumentos que está a utilizar. Por exemplo, se escrevesse a seguinte fórmula, = 1 & 2, o valor devolvido seria um valor de texto de "12". Isto deve-se ao facto de o operador de e comercial (&) se destinar à concatenação de texto. O DAX interpreta esta fórmula para ler: Calcular um resultado ao utilizar o valor 1 como texto e adicionar o valor 2 como texto. Agora, se escrever = 1 + 2, o DAX lê esta fórmula como: Calcular um resultado ao utilizar o valor numérico 1 e ao adicionar o valor numérico 2. O resultado é, naturalmente, "3", um valor numérico. O DAX calcula os valores resultantes consoante o operador na fórmula, não com base no tipo de dados das colunas utilizadas no argumento. Os tipos de dados no DAX são muito importantes, mas fora do âmbito deste Guia de Introdução. Para saber mais sobre tipos de dados e operadores em fórmulas DAX, consulte Referência da DAX (http://go.microsoft.com/fwlink/?LinkId=239769&clcid=0x409) no Books Online.

Vamos tentar outra. Desta vez, irá criar uma medida ao escrever a fórmula e ao utilizar o IntelliSense. Não se preocupe muito se não compreender totalmente a fórmula. O importante aqui é aprender a criar uma fórmula com vários elementos em conjunto 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 imediatamente abaixo de uma tabela na janela Power Pivot.

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

2. Escreva um sinal de igual = para iniciar a fórmula de cálculo.

3. Escreva as primeiras letras CAL e, em seguida, faça duplo clique na função que pretende utilizar. Nesta fórmula, quer utilizar a função CALCULATE .

4. Escreva um parêntese de abertura ( para iniciar os argumentos a serem transmitidos para a função CALCULATE.

   Repare que depois de escrever os parênteses de abertura, o IntelliSense mostra-lhe os argumentos necessários para a função CALCULATE. Irá aprender um pouco sobre os argumentos.

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

6. Escreva uma vírgula (,) para especificar o primeiro filtro e, em seguida, escreva PRE e, em seguida, faça duplo clique na função PREVIOUSQUARTER .

   Depois de selecionar a função PREVIOUSQUARTER, é apresentado outro parêntese de abertura, que indica que é necessário outro argumento; desta vez, para a função PREVIOUSQUARTER.

7. Escreva as primeiras letras Dim e, em seguida, faça duplo clique em DimDate[DateKey].

8. Feche o argumento que está a ser transmitido para a função PREVIOUSQUARTER e a função CALCULATE ao escrever dois parênteses de fecho )).

   A sua fórmula deverá ter o seguinte aspeto:

   Vendas do Trimestre Anterior:=CALCULATE(FactSales[Vendas], PREVIOUSQUARTER(DimDate[DateKey]))

9. Clique no botão verificar fórmula na barra de fórmulas para validar a fórmula. Se receber um erro, verifique cada elemento da sintaxe.

Foste tu! Acabou de criar uma medida com o DAX e não é fácil. O que esta fórmula irá fazer é calcular o total de vendas do trimestre anterior, consoante os filtros aplicados numa Tabela Dinâmica ou gráfico dinâmico.

Acabou de ser apresentado a vários aspetos importantes das fórmulas DAX. Primeiro, esta fórmula incluía 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 contenha tantas funções aninhadas. Na verdade, tal fórmula seria muito difícil de criar e depurar, e provavelmente também não seria muito rápida.

Nesta fórmula, também utilizou filtros. Os filtros restringem o que será calculado. Neste caso, selecionou um filtro como argumento, que na verdade é outra função. Irá saber mais sobre os filtros mais tarde.

Por fim, utilizou a função CALCULATE. Esta é uma das funções mais poderosas do DAX. À medida que cria modelos de dados e cria fórmulas mais complexas, provavelmente utilizará esta função muitas vezes. Discutir a função CALCULATE está fora do âmbito deste Início Rápido, mas à medida que o seu conhecimento sobre o DAX aumenta, preste especial atenção a este.

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

1. Na tabela FactSales, desloque-se para a coluna ReturnQuantity e, em seguida, clique no cabeçalho da coluna para selecionar toda a coluna.

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 assim tão fácil? Claro que nem todas as fórmulas que criar serão tão simples. No entanto, ao utilizar a funcionalidade Soma Automática, pode criar fórmulas rápidas e fáceis através de cálculos de agregação padrão.

Isto deve dar-lhe uma boa compreensão da sintaxe utilizada nas fórmulas DAX. Também lhe foram apresentadas algumas funcionalidades muito interessantes, como o IntelliSense e o AutoSum, para o ajudar a criar fórmulas rápidas, fáceis e precisas. Claro que há muito mais que pode aprender sobre sintaxe. Um bom local para saber mais é a Referência da DAX ou sqL Books Online.

Sintaxe QuickQuiz

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

2. O que rodeia sempre um nome de coluna numa 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: Adicionar uma função a uma fórmula com a Função Insert

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, escreva 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, pelo que vai querer filtrar as funções para facilitar a localização do tipo de função que procura.

5. Para esta fórmula, quer devolver alguns dados que já existem noutra tabela. Para tal, vai utilizar 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. Utilize o IntelliSense para o ajudar a encontrar e selecionar a coluna DimChannel[ChannelName].

7. Feche a fórmula e, em seguida, prima Enter.

8. Depois de premir Enter para concluir a fórmula, a palavra Calcular aparece na barra de estado ao longo da parte inferior da janela Power Pivot. Agora verá que acabou de criar uma nova coluna na tabela FactSales com informações de canal da tabela DimChannel.

9. Mude o nome da coluna Canal.

   A sua fórmula deverá ter o seguinte aspeto: =RELATED(DimChannel[ChannelName])

Acabou de ser apresentado a outra função muito importante no DAX, a função RELATED . A função RELATED devolve valores de outra tabela. Pode utilizar RELATED desde que exista uma relação entre a tabela em que se encontra e a tabela que contém os valores que pretende obter. Claro que a função RELATED tem imensas possibilidades. Neste caso, agora pode incluir o canal de vendas para cada venda na tabela FactSales. Agora, pode ocultar a tabela DimChannel da lista de campos da Tabela Dinâmica, tornando mais fácil navegar e ver apenas as informações mais importantes de que realmente precisa. Tal como a função CALCULATE descrita anteriormente, a função RELATED é muito importante e provavelmente irá utilizá-la muitas vezes.

Como pode ver, as funções no DAX podem ajudá-lo a criar fórmulas muito poderosas. Na verdade, só abordámos as noções básicas das funções. À medida que as suas competências da DAX melhoram, irá criar fórmulas com várias 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 das Funções

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

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

3. Que categoria de funções utilizaria para concatenar duas cadeias de texto numa cadeia?

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, lembre-se da coluna calculada Margem que viu anteriormente quando aprendeu sobre sintaxe? A fórmula =[SalesAmount] - [TotalCost] calcula um valor na coluna Margem para cada linha na tabela. Os valores de cada linha são calculados a partir de valores em duas outras colunas, [SalesAmount] e [TotalCost] na mesma linha. O DAX pode calcular os valores de cada linha na coluna Margem porque tem o contexto: Para cada linha, utiliza valores na coluna [TotalCost] e subtrai-os dos valores na coluna [SalesAmount].

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

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

Contexto de Filtro

O contexto de filtro é um pouco mais difícil de compreender do que o contexto de linha. Pode pensar mais facilmente no contexto de filtro como: Um ou mais filtros aplicados num cálculo que determina um resultado ou valor.

O contexto de filtro não existe em vez do contexto de linha; em vez disso, aplica-se para além do contexto de linha. Por exemplo, para restringir ainda mais os valores a incluir num cálculo, pode aplicar um contexto de filtro que não só especifica o contexto de linha, como também especifica apenas um determinado valor (filtro) nesse contexto de linha.

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

Porque é que o 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:

É evidente que esta fórmula é mais complexa do que algumas das outras fórmulas que viu. No entanto, para compreender melhor esta fórmula, podemos decompo-la, tal como fizemos com outras fórmulas.

Esta fórmula inclui os seguintes elementos de sintaxe:

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

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

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

4. Os parênteses () rodeiam 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, ser capaz de definir o contexto de filtro numa fórmula tem uma capacidade imensa e poderosa. Ser capaz de referenciar apenas um determinado valor numa tabela relacionada é apenas um desses exemplos. Não se preocupe se não compreender completamente o contexto imediatamente. À medida que cria as suas próprias fórmulas, compreenderá melhor o contexto e por que motivo é tão importante no DAX.

QuickQuiz de Contexto

1. Quais são os dois tipos de contexto?

2. O que é o contexto de filtro?

3. O que é o contexto de linha?

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