Cenários DAX no PowerPivot

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

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 por Power Pivot. No entanto, pode utilizar 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 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

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:

• SE

• Função OR

• Função CONCATENAR

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 Power Pivot, toda a coluna será 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:

• Função ISBLANK

• Função SE.ERRO

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.

• Função DATESINPERIOD

• Função DATESBETWEEN

• Função DATEADD

• Função FIRSTDATE

• Função LASTDATE

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:

• Função PARALLELPERIOD

  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 com as funções de análise de tempo concebidas para esta finalidade, tais como TOTALQTD, TOTALMTD, TOTALQTD, etc.

Mostrar apenas os dez primeiros itens numa Tabela Dinâmica

Ordenar itens dinamicamente com 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 DAX são calculadas dinamicamente, 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 Segmentação de Dados e, em seguida, selecionar os 5 primeiros, os 10 primeiros ou até os 100 valores mais altos.

• Função EARLIER.