Esta secção fornece ligações para exemplos que demonstram a utilização de fórmulas DAX nos seguintes cenários.
- Efetuar cálculos complexos
- Trabalhar com texto e datas
- Valores condicionais e verificação de erros
- Utilizar a análise de tempo
- Classificação e comparação de valores
Neste artigo
Introdução
Visite o Wiki do Centro de Recursos do DAX , onde você pode encontrar todos os tipos de informações sobre o DAX, incluindo blogs, amostras, whitepapers e vídeos fornecidos por profissionais líderes do setor e pela Microsoft.
Cenários: Executar cálculos complexos
As fórmulas DAX podem efetuar cálculos complexos que envolvem agregações personalizadas, filtragem e a utilização de valores condicionais. Esta secção fornece exemplos de como começar a utilizar cálculos personalizados.
Criar cálculos personalizados para uma Tabela Dinâmica
CALCULATE e CALCULATETABLE são funções avançadas e flexíveis que são úteis para definir campos calculados. Estas funções permitem-lhe alterar o contexto no qual o cálculo será efetuado. Também pode personalizar o tipo de agregação ou operação matemática a executar. Consulte os tópicos seguintes para obter exemplos.
Aplicar um filtro a uma fórmula
Na maioria dos locais onde uma função DAX utiliza uma tabela como argumento, normalmente pode transferir uma tabela filtrada ao utilizar a função FILTER em vez do nome da tabela ou ao especificar uma expressão do filtro como um dos argumentos da função. Os seguintes tópicos fornecem exemplos de como criar filtros e de como os filtros afetam os resultados das fórmulas. Para obter mais informações, consulte Filtrar Dados em Fórmulas DAX.
A função FILTRAR permite-lhe especificar critérios de filtragem ao utilizar uma expressão, enquanto as outras funções são criadas especificamente para filtrar valores em branco.
Remova filtros seletivamente para criar uma relação dinâmica
Através da criação de filtros dinâmicos em fórmulas, pode responder facilmente a perguntas como as seguintes:
- Qual foi a contribuição das vendas do produto atual para o total de vendas do ano?
- Quanto esta divisão contribuiu para os lucros totais de todos os anos operacionais, em comparação com outras divisões?
As fórmulas que utiliza numa Tabela Dinâmica podem ser afetadas pelo contexto de Tabela Dinâmica, mas pode alterar seletivamente o contexto ao adicionar ou remover filtros. O exemplo no tópico ALL mostra como fazer isso. Para encontrar a proporção das vendas de um revendedor específico em relação às vendas de todos os revendedores, crie uma medida que calcule o valor do contexto atual dividido pelo valor do contexto ALL.
O tópico ALLEXCEPT fornece um exemplo de como limpar seletivamente filtros numa fórmula. Ambos os exemplos irão guiá-lo pela forma como os resultados mudam, dependendo da estrutura da Tabela Dinâmica.
Para obter outros exemplos de como calcular rácios e percentagens, consulte os seguintes tópicos:
Utilizar um valor de um ciclo externo
Além de utilizar valores do contexto atual em cálculos, o DAX pode utilizar um valor de um ciclo anterior na criação de um conjunto de cálculos relacionados. O tópico seguinte fornece um passo a passo sobre como criar uma fórmula que referencia um valor a partir de um ciclo externo. A função ANTERIOR suporta até dois níveis de ciclos aninhados.
Para saber mais sobre o contexto de linha e as tabelas relacionadas, e como utilizar este conceito em fórmulas, consulte o artigo Contexto em Fórmulas DAX.
Cenários: Trabalhar com Texto e Datas
Esta secção fornece ligações para tópicos de referência do DAX que contêm exemplos de cenários comuns que envolvem trabalhar com texto, extrair e compor valores de data e hora ou criar valores com base numa condição.
Criar uma coluna chave por concatenação
O Power Pivot não permite chaves compostas; Portanto, se você tiver chaves compostas em sua fonte de dados, talvez seja necessário combiná-las em uma única coluna de chave. O tópico seguinte fornece um exemplo de como criar uma coluna calculada com base numa chave composta.
Compose uma data com base em partes de data extraídas de uma data de texto
O Power Pivot utiliza um tipo de dados Data/Hora SQL Server para trabalhar com datas; por isso, se os seus dados externos contiverem datas com formatos diferentes – por exemplo, se as suas datas forem escritas num formato de data regional que não é reconhecido pelo motor de dados do Power Pivot ou se os seus dados utilizarem teclas substitutas de número inteiro – poderá ter de utilizar uma fórmula DAX para extrair as partes de data e, em seguida, compor as partes numa data válida/ representação do tempo.
Por exemplo, se tiver uma coluna de datas que tenham sido representadas como um número inteiro e posteriormente importadas como uma cadeia de texto, pode converter a cadeia para um valor de data/hora utilizando a seguinte fórmula:
=DATA(DIREITA([Valor1];4);ESQUERDA([Valor1];2);SEG.TEXTO([Valor1];2))
| Valor1 | Resultado |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Os tópicos seguintes fornecem mais informações sobre as funções utilizadas para extrair e compor datas.
Definir uma data ou formato de número personalizado
Se os seus dados contiverem datas ou números que não estejam representados num dos formatos de texto padrão do Windows, pode definir um formato personalizado para garantir que os valores são processados corretamente. Estes formatos são utilizados ao converter valores em cadeias ou a partir de cadeias. Os tópicos seguintes também fornecem uma lista detalhada dos formatos predefinidos que estão disponíveis para trabalhar com datas e números.
- Formatos numéricos predefinidos para a função FORMAT
- Formatos numéricos personalizados para a função FORMATAR
- Formatos de data e hora predefinidos para a função FORMATAR
- Formatos personalizados de data e hora para a função FORMATAR
Alterar tipos de dados usando uma fórmula
No Power Pivot, o tipo de dados da saída é determinado pelas colunas de origem e você não pode especificar explicitamente o tipo de dados do resultado, pois o tipo de dados ideal é determinado pelo Power Pivot. No entanto, você pode usar as conversões de tipo de dados implícitas executadas pelo Power Pivot para manipular o tipo de dados de saída.
- Para converter uma data ou uma cadeia numérica em um número, multiplique por 1,0. Por exemplo, a fórmula a seguir calcula a data atual menos 3 dias e gera o valor inteiro correspondente.
=(HOJE()-3)*1,0 - Para converter uma data, um número ou um valor de 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.
=""& HOJE()
As funções a seguir também podem ser usadas para garantir que um determinado tipo de dados seja retornado:
Converter números reais em números inteiros
- Função ROUND
- Função TETO
-
Função ARREDMULTB
Converter números reais, números inteiros ou datas em cadeias de caracteres - Função FIXO
-
Função FORMATO
Converter cadeias de caracteres em números reais ou datas - Função VALOR
- Função DATA.VALOR
- Função VALOR.TEMPO
Cenário: valores condicionais e teste para erros
Como o Excel, o DAX tem funções que permitem testar valores nos dados e retornar um valor diferente com base em uma condição. Por exemplo, você pode criar uma coluna calculada que rotula os revendedores como Preferenciais ou Valor , dependendo do valor das vendas anuais. As funções que testam valores também são úteis para verificar o intervalo ou o tipo de valores, para evitar que erros inesperados de dados quebrem cálculos.
Criar um valor com base em uma condição
Você pode usar condições SE aninhadas para testar valores e gerar novos valores condicionalmente. Os tópicos a seguir contêm alguns exemplos simples de processamento condicional e valores condicionais:
Teste para encontrar erros em uma fórmula
Ao contrário do Excel, você não pode ter valores válidos em uma 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 Power Pivot, toda a coluna será sinalizada com um erro, para que você sempre precise corrigir erros de fórmula que resultem em valores inválidos.
Por exemplo, se você criar uma fórmula que divide por zero, poderá obter o resultado infinito ou um erro. Algumas fórmulas também falharão se a função encontrar um valor em branco quando espera um valor numérico. Enquanto você estiver desenvolvendo seu modelo de dados, é melhor permitir que os erros apareçam para que você possa clicar na mensagem e solucionar o problema. No entanto, ao publicar pastas de trabalho, você deve incorporar o tratamento de erros para impedir que valores inesperados causem falha nos cálculos.
Para evitar retornar erros em uma coluna calculada, use uma combinação de funções lógicas e de informações para testar erros e sempre retornar valores válidos. Os tópicos a seguir fornecem alguns exemplos simples de como fazer isso no DAX:
Cenários: usando a inteligência de tempo
As funções de inteligência de dados temporais do DAX incluem funções para ajudá-lo a recuperar datas ou intervalos de datas de seus dados. Você pode usar essas datas ou intervalos de datas para calcular valores em períodos semelhantes. As funções de inteligência de dados temporais também incluem funções que funcionam com intervalos de datas padrão, para permitir que você compare valores entre meses, anos ou trimestres. Você também pode criar uma fórmula que compare os valores da primeira e da última data de um período especificado.
Para obter uma 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 com eficiência em uma análise do Power Pivot, consulte Datas no Power Pivot.
Calcular vendas cumulativas
Os tópicos a seguir contêm exemplos de como calcular saldos de fechamento e de abertura. Os exemplos permitem criar saldos acumulados em diferentes intervalos, 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 ao longo do tempo
Os tópicos a seguir contêm exemplos de como comparar somas em diferentes períodos de tempo. Os períodos de tempo padrão com suporte do DAX são meses, trimestres e anos.
- Função PREVIOUSMONTH, PREVIOUSQUARTER,Função PREVIOUSYEAR
- Função TOTALMTD, função TOTALQTD, função TOTALYTD
- Função PARALLELPERIOD
Calcular um valor sobre 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 após o início de uma promoção de vendas.
Se você usar funções de inteligência de dados temporais 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 entre períodos de tempo. Consulte o tópico a seguir para obter um exemplo de como fazer isso:
-
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 que execute cálculos usando as funções de inteligência de dados temporais projetadas para esse fim, como TOTALQTD, TOTALMTD, TOTALQTD etc.
Cenários: classificação e comparação de valores
Para mostrar apenas o número n superior de itens em uma coluna ou Tabela Dinâmica, você tem várias opções:
- Você pode usar os recursos do Excel para criar um filtro superior. Você também pode selecionar vários valores superiores ou inferiores em uma Tabela Dinâmica. A primeira parte desta seção descreve como filtrar os 10 principais itens em uma Tabela Dinâmica. Para obter mais informações, consulte a documentação do Excel.
- Você pode criar uma fórmula que classifica valores dinamicamente e filtrar por valores de classificação ou usar o valor de classificação como uma Segmentação de Dados. A segunda parte desta seção descreve como criar essa fórmula e usar essa classificação em uma Segmentação de Dados.
Existem vantagens e desvantagens em cada método.
- O filtro Excel Top é fácil de usar, mas serve exclusivamente para fins de exibição. Se os dados subjacentes à Tabela Dinâmica forem alterados, você deverá atualizar manualmente a Tabela Dinâmica para ver as alterações. Se você precisar trabalhar dinamicamente com classificações, poderá usar o DAX para criar uma fórmula que compare valores a outros valores em uma coluna.
- A fórmula DAX é mais poderosa; além disso, ao adicionar o valor de classificação a uma Segmentação, você pode simplesmente clicar na Segmentação para alterar o número de valores principais exibidos. No entanto, os cálculos são computacionalmente caros e esse método pode não ser adequado para tabelas com muitas linhas.
Mostrar apenas os dez primeiros itens em uma Tabela Dinâmica
Para mostrar os valores superiores ou inferiores em uma Tabela Dinâmica
|
|---|
Ordenar itens dinamicamente através de uma fórmula
O tópico seguinte contém um exemplo de como utilizar o DAX para criar uma classificação que é armazenada numa coluna calculada. Uma vez que as fórmulas do DAX são calculadas dinamicamente, pode sempre ter a certeza de que a classificação está correta, mesmo que os dados subjacentes tenham sido alterados. Para além disso, como a fórmula é utilizada numa coluna calculada, pode utilizar a classificação numa Segmentação de Dados e, em seguida, selecionar os 5 primeiros, os 10 primeiros ou até os 100 primeiros valores.