Cenários DAX no Power Pivot

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.

• CALCULAR

• CALCULATETABLE

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.

• FILTRAR

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

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.

• TUDO

• ALLEXCEPT

Para obter outros exemplos de como calcular rácios e percentagens, veja os seguintes tópicos:

• ISBLANK

• ISERROR

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.

• Função EARLIER

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).

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.

• Função CONCATENAR

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))

Os tópicos seguintes fornecem mais informações sobre as funções utilizadas para extrair e compor datas.

• Função DATA.VALOR

• Função VALOR.TEMPO

• Função DATA

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.

• Formatos Numéricos Predefinidos 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

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

• Função ROUND

• Função CEILING

• Função FLOOR

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

• Função FIXA

• Função FORMAT

  Converter cadeias de caracteres em números reais ou datas

• 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:

• SE

• Função OR

• Função CONCATENATE

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:

• 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 criar saldos em execução em diferentes intervalos, como dias, meses, trimestres ou anos.

• Função CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER FUNCTION, CLOSINGBALANCEYEAR Function

• 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, PREVIOUSYEAR Function

• Função TOTALMTD, FUNÇÃO TOTALQTD, FUNÇÃO TOTALYTD

• Função PARALLELPERIOD

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.

• Função DATESINPERIOD

• Função DATESBETWEEN

• Função DATEADD

• Função FIRSTDATE

• Função LASTDATE

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:

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

Mostrar apenas os dez principais itens 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.

• Função ANTERIOR.