Cenários DAX no Power Pivot

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 realizada. Consulte os tópicos a seguir para ver exemplos.

• CALCULATE

• CALCULATETABLE

Aplicar um filtro a uma fórmula

Na maioria dos locais onde uma função DAX usa uma tabela como argumento, geralmente você pode passar em 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 Filter Data in DAX Formulas.

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.

• FILTER

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

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 taxa 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 do 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.

• ALL

• ALLEXCEPT

Para outros exemplos de como calcular taxas e porcentagens, consulte os seguintes tópicos:

• ISBLANK

• ISERROR

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.

• Função EARLIER

Para saber mais sobre o contexto de linhas e tabelas relacionadas e como usar esse conceito em fórmulas, consulte Context in DAX Formulas.

Criar uma coluna de chave por concatenaçã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 a seguir fornece um exemplo de como criar uma coluna calculada com base em uma chave composta.

• Função CONCATENATE

Compor uma data com base nas partes de data extraídas de uma data de texto

Power Pivot usa um tipo SQL Server data/hora para trabalhar com datas; portanto, se seus dados externos contiver datas formatadas de forma diferente, por exemplo, se suas datas são 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 válida de data/hora.

Por exemplo, se você tiver uma coluna de datas que foram representadas como um inteiro 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))

Os tópicos a seguir fornecem mais informações sobre as funções usadas para extrair e redigir datas.

• Função DATEVALUE

• Função TIMEVALUE

• Função DATE

Definir um formato de data ou número personalizado

Se seus dados contiver datas ou números que não são representados em um dos formatos de texto Windows padrão, 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 em 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 pré-definidos para a função FORMAT

• Formatos de data e hora personalizados para a função FORMAT

Alterar tipos de dados usando 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 Power Pivot manipular o tipo de dados de saída. 

• Para converter uma data ou uma cadeia de caracteres de número 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, o valor inteiro correspondente.

  =(TODAY()-3)*1.0

• Para converter um valor de data, número ou 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 determinado tipo de dados seja retornado:

Converter números reais em inteiros

• Função ROUND

• Função CEILING

• Função FLOOR

  Converter números reais, inteiros ou datas em cadeias de caracteres

• Função FIXED

• Função FORMAT

  Converter cadeias de caracteres em números ou datas reais

• Função VALUE

• Função DATEVALUE

• Função TIMEVALUE

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:

• IF

• Função OR

• Função CONCATENATE

Testar erros em uma fórmula

Ao 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, a coluna inteira será sinalizada com um erro, de modo que você sempre deve corrigir erros de fórmula que resultem em valores inválidos.

Por exemplo, se você criar uma fórmula dividida 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 o problema. No entanto, ao publicar as guias de trabalho, você deve incorporar o tratamento de erros para evitar que os valores inesperados causem falha nos cálculos.

Para evitar o retorno de erros 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:

• Função ISBLANK

• Função IFERROR

Calcular vendas cumulativas

Os tópicos a seguir contêm exemplos de como calcular saldos de fechamento e abertura. Os exemplos permitem que você crie saldos em 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 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.

• Função PREVIOUSMONTH, PREVIOUSQUARTER,Função PREVIOUSYEAR

• Função TOTALMTD, Função TOTALQTD, Função TOTALYTD

• Função PARALLELPERIOD

Calcular um valor em 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.

• Função DATESINPERIOD

• Função DATESBETWEEN

• Função DATEADD

• Função FIRSTDATE

• Função LASTDATE

Se você usar funções de inteligência de tempo 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 agregados personalizados entre períodos de tempo. Consulte o tópico a seguir para ver um exemplo de como fazer isso:

• Função PARALLELPERIOD

  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 de tempo projetadas para essa finalidade, como TOTALQTD, TOTALMTD, TOTALQTD, etc.

Mostrar apenas os dez principais itens em uma tabela dinâmica

Solicitar 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 foram alterados. Além disso, como a fórmula é usada em uma coluna calculada, você pode usar a classificação em uma Slicer e selecionar os 5 primeiros, os 10 primeiros ou até os 100 principais valores.

• Função EARLIER.