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 testes de erros
-
Utilizar a inteligência de tempo
-
Classificar e comparar valores
Neste artigo
Introdução
Visite o Wiki do Centro de Recursos DAX, onde pode encontrar todo o tipo de informações sobre DAX, incluindo blogues, exemplos, páginas brancas e vídeos fornecidos por profissionais líderes da indústria e a Microsoft.
Cenários: Efetuar Cálculos Complexos
As fórmulas do DAX podem efetuar cálculos complexos que envolvam agregações personalizadas, filtragem e a utilização de valores condicionais. Esta secção fornece exemplos de como começar com cálculos personalizados.
Criar cálculos personalizados para uma Tabela Dinâmica
AS funções CALCULATE e CALCULATETABLE são funções poderosas e flexíveis que são úteis para definir campos calculados. Estas funções permite-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 seguintes tópicos para exemplos.
Aplicar um filtro a uma fórmula
Na maioria dos locais onde uma função do DAX utiliza uma tabela como argumento, normalmente pode passar numa tabela filtrada, quer ao utilizar a função FILTER em vez do nome da tabela ou ao especificar uma expressão de filtro como um dos argumentos da 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, consulte Filtrar Dados em Fórmulas DAX.
A função FILTRAR permite-lhe especificar critérios de filtragem utilizando uma expressão, enquanto as outras funções foram concebidas especificamente para filtrar valores em branco.
Remover filtros seletivamente para criar uma proporção dinâmica
Ao criar filtros dinâmicos em fórmulas, pode responder facilmente a perguntas como as seguintes:
-
Qual foi o contributo das vendas do produto atual para o total de vendas do ano?
-
Quanto contribuiu esta divisão para o total dos lucros de todos os anos operativos, 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 ALL mostra-lhe como fazê-lo. Para determinar a proporção das vendas de um revendedor específico em relação às vendas de todos os revendedores, crie uma medida que calcula o valor do contexto atual dividido pelo valor do contexto ALL.
O tópico ALLEXCEPT fornece um exemplo de como limpar filtros seletivamente numa fórmula. Ambos os exemplos mostram como os resultados mudam consoante a estrutura da Tabela Dinâmica.
Para outros exemplos de como calcular proporções e percentagens, consulte os seguintes tópicos:
Utilizar um valor de um ciclo outer
Para 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 faça referência a um valor de um ciclo outer. A função ANTERIOR suporta até dois níveis de ciclos aninhados.
Para saber mais sobre o contexto de linha e tabelas relacionadas e sobre como utilizar este conceito em fórmulas, consulte o trm! 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 envolvendo 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
Power Pivot não permite chaves compostas; assim sendo, 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 SQL Server data/hora para trabalhar com datas; portanto, se os seus dados externos contiver datas formatadas de forma diferente , por exemplo, se as datas estiverem 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 da 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 inteiro e, em seguida, importadas como uma cadeia de texto, pode converter a cadeia num valor de data/hora utilizando a seguinte fórmula:
=DATA(DIREITA([Valor1],4),ESQUERDA([Valor1],2),SETA.SETA([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 contiver datas ou números que não estão representados num dos formatos de texto Windows padrão, pode definir um formato personalizado para garantir que os valores são tratados 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 disponíveis para trabalhar com datas e números.
Alterar tipos de dados através de uma fórmula
Em Power Pivot, o tipo de dados do resultado é determinado pelas colunas de origem e não pode especificar explicitamente o tipo de dados do resultado, porque o tipo de dados ideal é determinado pela 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 cadeia numémica num número, multipleta por 1,0. Por exemplo, a fórmula seguinte calcula a data atual menos três 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
-
Converter números reais, números inteiros ou datas em cadeias
-
Converter cadeias em números reais ou datas
Cenário: Valores Condicionais e Teste para Erros
Tal Excel, o DAX tem funções que lhe permite testar valores nos dados e devolver um valor diferente com base numa condição. Por exemplo, pode criar uma coluna calculada que rotula os revendedores como Preferencial 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 erros de dados inesperados de quebras de cálculo.
Criar um valor com base numa condição
Pode utilizar condições SE 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 numa fórmula
Ao contrário Excel, não pode ter valores válidos numa linha de uma coluna calculada e valores inválidos noutra linha. Isto é, se houver um erro em qualquer parte de uma coluna Power Pivot, toda a coluna é sinalizado com um erro, pelo que tem de corrigir sempre 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 do infinito ou um erro. Algumas fórmulas também irão falhar se a função encontrar um valor em branco quando esperar 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 manuseamento de erros para impedir que valores inesperados falhem os 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 seguintes tópicos fornecem alguns exemplos simples de como o fazer no DAX:
Cenários: Utilizar a Inteligência de Tempo
As funções de análise de tempo do DAX incluem funções que o ajudam a obter datas ou intervalos de datas a partir dos seus dados. Em seguida, pode utilizar 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 funcionam com intervalos de data padrão, para permitir comparar valores em meses, anos ou trimestres. Também pode criar uma fórmula que compare os valores da primeira e última data de um período especificado.
Para uma lista de todas as funções de inteligência de tempo, consulte Funções de Time Intelligence (DAX). Para sugestões sobre como utilizar datas e horas de forma eficaz numa análise de Power Pivot, consulte Datas no Power Pivot.
Calcular vendas cumulativas
Os tópicos seguintes contêm exemplos de como calcular saldos de fecho e abertura. Os exemplos permite-lhe criar saldos correntes em intervalos diferentes, como dias, meses, trimestres ou anos.
Comparar valores ao longo do tempo
Os tópicos seguintes contêm exemplos de como comparar somas em períodos de tempo diferentes. Os períodos de tempo predefinidos suportados pelo DAX são meses, trimestres e anos.
Calcular um valor num intervalo de datas personalizado
Consulte os seguintes tópicos para 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 informações 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 seguinte tópico para saber 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 utilizando as funções de controlo de tempo concebidas para esta finalidade, como TOTALQTD, TOTALMTD, TOTALQTD, etc.
Cenários: Classificação e Comparação de Valores
Para mostrar apenas o n número superior de itens numa coluna ou tabela dinâmica, tem várias opções:
-
Pode utilizar as funcionalidades do Excel 2010 para criar um Filtro superior. Também pode selecionar vários valores mais altos ou mais baixos numa Tabela Dinâmica. A primeira parte desta secção descreve como filtrar os primeiros 10 itens numa tabela dinâmica. Para obter mais informações, consulte a Excel documentos.
-
Pode criar uma fórmula que classifica os valores de forma dinâmica e, em seguida, filtrar pelos valores de classificação ou utilizar o valor de classificação como uma Filtragem. A segunda parte desta secção descreve como criar esta fórmula e, em seguida, utilizar essa classificação numa Filtragem.
Existem vantagens e desvantagens para cada método.
-
A Excel Filtro principal é fácil de utilizar, mas o filtro serve apenas para fins de apresentação. Se os dados subjaciais à tabela dinâmica mudarem, 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 do DAX é mais poderosa; além disso, ao adicionar o valor de classificação a uma Filtragem, pode simplesmente clicar na Filtragem para alterar o número de valores mais altos apresentados. No entanto, os cálculos são computacionalmente dispendiosos e este método pode não ser adequado para tabelas com muitas linhas.
Mostrar apenas os dez itens principais numa Tabela Dinâmica
|
Para mostrar os valores mais altos ou mais baixos 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 armazenada numa coluna calculada. Uma vez que as fórmulas do DAX são calculadas de forma dinâmica, pode sempre ter a certeza de que a classificação está correta mesmo que os dados subjacentes sejam alterados. Além disso, uma vez que a fórmula é utilizada numa coluna calculada, pode utilizar a classificação numa Filtragem e, em seguida, selecionar os 5 primeiros, primeiros 10 ou até os 100 valores mais elevados.
