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 texto e datas
- Valores condicionais e testes para erros
- Usando inteligência temporal
- Classificação e comparação de valores
Neste artigo
Introdução
Visite o DaX Resource Center Wiki , onde você pode encontrar todos os tipos de informações sobre o DAX, incluindo blogs, exemplos, whitepapers e vídeos fornecidos por profissionais líderes do setor e pela Microsoft.
Cenários: executando cálculos complexos
As fórmulas DAX podem executar cálculos complexos que envolvem agregações personalizadas, filtragem 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 poderosas e flexíveis que são úteis para definir campos calculados. Essas funções permitem alterar 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. Consulte os tópicos a seguir para obter exemplos.
Aplicar um filtro a uma fórmula
Na maioria dos lugares em que uma função DAX usa uma tabela como argumento, você geralmente pode passar uma tabela filtrada, 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 das fórmulas. Para obter mais informações, consulte Filtrar dados em fórmulas DAX.
A função FILTER permite especificar critérios de filtro usando uma expressão, enquanto as outras funções são projetadas especificamente para filtrar valores em branco.
Remover filtros seletivamente para criar uma taxa dinâmica
Ao criar filtros dinâmicos em fórmulas, você pode responder facilmente a perguntas como:
- Qual foi a contribuição das vendas do produto atual para o total de vendas do ano?
- Quanto essa divisão contribuiu para o lucro total de todos os anos operacionais, em comparação com outras divisões?
As fórmulas que você usa em uma Tabela Dinâmica podem ser afetadas pelo contexto de Tabela Dinâmica, mas você pode alterar seletivamente o contexto adicionando ou removendo filtros. O exemplo no tópico ALL mostra como fazer isso. Para encontrar a proporção de vendas de um revendedor específico sobre as vendas para todos os revendedores, crie uma medida que calcula o valor do contexto atual dividido pelo valor para o contexto ALL.
O tópico ALLEXCEPT fornece um exemplo de como limpar seletivamente filtros em uma fórmula. Ambos os exemplos explicam como os resultados mudam dependendo do design da Tabela Dinâmica.
Para obter outros exemplos de como calcular taxas e percentuais, confira os seguintes tópicos:
Usando um valor de um loop externo
Além de usar valores do contexto atual nos 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 criar 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 tabelas relacionadas e como usar esse conceito em fórmulas, consulte Contexto em Fórmulas DAX.
Cenários: trabalhando com texto e datas
Esta seção fornece links para tópicos de referência DAX que contêm exemplos de cenários comuns envolvendo trabalhar com texto, extrair e compor valores de data e hora ou criar valores com base em uma condição.
Criar uma coluna de chave por concatenação
O Power Pivot não permite chaves compostas; Portanto, se você tiver chaves compostas na fonte de dados, talvez seja necessário combiná-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 Power Pivot usa um tipo de dados de data/hora do SQL Server para trabalhar com datas; Portanto, se seus dados externos contiverem datas formatadas de forma diferente -- por exemplo, se suas datas forem escritas em um formato de data regional que não seja reconhecido pelo mecanismo de dados do Power Pivot ou se seus dados usarem chaves substitutas inteiros , talvez seja necessário 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 que foram representadas como inteiros e importadas como uma cadeia de caracteres de texto, você poderá converter a cadeia de caracteres em um valor de data/hora usando a 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 um formato de data ou número personalizado
Se seus dados contiverem datas ou números que não estão representados em um dos formatos de texto padrão do Windows, você poderá definir um formato personalizado para garantir que os valores sejam tratados corretamente. Esses formatos são usados ao converter valores em cadeias de caracteres ou de cadeias de caracteres. Os tópicos a seguir também fornecem uma lista detalhada dos formatos predefinidos que estão disponíveis para trabalhar com datas e números.
- Formatos numéricos pré-definidos para a função FORMAT
- Formatos numéricos personalizados para a Função FORMAT
- Formatos de Data e Hora predefinidos para a Função FORMAT
- Formatos de Data e Hora Personalizados para a Função FORMAT
Alterar tipos de dados com uma fórmula
No Power Pivot, o tipo de dados da saída é determinado pelas colunas de origem e não pode especificar explicitamente o tipo de dados do resultado, porque o tipo de dados ideal é determinado pelo Power Pivot. No entanto, pode utilizar as conversões implícitas de tipos de dados executadas pelo Power Pivot para manipular o tipo de dados de saída.
- Para converter uma data ou uma cadeia de números 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 seguinte fórmula devolve a data de hoje como uma cadeia.
=""& HOJE()
As seguintes funções também podem ser utilizadas para garantir que é devolvido um determinado tipo de dados:
Converter números reais em números inteiros
- Função ARRED
- Função ARRED.EXCESSO
-
Função FLOOR
Converter números reais, números inteiros ou datas em cadeias - Função FIXA
-
Função FORMAT
Converter cadeias em números reais ou datas - Função VALOR
- Função DATA.VALOR
- Função VALOR.TEMPO
Cenário: Valores Condicionais e Teste de Erros
Tal como o Excel, o 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 etiqueta os revendedores como Preferenciais ou Valor , consoante o valor de vendas anual. As funções que testam valores também são úteis para verificar o intervalo ou o tipo de valores, para evitar erros de dados inesperados de quebra de 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:
Testar 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. Ou seja, se existir um erro em qualquer parte de uma coluna do Power Pivot, toda a coluna é sinalizada com um erro, para que tenha sempre de corrigir erros de fórmula que resultem em valores inválidos.
Por exemplo, se criar uma fórmula que se divide por zero, poderá obter o resultado do 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 estiver a desenvolver o seu modelo de dados, é melhor permitir que os erros sejam apresentados 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 causem falhas nos cálculos.
Para evitar devolver erros numa coluna calculada, utilize uma combinação de funções lógicas e de informação para testar erros e devolver sempre valores válidos. Os tópicos seguintes fornecem alguns exemplos simples de como fazê-lo no DAX:
Cenários: Utilizar a Análise de Tempo
As funções de análise de tempo 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 lhe permitir comparar valores entre meses, anos ou trimestres. Também pode criar uma fórmula que compara os valores da primeira e última data de um período especificado.
Para obter uma lista de todas as funções de análise de tempo, veja Time Intelligence Functions (DAX). Para obter sugestões sobre como utilizar datas e horas de forma eficaz numa análise do Power Pivot, veja Dates in Power Pivot (Datas no Power Pivot).
Calcular vendas cumulativas
Os tópicos seguintes contêm exemplos de como calcular os saldos de fecho e abertura. Os exemplos permitem-lhe criar saldos de execução 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, PreviousYEAR Function
- Função TOTALMTD, Função TOTALQTD, Função TOTALYTD
- Função PARALLELPERIOD
Calcular um valor num intervalo de datas personalizado
Veja 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 executa cálculos, para criar agregações personalizadas ao longo dos períodos de tempo. Veja o tópico seguinte para obter um exemplo de como fazê-lo:
-
Observação
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 esta finalidade, tais 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 vários valores superiores ou inferiores numa Tabela Dinâmica. A primeira parte desta secção descreve como filtrar os 10 principais itens numa tabela dinâmica. Para obter mais informações, consulte a documentação do Excel.
- Pode criar uma fórmula que classifica dinamicamente os valores e, em seguida, filtrar pelos valores de classificação ou utilizar o valor de classificação como 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 para cada método.
- O filtro Superior do Excel é fácil de utilizar, mas o filtro é apenas para fins de apresentação. Se os dados subjacentes à tabela dinâmica forem alterados, tem de atualizar manualmente a tabela dinâmica para ver as alterações. Se precisar de trabalhar dinamicamente com classificações, pode utilizar o DAX para criar uma fórmula que compare 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, basta clicar na Segmentação de Dados para alterar o número de valores principais apresentados. No entanto, os cálculos são computacionais dispendiosos e este método pode não ser adequado para tabelas com muitas linhas.
Mostrar apenas os dez primeiros itens numa Tabela Dinâmica
Para mostrar os valores superiores ou inferiores numa Tabela Dinâmica
|
|---|
Encomendar itens dinamicamente usando uma fórmula
O tópico a seguir contém um exemplo de como usar o DAX para criar uma classificação armazenada em uma coluna calculada. Como as fórmulas DAX são calculadas dinamicamente, você sempre pode 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, você pode usar a classificação em um Slicer e, em seguida, selecionar os 5, top 10 ou até mesmo os 100 valores superiores.