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 | Result |
|---|---|
| 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 de Data e Hora Personalizados para a Função FORMATAR
Alterar tipos de dados através de uma fórmula
No Power Pivot, o tipo de dados do resultado é determinado pelas colunas de origem e não é possível especificar explicitamente o tipo de dados do resultado, uma vez que o tipo de dados ideal é determinado pelo Power Pivot. No entanto, pode utilizar as conversões de tipos de dados implícitas efetuadas pelo Power Pivot para manipular o tipo de dados de saída.
- Para converter uma data ou uma cadeia numérica num número, multiplique por 1,0. Por exemplo, a fórmula seguinte calcula a data atual menos 3 dias e, em seguida, produz o valor inteiro correspondente.
=(HOJE()-3)*1,0 - Para converter um valor de data, número ou moeda numa cadeia, concatene o valor com uma cadeia vazia. Por exemplo, a fórmula seguinte devolve a data de hoje como uma cadeia.
=""& HOJE()
As seguintes funções também podem ser utilizadas para garantir que é devolvido um tipo de dados específico:
Converter números reais em números inteiros
- Função ARRED
- Função ARRED.EXCESSO
-
Função ARRED.DEFEITO
Converter números reais, números inteiros ou datas em cadeias - Função FIXA
-
Função FORMAT
Converter cadeias em números ou datas reais - Função VALOR
- Função DATA.VALOR
- Função VALOR.TEMPO
Cenário: valores condicionais e verificação de erros
Tal como o Excel, DAX tem funções que lhe permitem testar valores nos dados e devolver um valor diferente com base numa condição. Por exemplo, pode criar uma coluna calculada que identifica os revendedores como Preferenciais ou Valor , dependendo do montante de vendas anual. As funções que testam valores também são úteis para verificar o intervalo ou tipo de valores, para impedir que erros de dados inesperados quebrem os cálculos.
Criar um valor com base numa condição
Pode utilizar condições SE aninhadas para testar valores e gerar novos valores condicionalmente. Os tópicos seguintes contêm alguns exemplos simples de processamento condicional e valores condicionais:
Verificar se existem erros numa fórmula
Ao contrário do Excel, não pode ter valores válidos numa linha de uma coluna calculada e valores inválidos noutra linha. Isto é, se existir um erro em qualquer parte de uma coluna do Power Pivot, toda a coluna é sinalizada com um erro, pelo que tem de corrigir sempre os erros de fórmulas que resultem em valores inválidos.
Por exemplo, se criar uma fórmula que se divide por zero, poderá obter o resultado infinito ou um erro. Algumas fórmulas também irão falhar se a função encontrar um valor em branco quando espera um valor numérico. Enquanto está a desenvolver o seu modelo de dados, é melhor permitir que os erros apareçam para que possa clicar na mensagem e resolver o problema. No entanto, quando publica livros, deve incorporar o processamento de erros para impedir que valores inesperados provoquem a falha dos cálculos.
Para evitar devolver erros numa coluna calculada, utilize uma combinação de funções lógicas e de informação para testar os erros e devolver sempre valores válidos. Os tópicos a seguir fornecem alguns exemplos simples de como fazer isso no DAX:
Cenários: Utilizar Análise de Tempo
As funções de análise de tempo do DAX incluem funções para o ajudar a obter datas ou intervalos de datas dos seus dados. Em seguida, pode utilizar essas datas ou intervalos de datas para calcular valores em períodos semelhantes. As funções de análise de tempo também incluem funções que funcionam com intervalos de data padrão, para permitir comparar valores ao longo de meses, anos ou trimestres. Também pode criar uma fórmula que compare valores da primeira e ú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 tempo (DAX). Para obter sugestões sobre como utilizar datas e horas de forma eficaz numa análise do Power Pivot, consulte Datas no Power Pivot.
Calcular vendas acumuladas
Os tópicos a seguir contêm exemplos de como calcular saldos de fechamento e abertura. Os exemplos permitem-lhe criar saldos correntes 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 ao longo do tempo
Os tópicos seguintes contêm exemplos de como comparar somas em diferentes períodos de tempo. Os períodos de tempo predefinidos suportados pelo 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 acima de um intervalo de datas personalizado
Consulte os tópicos seguintes para obter exemplos de como obter intervalos de datas personalizados, como os primeiros 15 dias após o início de uma promoção de vendas.
Se utilizar funções de análise de tempo para obter um conjunto personalizado de datas, pode utilizar esse conjunto de datas como entrada para uma função que efetua cálculos para criar agregados personalizados em períodos de tempo. Consulte o tópico seguinte para obter um exemplo de como fazê-lo:
-
Nota
Se não precisar de especificar um intervalo de datas personalizado, mas estiver a trabalhar com unidades de contabilidade padrão, como meses, trimestres ou anos, recomendamos que efetue cálculos com as funções de análise de tempo concebidas para este efeito, como TOTALQTD, TOTALMTD, TOTALQTD, etc.
Cenários: Classificação e Comparação de Valores
Para mostrar apenas o número n superior de itens numa coluna ou tabela dinâmica, tem várias opções:
- Pode utilizar as funcionalidades no Excel para criar um filtro Superior. Também pode selecionar alguns valores superiores ou inferiores numa tabela dinâmica. A primeira parte desta secção descreve como filtrar os 10 principais itens de uma Tabela Dinâmica. Para obter mais informações, consulte a documentação do Excel.
- Pode criar uma fórmula que ordene valores de forma dinâmica e, em seguida, filtrar pelos valores de classificação ou utilizar o valor de classificação como uma Segmentação de Dados. A segunda parte desta secção descreve como criar esta fórmula e, em seguida, utilizar essa classificação numa Segmentação de dados.
Existem vantagens e desvantagens em cada método.
- O filtro Excel Top é fácil de utilizar, mas destina-se apenas a fins de visualização. Se os dados subjacentes à Tabela Dinâmica forem alterados, terá de atualizar manualmente a Tabela Dinâmica para ver as alterações. Se precisar de trabalhar dinamicamente com ordenações, pode utilizar o DAX para criar uma fórmula que compara valores com outros valores numa coluna.
- A fórmula DAX é mais poderosa; além disso, ao adicionar o valor de classificação a uma Segmentação de Dados, pode simplesmente clicar na Segmentação de Dados para alterar o número de valores superiores que são apresentados. No entanto, os cálculos são computacionalmente caros e este método pode não ser adequado para tabelas com muitas linhas.
Mostrar apenas os dez principais itens numa tabela dinâmica
Para mostrar os valores superiores ou inferiores numa 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.