Excel DAX - Executando Cálculos Complexos

As fórmulas DAX podem realizar cálculos complexos que envolvem agregações personalizadas, filtragem e o uso de valores condicionais. Você pode fazer o seguinte com DAX -

Crie cálculos personalizados para uma tabela dinâmica.
Aplique um filtro a uma fórmula DAX.
Remova filtros seletivamente para criar uma proporção dinâmica.
Use um valor de um loop externo.

Criação de cálculos personalizados para uma tabela dinâmica

As funções DAX CALCULATE e CALCULATETABLE são poderosas e flexíveis. Eles são úteis para definir campos calculados. Essas funções DAX permitem que você altere o contexto no qual o cálculo será executado. Você também pode personalizar o tipo de agregação ou operação matemática a ser executada.

Função CALCULATE

CALCULAR (<expressão>, [<filtro1>], [<filtro2>] ...)

A função CALCULATE avalia a expressão dada em um contexto que é modificado por zero ou mais filtros especificados.

Se seus dados foram filtrados, a função CALCULATE altera o contexto no qual os dados são filtrados e avalia a expressão no novo contexto que você especifica pelos filtros. Isso significa que todos os filtros existentes na coluna especificada são removidos e o filtro usado no argumento do filtro é aplicado.

Exemplo

Suponha que você queira exibir a porcentagem de medalhas filtradas por nomes de países. Seu cálculo deve obter o valor da porcentagem substituindo o filtro que você aplica ao país na tabela dinâmica.

Defina um campo calculado - Porcentagem da contagem de medalhas, conforme mostrado na captura de tela a seguir.

Com esta fórmula DAX, todas as linhas da tabela Resultados são consideradas na função CALCULAR com o filtro que contém a função TODOS. Dessa forma, você tem a contagem total no denominador.

Sua tabela dinâmica será mostrada na imagem a seguir.

Na captura de tela acima, País é filtrado para EUA e os valores principais 18 são exibidos na Tabela Dinâmica. Em seguida, você pode filtrar valores dinamicamente na tabela dinâmica. No entanto, os cálculos serão corretos pela fórmula DAX personalizada que você usou.

A função CALCULATETABLE pega uma tabela de valores e executa a mesma ação da função CALCULATE.

Filtrando dados em fórmulas

Você pode criar filtros nas fórmulas DAX para selecionar os valores dos dados de origem para uso em cálculos. Você pode fazer isso definindo uma expressão de filtro e usando-a junto com a tabela que é uma entrada para a fórmula DAX.

A expressão de filtro permite que você obtenha um subconjunto dos dados de origem. O filtro é aplicado dinamicamente cada vez que você atualiza os resultados da fórmula DAX, dependendo do contexto atual de seus dados e você pode ter certeza de resultados precisos e esperados.

A expressão de filtro normalmente contém uma função de filtro DAX que retorna apenas as linhas selecionadas da tabela, que então podem ser usadas como um argumento para outra função DAX que você está usando para agregação de dados.

Exemplo

A captura de tela a seguir mostra a definição de um campo calculado que fornece a contagem de medalhas apenas para esportes de verão.

Com este campo calculado, a tabela dinâmica tem a aparência mostrada na captura de tela a seguir.

Como você pode observar, os valores da Tabela Dinâmica do lado direito com o novo campo calculado correspondem aos que estão na Tabela Dinâmica do lado esquerdo com o filtro no campo Temporada aplicado explicitamente.

Note - As funções de filtro e valor DAX retornam uma tabela, mas nunca retornam a tabela ou linhas diretamente para o Modelo de Dados e, portanto, estão sempre incorporadas em outra função DAX.

Para obter detalhes sobre essas funções DAX, consulte o capítulo - Funções de filtro DAX.

Adicionar e remover filtros dinamicamente

As fórmulas DAX que você usa em uma tabela dinâmica podem ser afetadas pelo contexto da tabela dinâmica. No entanto, você pode alterar seletivamente o contexto adicionando ou removendo filtros. Você pode usar as funções DAX ALL e ALLEXCEPT para selecionar dinamicamente as linhas, independentemente do contexto da tabela dinâmica.

Além disso, você pode usar as funções DAX DISTINCT e VALUES para retornar valores distintos.

Usando um valor de um loop externo

Você pode usar um valor de um loop anterior na criação de um conjunto de cálculos relacionados com a função DAX EARLIER. Esta função DAX suporta até dois níveis de loops aninhados.