DAX (Data Analysis Expressions) no PowerPivot

Noções sobre Fórmulas do DAX

As fórmulas do DAX são muito semelhantes às fórmulas do Excel. Para criar uma, escreva sinal de igual, seguido do nome de uma função ou expressão e quaisquer valores ou argumentos necessários. Tal como o Excel, DAX fornece uma série de funções que podem ser utilizadas para trabalhar com cadeias, efetuar cálculos utilizando datas e horas ou criar valores condicionais.

No entanto, as fórmulas do DAX diferem nos seguintes importantes aspetos:

Se quiser personalizar os cálculos numa base linha a linha, o DAX inclui funções que permitem utilizar o valor da linha atual, ou um valor relacionado, para efetuar cálculos que variem em função do contexto.
DAX inclui um tipo de função que devolve uma tabela como respetivo resultado, em vez de um valor único. Estas funções podem ser utilizadas para fornecer entrada para outras funções.
Funções de Informações de Tempo No DAX, permita cálculos utilizando intervalos de datas e compare os resultados em períodos paralelos.

Onde Utilizar Fórmulas DAX

Pode criar fórmulas no Power Pivot, quer em colunas calculadas ou em campos calculados.

Colunas Calculadas

Uma coluna calculada é uma coluna que adiciona a uma tabela do Power Pivot existente. Em vez de colar ou importar valores na coluna, crie uma fórmula DAX que defina os valores da coluna. Se incluir a tabela do Power Pivot numa Tabela Dinâmica (ou num Gráfico Dinâmico), a coluna calculada poderá ser utilizada como qualquer outra coluna de dados.

As fórmulas existentes em colunas calculadas são muito semelhantes às fórmulas criadas no Excel. No entanto, contrariamente ao que acontece no Excel, não é possível criar uma fórmula diferente para linhas diferentes de uma tabela; em vez disso, a fórmula do DAX é aplicada automaticamente a toda a coluna.

Quando uma coluna contém uma fórmula, o valor é calculado para cada linha. Os resultados são calculados para a coluna assim que criar a fórmula. Os valores das colunas só são recalculados se os dados subjacentes forem atualizados ou se for utilizado o recálculo manual.

Pode criar colunas calculadas baseadas em medidas e noutras colunas calculadas. No entanto, evite utilizar o mesmo nome para uma coluna calculada e para uma medida, pois tal pode provocar resultados confusos. Quando referenciar uma coluna, é melhor utilizar uma referência de coluna completamente qualificada, para evitar invocar acidentalmente uma medida.

Para obter informações mais detalhadas, consulte o artigo Colunas Calculadas no Power Pivot.

Medidas

Uma medida é uma fórmula criada especificamente para utilização numa tabela dinâmica (ou gráfico dinâmico) que utiliza Power Pivot dados. As medidas podem ser baseadas em funções de agregação padrão, como CONTAR ou SOMA ou pode definir a sua própria fórmula com DAX. Uma medida é utilizada na área Valores de uma Tabela Dinâmica. Se pretender colocar os resultados calculados numa área diferente de uma Tabela Dinâmica, deverá utilizar uma coluna calculada.

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

A definição da medida que criar é guardada com a tabela de dados de origem. Esta aparece na lista de Campos de Tabela Dinâmica e está disponível para todos os utilizadores do livro.

Para obter informações mais detalhadas, consulte Medidas no Power Pivot.

Criar Fórmulas Utilizando a Barra de Fórmulas

O Power Pivot, tal como o Excel, fornece uma barra de fórmulas para facilitar a criação e edição de fórmulas, bem como a funcionalidade Conclusão Automática, para minimizar os erros de introdução e sintaxe.

Para introduzir o nome de uma tabela Comece a escrever o nome da tabela. A Conclusão Automática de fórmulas oferece uma lista pendente que contém nomes válidos que começam por essas letras.

Para introduzir o nome de uma coluna Escreva o parêntese e, em seguida, escolha a coluna na lista de colunas da tabela atual. Relativamente a uma coluna de outra tabela, comece a escrever as primeiras letras do nome da tabela e, em seguida, escolha a coluna na lista pendente Conclusão Automática.

Para obter mais detalhes e instruções sobre como criar fórmulas, consulte o artigo Criar Fórmulas para Cálculos no Power Pivot.

Sugestões para Utilização da Conclusão Automática

É possível utilizar a Conclusão Automática de Fórmulas no meio de uma fórmula existente com funções aninhadas. O texto existente imediatamente antes do ponto de inserção é utilizado para apresentar valores na lista pendente; o texto existente após o ponto de inserção permanece inalterado.

Os nomes definidos criados para constantes não são apresentados na lista pendente de Conclusão Automática, mas podem ser introduzidos mesmo assim.

O Power Pivot não adiciona os parênteses de fecho das funções, nem corresponde automaticamente os parênteses. Deve garantir que a sintaxe de cada função está correta; caso contrário, não é possível guardar nem utilizar a fórmula.

Utilizar Várias Funções numa Fórmula

É possível aninhar funções, ou seja, é possível utilizar o resultado de uma função como argumento de outra função. É possível aninhar até 64 níveis de funções em colunas calculadas. No entanto, o aninhamento pode dificultar a criação ou resolução de problemas de fórmulas.

Muitas funções do DAX foram concebidas para serem utilizadas apenas como funções aninhadas. Estas funções devolvem uma tabela que não pode ser guardada diretamente como resultado no livro, mas que deve ser fornecida como entrada para uma função de tabela. Por exemplo, as funções SUMX, AVERAGEX e MINX necessitam de uma tabela como primeiro argumento.

Nota: Existem alguns limites para o aninhamento de funções dentro de medidas, para garantir que o desempenho não é afetado pelos vários cálculos necessários às dependências entre colunas.

Comparação entre as Funções do DAX e as Funções do Excel

A biblioteca de funções do DAX é baseada na biblioteca de funções do Excel, no entanto, as bibliotecas têm muitas diferenças. Esta secção resume as diferenças e as semelhanças entre as funções do Excel e as funções do DAX.

Muitas funções DAX têm o mesmo nome e o mesmo comportamento geral que as funções do Excel, mas foram modificadas para aceitarem tipos de entradas diferentes e, em alguns casos, podem devolver um tipo de dados diferentes. De uma maneira geral, não é possível utilizar funções DAX numa fórmula do Excel, nem utilizar fórmulas do Excel no Power Pivot sem algumas modificações.
As funções do DAX nunca utilizam uma referência ou um intervalo de células como referência; em vez disso, as funções do DAX utilizam uma coluna ou uma tabela como referência.
As funções do DAX de data e hora devolvem um tipo de dados datetime. Por seu lado, as funções de data e hora do Excel devolvem um número inteiro que representa uma data como um número de série.
Muitas das novas funções DAX devolvem uma tabela de valores ou efetuam cálculos com base numa tabela de valores como entrada. Por seu lado, o Excel não tem funções que devolvam uma tabela, mas algumas funções podem trabalhar com matrizes. A capacidade de referenciar facilmente tabelas e colunas é uma funcionalidade nova do Power Pivot.
O DAX fornece novas funções de pesquisa que são semelhantes às funções de pesquisa de matriz e vetor do Excel. No entanto, as funções do DAX exigem que seja estabelecida uma relação entre as tabelas.
É esperado que os dados de uma coluna sejam sempre do mesmo tipo de dados. Se os dados não forem do mesmo tipo, o DAX altera toda a coluna para o tipo de dados que melhor acomoda todos os valores.

Tipos de Dados do DAX

Pode importar dados para um modelo de dados do Power Pivot a partir de várias origens de dados diferentes, que podem suportar tipos de dados diferentes. Quando importa ou carrega os dados e, em seguida, utiliza esses dados em cálculos ou em Tabelas Dinâmicas, os dados são convertidos num dos tipos de dados do Power Pivot. Para obter uma lista dos tipos de dados, consulte Tipos de dados em Modelos de Dados.

O tipo de dados da tabela é um tipo de dados novo do DAX que é utilizado como entrada ou saída de muitas funções novas. Por exemplo, a função FILTER utiliza uma tabela como entrada e produz como saída outra tabela que contém apenas as linhas que satisfazem as condições do filtro. Através da combinação de funções de tabela com funções de agregação, é possível efetuar cálculos complexos em conjuntos de dados definidos dinamicamente. Para mais informações, consulte Agregações no Power Pivot.

Fórmulas e o Modelo Relacional

A janela do Power Pivot é uma área em que pode trabalhar com múltiplas tabelas de dados e ligar as tabelas num modelo relacional. Neste modelo de dados, as tabelas estão interligadas por relações, o que lhe permite criar correlações com colunas noutras tabelas e criar cálculos mais interessantes. Por exemplo, pode criar fórmulas que somam valores numa tabela relacionada e guardam esse valor numa só célula. Em alternativa, para controlar as linhas da tabela relacionada, pode aplicar filtros a tabelas e colunas. Para obter mais informações, consulte o artigo Relações entre tabelas num Modelo de Dados.

Visto que é possível ligar tabelas utilizando relações, as Tabelas Dinâmicas podem igualmente incluir dados de várias colunas de tabelas diferentes.

No entanto, como as fórmulas podem trabalhar com colunas e tabelas inteiras, tem de estruturar os cálculos de um modo diferente do Excel.

Em geral, uma fórmula do DAX numa coluna é sempre aplicada ao conjunto completo de valores existentes na coluna (nunca apenas a algumas linhas ou células).
As tabelas no Power Pivot devem ter sempre 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 ligadas por uma relação, espera-se que se certifique de que as duas colunas utilizadas como chaves têm valores maioritariamente correspondentes. Visto que o Power Pivot não impõe a integridade referencial, é possível ter valores não correspondentes numa coluna de chave e, mesmo assim, conseguir criar uma relação. No entanto, a presença de valores em branco ou não correspondentes poderá afetar os resultados das fórmulas e o aspeto das Tabelas Dinâmicas. Para mais informações, consulte Pesquisas em Fórmulas do Power Pivot.
Quando liga tabelas no livro através de relações, aumenta o âmbito ou o contexto em que as suas fórmulas são avaliadas. Por exemplo, as fórmulas existentes numa Tabela Dinâmica podem ser afetadas por quaisquer filtros ou cabeçalhos de coluna e de linha existentes na Tabela Dinâmica. Pode escrever fórmulas que manipulem o contexto, mas o contexto também pode fazer com que os resultados sejam alterados de um modo não previsto. Para obter mais informações, consulte o artigo Contexto em Fórmulas DAX.

Atualizar os Resultados das Fórmulas

A atualização de d ados e o recálculo são duas operações separadas, mas relacionadas, que deve compreender ao estruturar um modelo de dados que contém fórmulas complexas, grandes quantidades de dados ou dados obtidos de origens de dados externas.

Atualizar dados é o processo de atualizar os dados existentes no seu livro com dados novos provenientes de uma origem de dados externa. Pode atualizar dados manualmente em intervalos por si especificados. Alternativamente, se tiver publicado o livro num site SharePoint, pode agendar uma atualização automática das origens externas.

O recálculo é o processo de atualização dos resultados das fórmulas para refletir quaisquer alterações efetuadas às próprias fórmulas e para refletir essas alterações nos dados subjacentes. O recálculo pode afetar o desempenho dos seguintes modos:

Para uma coluna calculada, o resultado da fórmula deve ser recalculado para a coluna completa sempre que 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 da gráfico dinâmico. A fórmula também será recalculada quando alterar qualquer cabeçalho de linha ou coluna que afete os filtros existentes nos dados ou quando atualizar manualmente a Tabela Dinâmica.

Resolução de Problemas de Fórmulas

Erros ao escrever fórmulas

Se obtiver um erro ao definir uma fórmula, a fórmula poderá conter um erro sintático, um erro semântico ou um erro de cálculo.

Os erros sintáticos são mais fáceis de resolver. Geralmente estão relacionados com um parênteses ou uma vírgula em falta. Para obter ajuda sobre a sintaxe de funções individuais, consulte a Referência de Funções DAX.

O outro tipo de erro ocorre quando a sintaxe está correta, mas o valor ou a coluna referenciada não faz sentido no contexto da fórmula. Este tipo de erros semânticos e de cálculo poderão ser causados por quaisquer dos seguintes problemas:

A fórmula refere-se a uma coluna, tabela ou função não existente.
A fórmula parece correta, mas quando o motor de dados obtém os dados deteta um erro de correspondência e apresenta o erro.
A fórmula transmite um número ou um tipo de parâmetros incorreto a uma função.
A fórmula refere-se a uma coluna diferente com um erro e, por isso, os respetivos valores são inválidos.
A fórmula refere-se a uma coluna que ainda não foi processada, o que significa que contém metadados mas não dados reais para utilizar em cálculos.

Nos quatro primeiros casos, o DAX sinaliza a coluna completa que contém a fórmula inválida. No último caso, o DAX torna a coluna inativa para indicar que esta está num estado não processado.

Resultados incorretos ou invulgares ao classificar ou ordenar valores da coluna

Ao classificar ou ordenar uma coluna que contém o valor NaN (Não Numérico), poderá obter resultados incorretos ou inesperados. Por exemplo, quando um cálculo divide 0 por 0, é devolvido um resultado NaN.

Isto deve-se ao facto do motor da fórmula ordenar e classificar ao comparar os valores numéricos; no entanto, NaN não pode ser comparado com outros números na coluna.

Para assegurar resultados corretos, pode utilizar instruções condicionais utilizando a função SE para testar os valores NaN e devolver um valor numérico de 0.

Compatibilidade com os Modelos Tabulares do Analysis Services e o Modo DirectQuery

De uma forma geral, as fórmulas do DAX criadas no Power Pivot são totalmente compatíveis com os modelos tabulares do Analysis Services. No entanto, se migrar um modelo do Power Pivot para uma instância do Analysis Services e, em seguida, implementar o modelo no modo DirectQuery, existem algumas limitações.

Algumas fórmulas do DAX poderão devolver resultados diferentes se implementar o modelo no modo DirectQuery.
Algumas fórmulas poderão causar erros de validação quando implementar o modelo no modo DirectQuery, porque a fórmula contém uma função do DAX que não é suportada contra uma origem de dados relacionais.

Para mais informações, consulte a documentação da modelação tabular do Analysis Services em SQL Server 2012 Books Online.