Cenários DAX
Esta seção fornece links para exemplos que demonstram o uso de fórmulas DAX nos cenários a seguir.
Executando cálculos complexos
Trabalhando com textos e datas
Valores condicionais e testes de erros
Usando inteligência de tempo
Classificando e comparando valores
Introdução
Se você ainda não conhece as fórmulas DAX, recomendamos começar revisando os exemplos na pasta de trabalho DAX Sample. Para obter mais informações sobre como conseguir a pasta de trabalho de exemplo, consulte Obter dados de exemplo para o PowerPivot
Recursos adicionais
É possível usar os links a seguir para localizar vídeos, exemplos adicionais e instruções passo a passo para ajudar você a aprender DAX.
Cenários: Executando cálculos complexos
As fórmulas DAX podem executar cálculos complexos que envolvem agregações personalizadas, filtragens e o uso de valores condicionais. Esta seção fornece exemplos de como começar a usar cálculos personalizados.
Criar cálculos personalizados para uma Tabela Dinâmica
CALCULATE e CALCULATETABLE são funções sofisticadas, flexíveis, úteis para definir medidas. Essas funções permitem alterar o contexto no qual o cálculo será executado. Também é possível personalizar o tipo de agregação ou operação matemática a ser executada. Consulte os tópicos a seguir para obter exemplos.
Aplicar um filtro a uma fórmula
Na maioria dos lugares onde uma função DAX usa uma tabela como um argumento, normalmente é possível passar uma tabela filtrada em seu lugar, usando a função FILTER em vez do nome da tabela ou especificando uma expressão de filtro como um dos argumentos de função. Os tópicos a seguir fornecem exemplos de como criar filtros e como os filtros afetam os resultados de fórmulas. Para obter mais informações, consulte Filtrar dados em fórmulas.
A função FILTER permite especificar critérios de filtro, usando uma expressão, enquanto as outras funções foram projetadas especificamente para filtrar valores em branco.
Remover filtros seletivamente para criar uma razão dinâmica
Criando-se filtros dinâmicos em fórmulas, é possível responder facilmente perguntas como as seguintes:
Qual foi a contribuição das vendas do produto atual para o total de vendas do ano?
Qual foi a contribuição dessa divisão para o lucro total em todos os anos operacionais, comparada a outras divisões?
As fórmulas usadas em uma Tabela Dinâmica podem ser afetadas pelo contexto da Tabela Dinâmica, embora seja possível alterar seletivamente o contexto, adicionando-se ou removendo filtros. O exemplo no tópico de ALL mostra como fazer isso. Para achar a razão das vendas de um revendedor específico em relação às vendas de todos os revendedores, você cria uma medida que calcula o valor no contexto atual dividido pelo valor no contexto de ALL.
O tópico de ALLEXCEPT fornece um exemplo de como limpar seletivamente filtros em uma fórmula. Ambos os exemplos mostram como os resultados mudam dependendo do design da Tabela Dinâmica.
Para obter outros exemplos de como calcular razões e porcentagens, consulte estes tópicos:
Usando um valor de um loop externo
Além de usar valores do contexto atual em cálculos, o DAX pode usar um valor de um loop anterior na criação de um conjunto de cálculos relacionados. O tópico a seguir fornece um passo a passo de como compilar uma fórmula que referencia um valor de um loop externo. A função EARLIER dá suporte a até dois níveis de loops aninhados.
Para saber mais sobre o contexto da linha e as tabelas relacionadas, além de como usar esse conceito em fórmulas, consulte Contexto em fórmulas DAX.
Voltar ao início
Cenários: Trabalhando com textos e datas
Esta seção fornece links para tópicos de referência DAX que contêm exemplos de cenários comuns que envolvem o trabalho com texto, a extração e a composição de valores de data e hora, além da criação de valores com base em uma condição.
Criar uma coluna de chave por concatenação
O PowerPivot não permite chaves compostas; portanto, se tiver chaves compostas na fonte de dados, você talvez precise integrá-las em uma única coluna de chave. O tópico a seguir fornece um exemplo de como criar uma coluna calculada com base em uma chave composta.
Compor uma data com base em partes de data extraídas de uma data de texto
O PowerPivot usa um tipo de dados data/hora do SQL Server para trabalhar com datas; portanto, se os dados externos contiverem datas formatadas diferentemente -- por exemplo, se as datas forem escritas em um formato de data regional não reconhecido pelo mecanismo de dados PowerPivot ou se os dados usarem chaves substitutas de inteiro --, você talvez precise usar uma fórmula DAX para extrair as partes de data e, em seguida, compor as partes em uma representação de data/hora válida.
Por exemplo, se você tiver uma coluna de datas representadas como um inteiro e, em seguida, importadas como uma cadeia de caracteres de texto, será possível converter a cadeia de caracteres em um valor de data/hora por meio da seguinte fórmula:
=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))
Valor1 |
Resultado |
01032009 |
1/3/2009 |
12132008 |
12/13/2008 |
06252007 |
6/25/2007 |
Os tópicos a seguir fornecem mais informações sobre as funções usadas para extrair e compor datas.
Definir uma data personalizada ou um formato de número
Se os dados contiverem datas ou números não representados em um dos formatos de texto padrão do Windows, será possível definir um formato personalizado para verificar se os valores foram tratados corretamente. Esses formatos são usados durante a conversão de valores em cadeias de caracteres ou vice-versa. Os tópicos a seguir também fornecem uma lista detalhada dos formatos predefinidos disponíveis para o trabalho com datas e números.
Alterar tipos de dados usando uma fórmula
No PowerPivot, o tipo de dados da saída é determinado pelas colunas de origem, e não é possível especificar explicitamente o tipo de dados do resultado, porque o tipo de dados ideal é determinado pelo PowerPivot. No entanto, é possível usar as conversões de tipo de dados implícitos executadas pelo PowerPivot para manipular o tipo de dados da saída. Para obter mais informações sobre conversões de tipo, consulte Fontes de dados com suporte em pastas de trabalho PowerPivot.
Para converter uma cadeia de caracteres de data ou de número em um número, multiplique-a por 1.0. Por exemplo, a fórmula a seguir calcula a data atual menos 3 dias e, em seguida, produz o valor de inteiro correspondente.
=(TODAY()-3)*1.0Para converter um valor de data, número ou moeda em uma cadeia de caracteres, concatene o valor com uma cadeia de caracteres vazia. Por exemplo, a fórmula a seguir retorna a data de hoje como uma cadeia de caracteres.
=""& TODAY()
As seguintes funções também podem ser usadas para verificar se um determinado tipo de dados é retornado:
Converter números reais em inteiros
Converter números reais, inteiros ou datas em cadeias de caracteres
Converter cadeias de caracteres em números reais ou datas
Voltar ao início
Cenário: Valores condicionais e testes de erros
Assim como o Excel, o DAX tem funções que permitem testar valores nos dados e retornam um valor diferente com base em uma condição. Por exemplo, você poderia criar uma coluna calculada que identifica revendedores como Preferido ou Valor dependendo do valor das vendas anual. As funções que testam valores também são úteis para verificar o intervalo ou o tipo de valores a fim de evitar que erros de dados inesperados interrompam cálculos.
Criar um valor com base em uma condição
É possível usar condições de IF aninhadas para testar valores e gerar novos valores condicionalmente. Os seguintes tópicos contêm alguns exemplos simples de processamento condicional e valores condicionais:
Testar se há erros dentro de uma fórmula
Diferentemente do Excel, não é possível ter valores válidos em uma só linha de uma coluna calculada e valores inválidos em outra linha. Ou seja, se houver um erro em qualquer parte de uma coluna do PowerPivot, toda a coluna será sinalizada com um erro, de forma que você deva sempre corrigir erros de fórmula que resultem em valores inválidos.
Por exemplo, se criar uma fórmula que divida por zero, você talvez possa obter o resultado de infinito, ou um erro. Também haverá falha em algumas fórmulas se a função encontrar um valor em branco quando esperar um valor numérico. Enquanto estiver desenvolvendo o modelo de dados, é melhor permitir que os erros sejam exibidos de forma que você possa clicar na mensagem e solucionar o problema. No entanto, ao publicar pastas de trabalho, você deve incorporar a tratamento de erros para impedir que valores inesperados causem falhas em cálculos.
Para evitar o retorno de erros em uma coluna calculada, você usa uma combinação de funções lógicas e de informações para testar se há erros e sempre retornar valores válidos. Os seguintes tópicos fornecem alguns exemplos simples de como fazer isso no DAX:
Voltar ao início
Cenários: Usando inteligência de tempo
As funções de inteligência de tempo DAX incluem funções para ajudar a recuperar datas ou intervalos de datas dos dados. Em seguida, é possível usar essas datas ou intervalos de datas para calcular valores em períodos semelhantes. As funções de inteligência de tempo também incluem funções que trabalham com intervalos de datas padrão para permitir comparar valores de meses, anos ou trimestres. Também é possível criar uma fórmula que compare valores da primeira e da última data de um período especificado.
Para obter lista de todas as funções de inteligência de tempo, consulte Funções de inteligência de dados temporais (DAX). Para obter dicas sobre como usar datas e horas efetivamente em uma análise do PowerPivot, consulte Datas no PowerPivot.
Calcular vendas cumulativas
Os tópicos a seguir contêm exemplos de como calcular saldos inicial e final. Os exemplos permitem criar saldos parciais em intervalos diferentes, como dias, meses, trimestres ou anos.
Função CLOSINGBALANCEMONTH, Função CLOSINGBALANCEQUARTER, Função CLOSINGBALANCEYEAR
Função OPENINGBALANCEMONTH, Função OPENINGBALANCEQUARTER, Função OPENINGBALANCEYEAR
Comparar valores com o passar do tempo
Os tópicos a seguir contêm exemplos de como comparar somas de períodos diferentes. Os períodos padrão suportados pelo DAX são meses, trimestres e anos.
Calcular um valor com o passar do tempo de um intervalo de datas personalizado
Consulte os tópicos a seguir para obter exemplos de como recuperar intervalos de datas personalizados, como os primeiros 15 dias depois do início de uma promoção de vendas.
Se você usar funções de inteligência de tempo para recuperar um conjunto personalizado de datas, poderá usar esse conjunto de datas como entrada para uma função que executa cálculos, para criar agregações personalizadas em períodos de tempo. Consulte o seguinte tópico para obter um exemplo de como fazer isso:
.gif "Observação") Observação |
|---|
Se você não precisar especificar um intervalo de datas personalizado, mas estiver trabalhando com unidades contábeis padrão, como meses, trimestres ou anos, recomendamos a execução de cálculos usando-se as funções de inteligência de tempo projetadas com essa finalidade, como TOTALQTD, TOTALMTD, TOTALQTD etc. |
Voltar ao início
Cenários: Classificando e comparando valores
Para mostrar apenas o n primeiro número de itens em uma coluna ou Tabela Dinâmica, você tem várias opções:
É possível usar os recursos do Excel 2010 para criar um filtro Superior. Também é possível selecionar um determinado número de valores superiores ou inferiores em uma Tabela Dinâmica. A primeira parte desta seção descreve como filtrar os 10 itens superiores em uma Tabela Dinâmica. Para obter mais informações, consulte a documentação do Excel.
É possível criar uma fórmula que classifica valores dinamicamente e, em seguida, filtrar os valores de classificação ou usá-los como uma Segmentação de Dados. A segunda parte desta seção descreve como criar essa fórmula e, em seguida, usar essa classificação em uma segmentação de dados.
Há vantagens e desvantagens em cada método.
O filtro Superior do Excel é fácil usar, mas exclusivamente para fins de exibição. Se os dados forem subjacentes às alterações da Tabela Dinâmica, você deverá atualizar manualmente a Tabela Dinâmica para ver as alterações. Se for necessário trabalhar dinamicamente com classificações, será possível usar o DAX para criar uma fórmula que compare valores com outros valores dentro de uma coluna.
A fórmula DAX é mais avançada; além disso, adicionando-se o valor da classificação a uma segmentação de dados, basta clicar na Segmentação de Dados para alterar o número de valores superiores exibidos. No entanto, os cálculos são caros em termos computacionais e esse método talvez não seja apropriado a tabelas com muitas linhas.
Mostrar apenas os dez primeiros itens uma Tabela Dinâmica
Para mostrar os valores superiores ou inferiores em uma Tabela Dinâmica |
||||||||
|
Classificar itens dinamicamente usando-se uma fórmula
O próximo tópico contém um exemplo de como usar DAX para criar uma classificação armazenada em uma coluna calculada. Como as fórmulas DAX são calculadas dinamicamente, você sempre terá certeza de que a classificação está correta, mesmo se os dados subjacentes tiverem sido alterados. Além disso, como a fórmula é usada em uma coluna calculada, é possível usar a classificação em uma segmentação de dados e, em seguida, selecionar os 5, os 10 ou mesmo os 100 primeiros valores.
Voltar ao início
Consulte também
Conceitos
Visão geral da linguagem DAX (Data Analysis Expressions)
Adicionar cálculos a relatórios, gráficos e tabelas dinâmicas