DAX – Calculate I

Olá pessoal!

Post com conteúdo novo sobre DAX e, para reiniciar no blog, nada como falar de uma das funções mais importantes do PBI, a função calculate. Dada a importância desta função e seu uso, penso em criar uma área a parte para tratar apenas dela, com tudo que puder trazer à respeito.

Será uma serie de posts bem longa, mas que pretendo cobrir tudo que ela abrange para facilitar e deixar o material o mais completo possível. Dito isso, vamos ao post!!

INTRODUÇÃO

A função calculate é uma das mais importantes dentro da linguagem DAX e embora seja de uma sintaxe simples, seu comportamento é bem complexo.

A função calculate é a única que consegue criar um novo filter context dentro de um já existente e é isso que torna seu comportamento complexo, essa habilidade de manipular múltiplos filtros em um único. Aqui é importante destacar que esse filter context base ao qual me refiro, é aquele que o PBI abre quando criamos medidas.

NOTA: Até a conclusão deste post, a função calculate me pareceu se adequar e trabalhar melhor quando utilizada com medidas.

Vamos supor que seja necessário criar um gráfico com o lucro e a porcentagem que uma determinada marca obteve nas vendas, em cada categoria de produto

Na primeira imagem temos a medida criada para calcular o lucro.

Então, como teríamos muitas medidas criadas dentro do modelo, decidi agrupar os cálculos em variáveis criando uma única expressão para chegarmos na porcentagem das vendas, veja.

Perceba que para uma única medida, há 5 variáveis. Essa situação acaba deixando o código um pouco sujo, principalmente para uma situação tão simples para obter este gráfico abaixo.

Então, para solução desta situação, temos a função calculate que consegue simplificar todo trabalho.

Notem que agora, basta eu aplicar a medida na função, explicitar o filtro que desejo para a função e retornar a variável com o cálculo elaborado.

Veja que a porcentagem é a mesma e o “trabalho” de elaborar a expressão em si, foi bem mais simples.

Como podemos perceber nessa breve introdução, a função calculate simplifica e muito a criação de medidas que poderiam ocupar muito mais tempo do que o normal.

NOTA: a anotação na expressão calculate sobre o tipo boolean é para destacar que diferente do que ocorre com a função filter que trata a igualdade como tal, aqui não é assim. Ela funciona com uma lista de valores, irei mostrar mais a frente.

CALCULATE

Foi dito no início do post que esta função é a única que consegue criar um filter context dentro de outro já existente e retornar um resultado diferente do esperado. Mas, cuidado com este conceito.

Quando a função é executada e cria um novo contexto para o filtro já existente, ela une o valor do novo ao filtro ‘antigo’. Ou seja, ela faz um merge entre os dois filter context criados na sua execução.

Outro aspecto importante e citado é que o argumento de filtro passado para a função não é do tipo boolean, ele é uma tabela e se fosse, seria convertido em tal. Por isso, temos as seguintes situações:

1. Podemos utilizar FILTER dentro de uma calculate, já que esta é uma table function.
2. Se utilizar a função FILTER NÃO utilizar mais de uma expressão booleana em sua igualdade.
3. Embora calculate seja uma função que recebe tabela, para cada novo filter context aberto, somente um filtro é aceito.

Vamos às demonstrações para entender tais regras!

O primeiro exemplo será um bem simples onde adiciono uma medida com a porcentagem de vendas que a marca “Constoso” obteve, filtrada por categoria de produtos. De fato, percebemos que temos dois filter context: Categoria e Marca (dentro da calculate).

O que aconteceu nessa expressão foi que o filtro na função sobrepôs o filtro do gráfico, onde cada uma destas categorias informa apenas a marca desejada. Tanto que se eu adicionar a coluna Brand no gráfico, veja o resultado.

Independente da marca, todos os valores são referentes a marca Contoso. É como se eu estivesse utilizando a seguinte sintaxe na expressão.

mrCalculateWALL =
VAR CALC =
    CALCULATE (
        [mrCalcPCT] -- AQUI TEMOS UMA MEDIDA,
        FILTER (
            ALL ( 'Product'[Brand] ),
            'Product'[Brand] = "CONTOSO"
        )
    )
RETURN
    CALC

Uma prova concreta de que a sintaxe embora diferente, tenha exatamente o mesmo efeito, é adicionar essa medida no gráfico comparando o resultado e desta vez, filtrando apenas por marca. veja.

Como dito, chegamos ao mesmo resultado com expressões um pouco diferentes. Isso se aplica a qualquer medida que tenha calculate & all na mesma função.

Como sabemos, ALL remove todos os filter context que existem e impõe o valor que ele traz ao contexto e aqui não seria diferente. Quando aplicamos, o que aconteceu por debaixo dos panos foi uma sobrescrita de um filtro no outro.

Você pode se perguntar: Por que então no outro gráfico quando adicionamos a coluna Brand, o valor mudou, passando a ser único?

Isso ocorre justamente pela sobrescrita que a função calculate faz no filter context antigo. Se o contexto de filtro do gráfico, como foi o caso acima, for igual ao filtro da função, este sobrescreve.

Lembra quando disse que a calculate trabalha apenas com uma única lista de valores e que se for booleano ela converte e se não, é um valor tabular? Então, é por isso. Como ela só enxerga a marca Contoso, todo o filtro passa a ser esse.

É válido saber que a função possui uma relação de 1×1 para o filter context do gráfico e estes precisam ser iguais, um exemplo disto é este gráfico com dois filtros.

Cada um dos filtros do gráfico foram “anulados” pela calculate e seus filtros internos. Como minha categoria filtrada é “cell phone” e a cor “Black”, este resultado final é condizente a tais.

O que aconteceu:

1. Criei um gráfico de matriz com os dois filtros colunares: ProductCategory e Color.
2. No primeiro filtro da função calculate, a função filter + all sobrescreveram a medida, retornando APENAS a categoria “Cell Phones”; fecho essa iteração.
3. Criei um novo filtro para a medida na calculate e desta vez, para a coluna Color; coluna esta que está no gráfico.
4. Esse novo filtro também exclui o valor de todas as cores – característica da função All; trazendo apenas as vendas da cor preta.
5. Quando jogo essa medida no gráfico, tenho apenas os valores da categoria “cell phones” e da cor “black”, independente dos filtros que estejam no gráfico.

O que ocorreu foi uma sobrescrita dos filtros internos ao externo, limitando a visualização dos valores para os passados como parâmetros das funções.

CALCULATE, ALL E PORCENTAGENS – COMPUTANDO COM AS FUNÇÕES

Na primeira parte deste post vimos como a função age nos filtros e também, quando combinada com filter e all. Entendemos seu mecanismo de substituição e como o resultado é impactado, principalmente quando os filtros são iguais.

Avançando um pouco mais no conteúdo, vamos entender um pouco mais sobre como calculate e all funcionam juntas, e desta vez, sem a função filter. Além, vamos descobrir o por que isso pode afetar cálculos que envolvem porcentagens.

Mas por que iremos abordar cálculos percentuais em si? Pois quando envolve esse tipo de cenário em uma calculate, é imprescindível que tudo esteja devidamente definido.

Observe o seguinte cenário:

Vamos supor que queiramos criar um report simples com as vendas por categorias e a porcentagem que cada uma representa. Utilizamos estas duas medidas básicas: Uma para calcular as vendas e a outra para calcular a porcentagem.

/* VENDAS */

mrCalcSAmount =

SUMX (
    'Sales',
    'Sales'[Net Price] * 'Sales'[Quantity]
)

--------------------------------------------------

/* PORCENTAGEM */ 

mrPctCLCTByCTG =

VAR MRSALESAM = [mrCalcSAmount]
VAR MRALLSALES =
    CALCULATE (
        [mrCalcSAmount],
        ALL ( 'Product Category'[Category] )
    )
VAR PCTCALC =
    DIVIDE (
        MRSALESAM,
        MRALLSALES
    )
RETURN
    PCTCALC

Jogando estas duas medidas no gráfico filtrado por colunas, temos:

Gostaria de chamar atenção que em nenhuma das medidas há qualquer filtro extra, salvo os convencionais que são padrões de suas criações.

Um fato curioso nessa situação é que a medida de porcentagem e a medida de grand total possuem a função ALL, mas somente uma removeu o filtro. Veja a medida e sua aplicação no gráfico.

Inclusive, a mesma medida foi utilizada para porcentagem.

mrCalcuateALLCTG =

VAR CALCAMOUNT =
    CALCULATE (
        [mrCalcSAmount],

        /* FUNÇÃO ALL PARA CALCULATE 
        ENXERGAR TODAS AS CATEGORIAS
        REMOVENDO O FILTER CONTEXT */

        ALL ( 'Product Category' )
    )
RETURN
    CALCAMOUNT

Essa situação ocorre pois todo o contexto criado para a medida de porcentagem foi aplicado na medida mrCalcSAmount. Como foi dito no post sobre contexto(aqui e aqui), quando abrimos um contexto em uma variável e finalizamos atribuindo o valor, é como se ele nascesse e morresse ali.

Como utilizei a variável na operação de divisão para obter a porcentagem, nosso resultado não alterou a exibição dos valores no filter context criado pelo gráfico.

Mas por que então, devemos especificar exatamente o que desejamos quando vamos trabalhar com calculate e porcentagem!?

Veja a seguinte situação quando adiciono mais um filtro ao gráfico, desta vez, adiciono a coluna Class da tabela Product.

Perceba que a porcentagem para a categoria continua intacta, diferente do que ocorre quando expandimos para classe. O mais interessante desta situação é que esse pequeno ‘problema’ só acontece com a medida que tem calculate.

Esta é uma situação bem simples de resolver, basta lembrar do conceito no início do post: “o filtro na calculate age com uma relação de 1-1″. Ou seja, a medida filtrada só consegue agir quando esta diretamente relacionada para o contexto da categoria!!!

Se eu alterar a ordem dos filtros no gráfico, veja o resultado!

Conseguem entender que como o filtro está agindo na categoria, o gráfico está mais assertivo? Somando as porcentagens, temos os 100% dentro da classe Deluxe dividido corretamente entre as categorias.

E para resolver essa situação, basta adicionar o filtro desejado na medida dentro da calculate. Claro, os filtros devem corresponder.

Como alterei apenas a variável que tinha a função, postarei apenas o print.

E por consequência do ajuste da medida, o gráfico está correto.

NOTA: a ordem continua importando. Se na calculate o primeiro filtro for para a tabela ProductCategory e o segundo para Class, no gráfico, eles devem respeitar esta ordem; mesmo após as devidas correções. Façam o teste!

Ao entender essa particularidade da função, é importante esclarecer que este não é um erro da função, sintaxe ou do engine do PBI para processar a função DAX. É como ela foi construída mesmo. É essa a relação que ela possui com o filter context, 1-1.

Vale lembrar que não só de coluna vive a função e que caso queiramos, podemos passar uma tabela inteira que ela produz o relatório. Talvez por isso, ela seja tão importante!

Primeiro, a mudança na variável.

E o resultado no gráfico, vejam que posso adicionar qualquer coluna da tabela Product que não causa problema no dashboard.

RESUMO CALCULATE

Abaixo, um guia do que acontece de fato quando executamos a função calculate:

1. Ela cria uma cópia do atual filter context.
2. Avalia o argumento de filtro e produz uma lista de valores com resultados válidos para a coluna específica.
3. Se houver mais de dois argumentos de filtro afetando a mesma coluna, eles são unidos por um AND ou uma operação de interseção
4. Com essa nova condição, a função verifica se já existe um filtro nesta coluna e daí, passa  a ter duas opções:
   1. Se houver um filtro – sobrescreve o filtro existente utilizando o novo filtro
   2. Se não houver – utiliza o novo filtro criado no filter context
5. Com o novo filter context criado, a função aplica ao modelo e na expressão e a partir disto, temos o resultado.

No geral, a função permite quantos filtros quiser, mas para cada filtro, um AND é adicionado.

EXTRA: CALCULATE E O ROW CONTEXT

Esta parte é apenas um teste que fiz e é bem breve, não irá alterar o post no geral, mas gostaria de mostrar para vocês.

Como sabemos que existe dois tipos de contexto quando estamos trabalhando com DAX, decidi averiguar como a função calculate se sai quando aplicada em uma coluna calculada (que automaticamente cria um row context).

Antes, explico que a primeira variável é uma expressão separada, somente a segunda e terceira que são ‘ligadas’. Excluindo claro, a variável BRND que armazena apenas um valor para filtro da coluna Brand.

ccTesteCalcBrnd =
-- TESTANDO CONTEXTO DE LINHA COM CALCULATE
-- A CONTA NÃO BATEU MUITO COM A MESMA MEDIDA NO CONTEXTO DE FILTRO
-- MESMO JOGANDO ESSA COLUNA EM UM GRÁFICO DE TABELA, OS CÁLCULOS NÃO FUNCIONARAM CORRETAMENTE.
VAR BRND = "CONTOSO"
VAR CALC =
    CALCULATE (
        DIVIDE (
            SUMX (
                RELATEDTABLE ( 'Sales' ),
                Sales[Quantity] * ( 'Sales'[Unit Price] - 'Sales'[Unit Cost] )
            ),
            SUMX ( RELATEDTABLE ( 'Sales' ), 'Sales'[Net Price] * 'Sales'[Quantity] )
        ),
        'Product'[Brand] = BRND
    )
VAR TSTE =
    DIVIDE (
        SUMX (
            RELATEDTABLE ( 'Sales' ),
            Sales[Quantity] * ( 'Sales'[Unit Price] - 'Sales'[Unit Cost] )
        ),
        SUMX ( RELATEDTABLE ( 'Sales' ), 'Sales'[Net Price] * 'Sales'[Quantity] )
    )
VAR TESTE2 =
    CALCULATE ( TSTE, 'Product'[Brand] = BRND )
RETURN
    TESTE2

Este é o gráfico onde aplico a coluna e uma medida criada ‘corretamente’, veja a diferença.

E criando uma medida com a mesma expressão da coluna calculada e, claro, aplicando no gráfico comparando valores, não diferenciou muito.

Mesmo alterando as variáveis de resultado nos dois gráficos, o resultado continua discrepante. Desconfio que seja algum conflito de contexto e isso está causando erro no resultado. Até, se repararmos bem, eu abri um contexto de linha dentro de uma calculate e isso pode ter sido feito de alguma forma errada.

Em todo caso, é correto afirmar que a função funciona de forma diferente para uma coluna calculada, o que já era de se esperar. Continuarei investigando e testando e, quando chegar na solução, trago aqui!

CONCLUSÃO

Este foi um post bem extenso, e explicativo. Quis cobrir todo o conceito base para que envolve esta função e em algumas situações, talvez tenha ficado repetitivo, mas entenda, DAX é uma linguagem cheia de detalhes, e é aqui que o diabo mora.

Tentei abordar todas as situações bases possíveis que vi até agora sobre esta função e posso dizer, ela é bem mais complexa do que se imagina.

Peço que leiam o post com calma e tentem reproduzir e entender o que está acontecendo. Dominar essa função é um bom passo para ter sucesso com DAX, por isso tantos detalhes!

Espero que esse post os ajude, Deus os abençoe!

Saúde!