Esta secção fornece ligações para exemplos que demonstram a utilização de fórmulas DAX nos seguintes cenários.
-
Realizar cálculos complexos
-
Trabalhar com texto e datas
-
Valores condicionais e teste de erros
-
Utilizar a análise de tempo
-
Classificar e comparar valores
Neste artigo
Introdução
Visite o Wiki do Centro de Recursos da DAX , onde pode encontrar todo o tipo de informações sobre o DAX, incluindo blogues, exemplos, documentos técnicos e vídeos fornecidos por profissionais líderes do setor e pela Microsoft.
Cenários: Executar Cálculos Complexos
As fórmulas DAX podem realizar cálculos complexos que envolvem agregações personalizadas, filtragem e 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. Veja os tópicos seguintes para obter exemplos.
Aplicar um filtro a uma fórmula
Na maioria dos locais em que uma função DAX utiliza uma tabela como argumento, normalmente pode transmitir uma tabela filtrada, quer utilizando a função FILTER, em vez do nome da tabela, quer especificando uma expressão de filtro como um dos argumentos de função. Os tópicos seguintes fornecem exemplos de como criar filtros e como os filtros afetam os resultados das fórmulas. Para obter mais informações, veja Filter Data in DAX Formulas (Filtrar Dados em Fórmulas DAX).
A função FILTER permite-lhe especificar critérios de filtro através de uma expressão, enquanto as outras funções são concebidas especificamente para filtrar valores em branco.
Remover filtros seletivamente para criar uma relação dinâmica
Ao criar filtros dinâmicos em fórmulas, pode responder facilmente a perguntas como:
-
Qual foi a contribuição das vendas do produto atual para o total de vendas do ano?
-
Quanto contribuiu esta divisão 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 da Tabela Dinâmica, mas pode alterar seletivamente o contexto ao adicionar ou remover filtros. O exemplo no tópico TODOS mostra-lhe como fazê-lo. Para encontrar o rácio de vendas de um revendedor específico sobre as vendas de 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 numa fórmula. Ambos os exemplos explicam como os resultados mudam consoante a estrutura da tabela dinâmica.
Para obter outros exemplos de como calcular rácios e percentagens, veja 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 instruções sobre como criar uma fórmula que referencia um valor de um ciclo externo. A função EARLIER 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, veja Context in DAX Formulas (Contexto em Fórmulas DAX).
Cenários: Trabalhar com Texto e Datas
Esta secção fornece ligações para tópicos de referência 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 de chave por concatenação
Power Pivot não permite chaves compostas; Por conseguinte, se tiver chaves compostas na sua origem de dados, poderá ter de as combinar numa única coluna de chave. O tópico seguinte fornece um exemplo de como criar uma coluna calculada com base numa chave composta.
Compor uma data com base em partes de data extraídas de uma data de texto
Power Pivot utiliza um tipo de dados de data/hora do SQL Server para trabalhar com datas; Por conseguinte, se os dados externos contiverem datas formatadas de forma diferente, por exemplo, se as datas forem escritas num formato de data regional que não seja reconhecido pelo motor de dados Power Pivot ou se os seus dados utilizarem chaves de substituição 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 representação de data/hora válida.
Por exemplo, se tiver uma coluna de datas que foram representadas como um número inteiro e depois importadas como uma cadeia de texto, pode converter a cadeia num valor de data/hora com 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 um formato de data ou número personalizado
Se os seus dados contiverem datas ou números que não estão 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.
Alterar tipos de dados com uma fórmula
Em 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 por Power Pivot. No entanto, você pode usar as conversões implícitas de tipo de dados executadas por Power Pivot para manipular o tipo de dados de saída.
-
Para converter uma data ou uma cadeia de caracteres 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, em seguida, gera o valor inteiro correspondente.
=(TODAY()-3)*1.0
-
Para converter uma data, número ou 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.
="& TODAY()
As seguintes funções também podem ser usadas para garantir que um tipo de dados específico seja 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
Cenário: Valores condicionais e teste para erros
Assim 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 revendedores como Preferenciais ou Valor , dependendo do valor anual das vendas. Funções que testam valores também são úteis para verificar o intervalo ou o tipo de valores, para evitar que erros de dados inesperados quebrando cálculos.
Criar um valor com base em uma condição
Você pode usar condições IF 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:
Testar erros dentro de 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 Power Pivot, toda a coluna será sinalizada com um erro, para que você sempre corrija erros de fórmula que resultem em valores inválidos.
Por exemplo, se você criar uma fórmula que se 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ê está desenvolvendo seu modelo de dados, é melhor permitir que os erros apareçam para que você possa clicar na mensagem e solucionar problemas do problema. No entanto, ao publicar pastas de trabalho, você deve incorporar o tratamento de erros para evitar que valores inesperados façam com que os cálculos falhem.
Para evitar erros de retorno em uma coluna calculada, você usa 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 temporal
As funções de inteligência temporal DAX incluem funções para ajudá-lo a recuperar datas ou intervalos de datas de seus dados. Em seguida, você pode usar essas datas ou intervalos de datas para calcular valores em períodos semelhantes. As funções de inteligência temporal também incluem funções que funcionam com intervalos de data padrão, para permitir que você compare valores entre meses, anos ou trimestres. Você também pode criar uma fórmula que compara valores para a primeira e última data de um período especificado.
Para obter uma lista de funções de inteligência de todos os tempos, consulte DAX (Time Intelligence Functions). Para obter dicas sobre como usar datas e horários efetivamente em uma análise de 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 abertura. Os exemplos permitem criar saldos em execução em diferentes intervalos, como dias, meses, trimestres ou anos.
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 suportados pelo DAX são meses, trimestres e anos.
Calcular um valor em um intervalo de datas personalizado
Confira 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 temporal 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 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 de contabilidade padrão, como meses, trimestres ou anos, recomendamos que você execute cálculos usando as funções de inteligência temporal projetadas para essa finalidade, 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 no Excel para criar um filtro Top. Você também pode selecionar um número de valores superior ou inferior em uma Tabela Dinâmica. A primeira parte desta seção descreve como filtrar para os 10 principais itens em uma Tabela Dinâmica. Para obter mais informações, confira a documentação do Excel.
-
Você pode criar uma fórmula que classifica dinamicamente valores e, em seguida, filtrar pelos valores de classificação ou usar o valor de classificação como um Segmentador. A segunda parte desta seção descreve como criar essa fórmula e, em seguida, usar essa classificação em um Slicer.
Há vantagens e desvantagens em cada método.
-
O filtro Top do Excel é fácil de usar, mas o filtro é somente 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 com outros valores em uma coluna.
-
A fórmula DAX é mais poderosa; além disso, adicionando o valor de classificação a um Slicer, basta clicar no Slicer para alterar o número de valores superiores 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 principais itens em uma tabela dinâmica
|
Para mostrar os valores superior ou inferior em uma 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.
