Data Analysis Expressions (DAX) no Power Pivot

Compreendendo as fórmulas DAX

Fórmulas DAX são muito semelhantes a fórmulas do Excel. Para criar uma, você digita um sinal de igual, seguido de um nome de função ou uma expressão, e todos os valores ou argumentos necessários. Assim como o Excel, a DAX fornece diversas funções que você pode usar para trabalhar com cadeias de caracteres, executar cálculos usando datas e horas, ou criar valores condicionais.

No entanto, as fórmulas DAX são diferentes no que diz respeito a estes itens importantes:

Para personalizar os cálculos linha por linha, o DAX inclui funções que permitem usar o valor da linha atual ou um valor relacionado para executar cálculos que variam de acordo com o contexto.
A 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.
Funções de Inteligência de Tempo no DAX permitem cálculos usando intervalos de datas e comparam os resultados entre períodos paralelos.

Onde usar fórmulas DAX

Você pode criar fórmulas no Power Pivot, em Colunas Calculadas ou em Campos Calculados.

Colunas calculadas

Coluna calculada é uma coluna adicionada a uma tabela do Power Pivot existente. Em vez de colar ou importar valores na coluna, você cria uma fórmula DAX que define os valores da coluna. Se incluir a tabela do Power Pivot em uma Tabela Dinâmica (ou Gráfico Dinâmico), a coluna calculada poderá ser usada como você faria com qualquer outra coluna de dados.

As fórmulas nas colunas calculadas são bem semelhantes às fórmulas criadas no Excel. No entanto, diferentemente do Excel, não é possível criar uma fórmula diferente para linhas distintas em uma tabela; em vez disso, a fórmula DAX é aplicada automaticamente a toda a coluna.

Quando uma coluna contém uma fórmula, o valor é computado para cada linha. Os resultados são calculados para a coluna assim que você cria a fórmula. Apenas os valores da coluna serão recalculados se os dados subjacentes forem atualizados ou se o recálculo manual for usado.

Você pode criar colunas calculadas baseadas em medidas e outras colunas calculadas. No entanto, evite usar o mesmo nome para uma coluna calculada e uma medida, pois isso pode levar a resultados confusos. Ao se referir a uma coluna, é melhor usar uma referência de coluna totalmente qualificada, para evitar invocar acidentalmente uma medida.

Para saber mais detalhes, consulte Colunas Calculadas no Power Pivot.

Medidas

Uma medida é uma fórmula criada especificamente para uso em uma tabela dinâmica (ou Gráfico Dinâmico) que usa Power Pivot dados. As medidas podem ser baseadas em funções de agregação padrão, como COUNT ou SUM, ou você pode definir sua própria fórmula usando o DAX. Uma medida é usada na área Valores de uma Tabela Dinâmica. Se você quiser colocar resultados calculados em uma área diferente de uma Tabela Dinâmica, use uma coluna calculada em seu lugar.

Quando você define uma fórmula para uma medida explícita, nada acontece até adicionar a medida a uma tabela dinâmica. Quando você adiciona a medida, a fórmula é avaliada para cada célula na área Valores da Tabela Dinâmica. Como um resultado é criado para cada combinação de headers de linha e coluna, o resultado da medida pode ser diferente em cada célula.

A definição da medida que você criar é salva com sua tabela de dados de origem. Ela é exibida na lista de Campos da Tabela Dinâmica e está disponível para todos os usuários da pasta de trabalho.

Para obter informações mais detalhadas,consulte Measures in Power Pivot .

Criando fórmulas com a barra de fórmulas

O Power Pivot, assim como o Excel, fornece uma barra de fórmulas para facilitar a criação e edição de fórmulas, além da funcionalidade de Preenchimento Automático, para minimizar os erros de digitação e sintaxe.

Para digitar o nome de uma tabela Comece a digitar o nome da tabela. AutoCompletar Fórmula fornece uma lista suspensa que contém nomes válidos iniciados com essas letras.

Para digitar o nome de uma coluna Digite um colchete e escolha a coluna na lista de colunas da tabela atual. Para uma coluna de outra tabela, comece digitando as primeiras letras do nome da tabela e, em seguida, escolha a coluna na lista suspensa Preenchimento Automático.

Para saber mais detalhes e instruções sobre como criar fórmulas, consulte Criar fórmulas para cálculos no Power Pivot.

Dicas para usar o Preenchimento Automático

É possível usar a opção Preenchimento Automático 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 depois do ponto de inserção permanece inalterado.

Os nomes definidos que você cria para as constantes não são exibidos na lista suspensa Preenchimento Automático, mas você pode digitá-los.

O Power Pivot não adiciona parênteses de fechamento das funções, nem compara parênteses automaticamente. Você deve verificar se cada função está sintaticamente correta; caso contrário, não poderá salvar nem usar a fórmula.

Usando várias funções em uma fórmula

Você pode aninhar funções, o que significa que você usa os resultados de uma função como um argumento de outra função. Você pode aninhar até 64 níveis de funções em colunas calculadas. Entretanto, o aninhamento pode dificultar a criação ou a solução de problemas em fórmulas.

Muitas funções do DAX destinam-se ao uso somente como funções aninhadas. Essas funções retornam uma tabela, que não pode ser salva diretamente como um resultado; mas deve ser fornecida como entrada para uma função de tabela. Por exemplo, as funções SUMX, AVERAGEX e MINX requerem uma tabela como o primeiro argumento.

Observação: Alguns limites de aninhamento de funções existem em medidas, para garantir que o desempenho não seja afetado pelos muitos cálculos exigidos pelas dependências entre colunas.

Comparando funções de DAX e funções do Excel

A biblioteca de funções de DAX se baseia na biblioteca de funções do Excel, mas elas são muito diferentes. Esta seção resume as diferenças e semelhanças entre funções do Excel e de DAX.

Muitas funções de DAX têm o mesmo nome e o mesmo comportamento geral que as funções do Excel, mas foram modificadas para aceitar diferentes tipos de entradas e, em alguns casos, podem retornar um tipo de dados diferente. Em geral, não é possível usar funções DAX em uma fórmula do Excel, ou usar fórmulas do Excel no Power Pivot sem alguma modificação.
As funções de DAX nunca aceitam uma referência de células ou um intervalo como referência, mas aceitam uma coluna ou tabela como referência.
As funções de data e hora de DAX retornam um tipo de dados datetime. Por outro lado, as funções de data e hora do Excel retornam um inteiro que representa uma data como um número de série.
Muitas das novas funções de DAX retornam uma tabela de valores ou fazem cálculos com base em uma tabela de valores como entrada. Por outro lado, o Excel não tem funções que retornem uma tabela, mas algumas delas funcionam com matrizes. A capacidade de referenciar facilmente tabelas e colunas completas é um novo recurso do Power Pivot.
A DAX oferece novas funções de pesquisa, semelhantes às funções de pesquisa de matriz e vetor do Excel. Porém, as funções de DAX requerem que uma relação seja estabelecida entre as tabelas.
Espera-se que o tipo de dados de uma coluna seja sempre o mesmo. Se os dados não forem do mesmo tipo, o DAX alterará a coluna inteira para o tipo de dados que melhor acomode todos os valores.

Tipos de dados DAX

É possível importar para um modelo de dados do Power Pivot de diversas fontes de dados diferentes que podem dar suporte a diferentes tipos de dados. Quando você importa ou carrega os dados em uma pasta de trabalho, e depois usa esses dados em cálculos ou em Tabelas Dinâmicas, eles são convertidos em um dos tipos de dados do Power Pivot. Para obter uma lista de tipos de dados, consulte Tipos de dados em Modelos de Dados.

O tipo de dados da tabela é um novo tipo de dados do DAX usado como a entrada ou a saída para muitas funções novas. Por exemplo, a função FILTER usa uma tabela como entrada e gera outra tabela que contém apenas as linhas que atendam às condições do filtro. Ao combinar funções de tabela com funções de agregação, você pode executar cálculos complexos em conjuntos de dados definidos de forma dinâmica. Para obter mais informações, consulte Agregações no Power Pivot.

As fórmulas e o modelo relacional

A janela do Power Pivot é uma área na qual é possível trabalhar com várias tabelas de dados e conectá-las em um modelo relacional. Nesse modelo de dados, as tabelas são conectadas umas às outras por relações, o que permite criar correlações com as colunas de outras tabelas e criar cálculos mais relevantes. Por exemplo, você pode criar fórmulas que somam valores para uma tabela relacionada e salvar esse valor em uma única célula. Ou então, para controlar as linhas da tabela relacionada, pode aplicar filtros às tabelas e às colunas. Para saber mais, consulte Relações entre tabelas em um Modelo de Dados.

Como é possível vincular tabelas usando relações, as Tabelas Dinâmicas também podem incluir dados de várias colunas de tabelas diferentes.

No entanto, como as fórmulas podem funcionar com tabelas e colunas inteiras, é necessário criar cálculos de forma diferente do que é feito no Excel.

Em geral, uma fórmula DAX em uma coluna é sempre aplicada ao conjunto inteiro de valores da coluna (nunca a apenas algumas linhas ou células).
As tabelas do Power Pivot sempre devem ter o mesmo número de colunas em cada linha, e todas as linhas de uma coluna devem conter o mesmo tipo de dados.
Quando as tabelas estão conectadas por uma relação, você deve verificar se essas duas colunas usadas como chaves têm valores correspondentes na maior parte. Como o Power Pivot não impõe integridade referencial, é possível ter valores não correspondentes em uma coluna de chave e, ainda assim, criar uma relação. No entanto, a presença de valores em branco ou não correspondentes pode afetar os resultados das fórmulas e a aparência das Tabelas Dinâmicas. Para obter mais informações, consulte Pesquisas em fórmulas do Power Pivot.
Ao vincular tabelas usando relações, você amplia o escopo ou o contexto no qual as fórmulas são avaliadas. Por exemplo, as fórmulas de uma Tabela Dinâmica podem ser afetadas por quaisquer filtros ou títulos de colunas e linhas. É possível gravar fórmulas que manipulam o contexto, mas o contexto também pode fazer com que os resultados sejam alterados de maneiras imprevisíveis. Para saber mais, consulte Contexto em fórmulas DAX.

Atualizando os resultados de fórmulas

A atualização de dados e o recálculo são duas operações separadas, porém relacionadas, que você deve compreender ao criar um modelo de dados que contenha fórmulas complexas, grandes volumes de dados ou dados obtidos de fontes de dados externas.

Atualização de dados é o processo de atualizar os dados da pasta de trabalho com novos dados de uma fonte de dados externa. Você pode atualizar os dados manualmente a intervalos que você especifica. Ou, se você publicou a pasta de trabalho em um site do SharePoint, poderá agendar uma atualização automática de fontes externas.

Recálculo é o processo de atualizar os resultados das fórmulas para refletir todas as alterações feitas nas próprias fórmulas e nos dados subjacentes. O recálculo pode afetar o desempenho das seguintes formas:

Para uma coluna calculada, o resultado da fórmula sempre deve ser recalculado para a coluna inteira, todas as vezes que você alterar a fórmula.
Para uma medida, os resultados de uma fórmula não são calculados até que a medida seja colocada no contexto da tabela dinâmica ou Gráfico Dinâmico. A fórmula também será recalculada quando você alterar qualquer título de linha ou coluna que afete os filtros nos dados, ou quando você atualizar manualmente a Tabela Dinâmica.

Solução de problemas de fórmulas

Erros ao gravar fórmulas

Se você receber um erro ao definir uma fórmula, talvez a fórmula contenha um erro sintático, um erro semântico ou um erro de cálculo.

Os erros sintáticos são os mais fáceis de resolver. Eles normalmente envolvem um parêntese ou vírgula ausente. Para obter ajuda com a sintaxe de funções individuais, consulte a Referência da Função DAX.

Os outros tipos de erros ocorrem quando a sintaxe está correta, mas o valor ou a coluna referenciada não faz sentido no contexto da fórmula. Esses erros semânticos e de cálculo podem ser causados por qualquer um dos seguintes problemas:

A fórmula se refere a uma coluna, tabela ou função não existente.
A fórmula parece estar correta, mas quando o mecanismo de dados busca os dados, ele encontra uma incompatibilidade de tipos e gera um erro.
A fórmula passa um número ou tipo de parâmetros incorreto a uma função.
A fórmula referencia uma coluna diferente que tem o erro e, por isso, os valores são inválidos.
A fórmula se refere a uma coluna que não foi processada, o que significa que ela tem metadados, mas não há dados reais a serem usados para os cálculos.

Nos quatro primeiros casos, o DAX sinaliza a coluna inteira que contém a fórmula inválida. No último caso, o DAX torna a coluna indisponível para indicar que ela está em um estado não processado.

Resultados incorretos ou incomuns na classificação ou ordenação de valores da coluna

Ao classificar ou ordenar uma coluna que contenha o valor NaN (Não é um Número), você pode obter resultados errados ou inesperados. Por exemplo, quando um cálculo divide 0 por 0, um resultado NaN é retornado.

Isso ocorre porque o mecanismo de fórmula efetua a ordenação e a classificação comparando os valores numéricos; no entanto, NaN não pode ser comparado a outros números na coluna.

Para garantir resultados corretos, você pode usar instruções condicionais usando a função SE para testar se há valores NaN e retornar um valor numérico 0.

Compatibilidade com modelos de tabela do Analysis Services e modo DirectQuery

Em geral, as fórmulas do DAX que você cria no Power Pivot são completamente compatíveis com os modelos tabulares do Analysis Services. Porém, se você migrar seu modelo do Power Pivot para uma instância do Analysis Services e implantar o modelo em modo Consulta DirectQuery, há algumas limitações.

Algumas fórmulas DAX poderão retornar resultados diferentes se você implantar o modelo no modo DirectQuery.
Algumas fórmulas podem causar erros de validação quando você implanta o modelo para o modo DirectQuery, pois a fórmula contém uma função DAX sem suporte de uma fonte de dados relacional.

Para obter mais informações, consulte a documentação de modelos tabulares do Analysis Services nos Manuais Online do SQL Server 2012.