Excel DAX - Fórmulas
DAX é uma linguagem de fórmula para criar cálculos personalizados nas tabelas dinâmicas do Power. Você pode usar as funções DAX projetadas para trabalhar com dados relacionais e realizar agregação dinâmica em fórmulas DAX.
DAX formulassão muito semelhantes às fórmulas do Excel. Para criar uma fórmula DAX, você digita um sinal de igual, seguido por um nome de função ou expressão e quaisquer valores ou argumentos necessários.
Funções DAX vs. Fórmulas DAX
As fórmulas DAX podem incluir funções DAX e otimizar seu uso. É aqui que as fórmulas DAX tendem a diferir das funções DAX em aspectos importantes.
Uma função DAX sempre faz referência a uma coluna ou tabela completa. Se desejar usar apenas valores específicos de uma tabela ou coluna, você pode adicionar filtros à fórmula.
Se você deseja personalizar os cálculos linha por linha, o Power Pivot fornece funções que permitem usar o valor da linha atual ou um valor relacionado para realizar cálculos que variam de acordo com o contexto.
DAX inclui um tipo de função que retorna uma tabela como resultado, em vez de um único valor. Essas funções podem ser usadas para fornecer entrada para outras funções, calculando assim valores para tabelas ou colunas inteiras.
Algumas funções DAX fornecem inteligência de tempo, o que permite criar cálculos usando intervalos significativos de datas e comparar os resultados em períodos paralelos.
Compreendendo a sintaxe da fórmula DAX
Cada fórmula DAX tem a seguinte sintaxe -
Cada fórmula deve começar com um sinal de igual.
À direita do sinal de igual, você pode digitar ou selecionar um nome de função ou digitar uma expressão. A expressão pode conter nomes de tabelas e nomes de colunas conectados por operadores DAX.
A seguir estão algumas fórmulas DAX válidas -
- [column_Cost] + [column_Tax]
- = Hoje ()
Compreendendo o recurso IntelliSense
O DAX fornece o recurso IntelliSense que permitirá que você escreva fórmulas DAX de maneira rápida e correta. Com este recurso, você não precisa digitar os nomes de tabela, coluna e função completamente, mas selecionar os nomes relevantes na lista suspensa ao escrever uma fórmula DAX.
Comece a digitar as primeiras letras do nome da função. AutoCompletar exibe uma lista de funções disponíveis com os nomes começando com essas letras.
Coloque o ponteiro em qualquer um dos nomes de função. A dica de ferramenta do IntelliSense será exibida fornecendo a você o uso da função.
Clique no nome da função. O nome da função aparece na barra de fórmulas e a sintaxe é exibida, que o guiará enquanto você seleciona os argumentos.
Digite a primeira letra do nome da tabela que você deseja. AutoCompletar exibe uma lista de tabelas e colunas disponíveis com os nomes começando com aquela letra.
Pressione TAB ou clique no nome para adicionar um item da lista de AutoCompletar à fórmula.
Clique no Fxbotão para exibir uma lista de funções disponíveis. Para selecionar uma função da lista suspensa, use as teclas de seta para destacar o item e clique em OK para adicionar a função à fórmula.
Forneça os argumentos para a função selecionando-os em uma lista suspensa de tabelas e colunas possíveis ou digitando os valores necessários.
O uso desse recurso útil do IntelliSense é altamente recomendado.
Onde usar fórmulas DAX?
Você pode usar fórmulas DAX na criação de colunas calculadas e campos calculados.
Você pode usar fórmulas DAX em colunas calculadas, adicionando uma coluna e digitando uma expressão na barra de fórmulas. Você cria essas fórmulas na janela do PowerPivot.
Você pode usar fórmulas DAX em campos calculados. Você cria essas fórmulas -
Na janela do Excel na caixa de diálogo Campo Calculado, ou
Na janela do Power Pivot na área de cálculo de uma tabela.
A mesma fórmula pode se comportar de maneira diferente, dependendo se a fórmula é usada em uma coluna calculada ou em um campo calculado.
Em uma coluna calculada, a fórmula é sempre aplicada a todas as linhas da coluna, em toda a tabela. Dependendo do contexto da linha, o valor pode mudar.
Em um campo calculado, entretanto, o cálculo dos resultados é fortemente dependente do contexto. Ou seja, o design da Tabela Dinâmica e a escolha dos cabeçalhos das linhas e colunas afetam os valores usados nos cálculos.
É importante entender o conceito de contexto no DAX para escrever fórmulas DAX. Isso pode ser um pouco difícil no início de sua jornada DAX, mas assim que você entender isso, poderá escrever fórmulas DAX eficazes que são necessárias para análises de dados complexas e dinâmicas. Para obter detalhes, consulte o capítulo - Contexto DAX.
Criação de uma fórmula DAX
Você já aprendeu sobre o recurso IntelliSense em uma seção anterior. Lembre-se de usá-lo ao criar qualquer fórmula DAX.
Para criar uma fórmula DAX, use as seguintes etapas -
Digite um sinal de igual.
À direita do sinal de igual, digite o seguinte -
Digite a primeira letra de uma função ou nome de tabela e selecione o nome completo na lista suspensa.
Se você escolheu um nome de função, digite parênteses '('.
Se você escolheu o nome da tabela, digite o colchete '['. Digite a primeira letra do nome da coluna e selecione o nome completo na lista suspensa.
Feche os nomes das colunas com ']' e os nomes das funções com ')'.
Digite um operador DAX entre as expressões ou digite ',' para separar os argumentos da função.
Repita as etapas 1 a 5 até que a fórmula DAX seja concluída.
Por exemplo, você deseja encontrar o valor total das vendas na região Leste. Você pode escrever uma fórmula DAX conforme mostrado abaixo. East_Sales é o nome da tabela. A quantidade é uma coluna da tabela.
SUM ([East_Sales[Amount])Conforme já discutido no capítulo - Sintaxe DAX, é uma prática recomendada usar o nome da tabela junto com o nome da coluna em todas as referências a qualquer nome de coluna. Isso é denominado como - “o nome totalmente qualificado”.
A fórmula DAX pode variar dependendo se é para um campo calculado ou coluna calculada. Consulte as seções abaixo para obter detalhes.
Criação de uma fórmula DAX para uma coluna calculada
Você pode criar uma fórmula DAX para uma coluna calculada na janela do Power Pivot.
- Clique na guia da tabela na qual deseja adicionar a coluna calculada.
- Clique na guia Design na Faixa de Opções.
- Clique em Adicionar.
- Digite a fórmula DAX para a coluna calculada na barra de fórmulas.
= DIVIDE (East_Sales[Amount], East_Sales[Units])Esta fórmula DAX faz o seguinte para cada linha na tabela East_Sales -
Divide o valor na coluna Quantidade de uma linha pelo valor na coluna Unidades na mesma linha.
Coloca o resultado na nova coluna adicionada na mesma linha.
Repete as etapas 1 e 2 iterativamente até completar todas as linhas da tabela.
Você adicionou uma coluna para Preço unitário na qual essas unidades são vendidas com a fórmula acima.
Como você pode observar, as colunas calculadas também requerem computação e espaço de armazenamento. Portanto, use colunas calculadas apenas se necessário. Use campos calculados sempre que possível e suficiente.
Consulte o capítulo - Colunas calculadas para obter detalhes.
Criação de uma fórmula DAX para um campo calculado
Você pode criar uma fórmula DAX para um campo calculado na janela do Excel ou na janela do Power Pivot. No caso de campo calculado, você precisa fornecer o nome com antecedência.
Para criar uma fórmula DAX para um campo calculado na janela do Excel, use a caixa de diálogo Campo calculado.
Para criar uma fórmula DAX para um campo calculado na janela do Power Pivot, clique em uma célula na área de cálculo na tabela relevante. Inicie a fórmula DAX com CalculatedFieldName: =.
Por exemplo, Total East Sales Amount: = SUM ([East_Sales [Amount])
Se você usar a caixa de diálogo Campo Calculado na janela do Excel, poderá verificar a fórmula antes de salvá-la e torná-la um hábito obrigatório para garantir o uso de fórmulas corretas.
Para mais detalhes sobre essas opções, consulte o capítulo - Campos Calculados.
Criação de fórmulas DAX usando a barra de fórmulas
A janela do Power Pivot também tem uma barra de fórmulas semelhante à barra de fórmulas da janela do Excel. A barra de fórmulas facilita a criação e edição de fórmulas, usando a funcionalidade AutoCompletar para minimizar erros de sintaxe.
Para inserir o nome de uma tabela, comece digitando o nome da tabela. O AutoCompletar de fórmulas fornece uma lista suspensa contendo nomes de tabelas válidos que começam com essas letras. Você pode começar com uma letra e digitar mais letras para restringir a lista, se necessário.
Para inserir o nome de uma coluna, você pode selecioná-la na lista de nomes de colunas na tabela selecionada. Digite um colchete '[', à direita do nome da tabela e, em seguida, escolha a coluna da lista de colunas na tabela selecionada.
Dicas para usar o AutoCompletar
A seguir estão algumas dicas para usar o AutoCompletar -
Você pode aninhar funções e fórmulas em uma fórmula DAX. Nesse caso, você pode usar o Preenchimento Automático de Fórmula no meio de uma fórmula existente com funções aninhadas. O texto imediatamente antes do ponto de inserção é usado para exibir valores na lista suspensa e todo o texto após o ponto de inserção permanece inalterado.
Os nomes definidos que você cria para constantes não são exibidos na lista suspensa AutoCompletar, mas você ainda pode digitá-los.
O parêntese de fechamento das funções não é adicionado automaticamente. Você precisa fazer isso sozinho.
Você deve se certificar de que cada função está sintaticamente correta.
Compreendendo o recurso da função de inserção
Você pode encontrar o botão Inserir Função rotulado como fx, tanto na janela do Power Pivot quanto na janela do Excel.
O botão Inserir função na janela do Power Pivot está à esquerda da barra de fórmulas.
O botão Inserir Função na janela do Excel está na caixa de diálogo Campo Calculado à direita da Fórmula.
Quando você clica no fxbotão, a caixa de diálogo Inserir função aparece. A caixa de diálogo Inserir função é a maneira mais fácil de encontrar uma função DAX que seja relevante para sua fórmula DAX.
A caixa de diálogo Inserir Função ajuda a selecionar funções por categoria e fornece descrições curtas para cada função.
Usando a função de inserção em uma fórmula DAX
Suponha que você queira criar o seguinte campo calculado -
Medal Count: = COUNTA (]Medal])Você pode usar a caixa de diálogo Inserir Função usando as seguintes etapas -
- Clique na área de cálculo da tabela Resultados.
- Digite o seguinte na barra de fórmulas -
Medal Count: =- Clique no botão Inserir Função (fx)
A caixa de diálogo Inserir Função é exibida.
Selecione Estatística na caixa Selecionar uma categoria, conforme mostrado na captura de tela a seguir.
Selecione CONT.valores na caixa Selecionar uma função, conforme mostrado na imagem a seguir.
Como você pode observar, a sintaxe da função DAX selecionada e a descrição da função são exibidas. Isso permite que você tenha certeza de que é a função que deseja inserir.
Clique OK. Contagem de medalhas: = CONT.valores (aparece na barra de fórmulas e uma dica de ferramenta exibindo a sintaxe da função também aparece.
Tipo [. Isso significa que você está prestes a digitar um nome de coluna. Os nomes de todas as colunas e os campos calculados na tabela atual serão exibidos na lista suspensa. Você pode usar o IntelliSense para completar a fórmula.
Digite M. Os nomes exibidos na lista suspensa serão limitados aos que começam com 'M'.
Clique em medalha.
Clique duas vezes em medalha. Contagem de medalhas: = COUNTA ([Medalha] será exibido na barra de fórmulas. Feche os parênteses.
Pressione Enter. Você terminou. Você também pode usar o mesmo procedimento para criar uma coluna calculada. Você também pode seguir as mesmas etapas para inserir uma função na caixa de diálogo Campo Calculado na janela do Excel usando o recurso Inserir Função.
Clique na função Inserir (fx) à direita da Fórmula.
A caixa de diálogo Inserir Função é exibida. O resto das etapas são as mesmas acima.
Usando múltiplas funções em uma fórmula DAX
As fórmulas DAX podem conter até 64 funções aninhadas. Porém, é improvável que uma fórmula DAX contenha tantas funções aninhadas.
Se uma fórmula DAX tiver muitas funções aninhadas, ela terá as seguintes desvantagens -
- A fórmula seria muito difícil de criar.
- Se a fórmula contiver erros, será muito difícil depurar.
- A avaliação da fórmula não seria muito rápida.
Nesses casos, você pode dividir a fórmula em fórmulas gerenciáveis menores e construir a fórmula grande de forma incremental.
Criação de uma fórmula DAX usando agregações padrão
Ao realizar a análise de dados, você fará cálculos nos dados agregados. Existem várias funções de agregação DAX, como SUM, COUNT, MIN, MAX, DISTINCTCOUNT, etc. que você pode usar em fórmulas DAX.
Você pode criar fórmulas automaticamente usando agregações padrão usando o recurso AutoSoma na janela do Power Pivot.
- Clique na guia Resultados na janela do Power Pivot. A tabela de resultados será exibida.
- Clique na coluna Medalha. A coluna inteira - Medalha será selecionada.
- Clique na guia Página inicial na faixa de opções.
- Clique na seta para baixo ao lado de AutoSoma no grupo Cálculos.
- Clique em COUNT na lista suspensa.
Como você pode observar, o campo calculado Contagem de Medalha aparece na área de cálculo abaixo da coluna - Medalha. A fórmula DAX também aparece na barra de fórmulas -
Count of Medal: = COUNTA([Medal])O recurso AutoSoma fez o trabalho para você - criou o campo calculado para agregação de dados. Além disso, o AutoSoma assumiu a variante apropriada da função DAX COUNT, ou seja, COUNTA (DAX tem funções COUNT, COUNTA, COUNTAX).
Uma palavra de cautela - para usar o recurso AutoSoma, você precisa clicar na seta para baixo ao lado de AutoSoma na faixa de opções. Se você clicar no próprio AutoSum, você obterá -
Sum of Medal: = SUM([Medal])E um erro é sinalizado porque a medalha não é uma coluna de dados numéricos e o texto na coluna não pode ser convertido em números.
Você pode consultar o capítulo - DAX Error Reference para obter detalhes sobre erros DAX.
Fórmulas DAX e o modelo relacional
Como você sabe, no Modelo de Dados do Power Pivot, você pode trabalhar com várias tabelas de dados e conectar as tabelas definindo relacionamentos. Isso permitirá que você crie fórmulas DAX interessantes que usam as correlações das colunas entre as tabelas relacionadas para cálculos.
Ao criar um relacionamento entre duas tabelas, espera-se que você certifique-se de que as duas colunas usadas como chaves tenham valores correspondentes, pelo menos para a maioria das linhas, se não completamente. No Power Pivot Data Model, é possível ter valores não correspondentes em uma coluna-chave e ainda criar um relacionamento, porque o Power Pivot não impõe integridade referencial (consulte a próxima seção para obter detalhes). No entanto, a presença de valores em branco ou não correspondentes em uma coluna-chave pode afetar os resultados das fórmulas DAX e a aparência das tabelas dinâmicas.
Integridade referencial
O estabelecimento da integridade referencial envolve a construção de um conjunto de regras para preservar os relacionamentos definidos entre as tabelas ao inserir ou excluir dados. Se você não garantir isso exclusivamente, já que o Power Pivot não o impõe, você pode não obter resultados corretos com as fórmulas DAX criadas antes que as alterações de dados sejam feitas.
Se você impõe integridade referencial, você pode evitar as seguintes armadilhas -
Adicionar linhas a uma tabela relacionada quando não houver nenhuma linha associada na tabela primária (ou seja, com valores correspondentes nas colunas-chave).
Alterar dados em uma tabela primária que resultaria em linhas órfãs em uma tabela relacionada (ou seja, linhas com um valor de dados na coluna-chave que não tem um valor correspondente na coluna-chave da tabela primária).
Excluindo linhas de uma tabela primária quando houver valores de dados correspondentes nas linhas da tabela relacionada.