Avançando nos estudos da linguagem DAX, chegamos à função mais popular e que possui um dos comportamentos mais complexos que existem no PowerBI, sim, estamos falando da CALCULATE E CALCULATETABLE.

O nível de importância delas se justifica pois são as únicas capazes de manipular  a ‘realidade’ dentro do Power BI.

Não, elas são capazes de manipular um contexto de filtro inteiro de acordo com a vontade do usuário, assim sendo, tenha muito cuidado quando for trabalhar com CALCULATE.

Outro aspecto de grande importância e vantagem para a CALCULATE é a capacidade de simplificar algumas expressões DAX que normalmente teríamos códigos maiores do que o normal.

Justificado a importância da função, vamos ao artigo ao qual trago um deep dive completo de uma das mais importantes funções dentro da linguagem DAX.

CALCULATE & CALCULATETABLE

Para começarmos a entender a CALCULATE de fato, precisamos deixar alguns conceitos sedimentados:

  • É uma função tabular
  • Seu parâmetro de filtro não é boolean, ao contrário, é uma tabela.
  • Trabalha com o contexto de filtro.

Estes três conceitos básicos farão toda a diferença quando estivermos desenvolvendo expressões.

O interessante da função é que o seu filtro é computado antes da expressão que gerará os valores numéricos. O filtro processado será utilizado para manipular o contexto de filtro que as medidas exigem.

Como dito no início, a criação de uma expressão utilizando CALCULATE OU CALCULATETABLE é bem simples. 

Exemplo função CALCULATE.

É plenamente possível aplicar uma medida no parâmetro da expressão da função e filtrar os valores dali, utilizei a SUMX apenas para exemplo.

Como havia mencionado no início do post, o filtro não é booleano e devemos considerá-lo uma tabela. Lembre-se que estamos trabalhando com uma table function.

Veja que nesta operação, eu posso trabalhar com as duas formas e ambas estão corretas e mais, sem apresentar nenhuma falha.

Primeiro exemplo de construção:

Aplicando medida na função.

Segundo exemplo de construção:

Utilizando variáveis dentro da CALCULATE.

Resultado final:

Resultado final exibido no gráfico.

Somos plenamente capazes de utilizar uma variável como valor de filtro e obtermos o resultado correto.

Conforme mencionado no início deste artigo, como a função manipula o contexto de filtro sobrescrevendo-o com o seu parâmetro de filtro, não conseguimos manipular dois filtros da mesma coluna de uma tabela.

Veja que no exemplo abaixo, tento filtrar pelas duas marcas: Litware e Proseware e o resultado é vazio.

Erro ao utilizar dois valores da mesma coluna.

Perceba que alterando os parâmetros para duas colunas, a expressão retorna valor correto:

Solução do problema, trocando uma das colunas.

O que ocorre entre o primeiro e o segundo exemplo? Quando adicionamos apenas um parâmetro de filtro para a CALCULATE e jogamos no contexto, ela sobrescreve o existente com o seu valor de parâmetro. Porém, ao adicionarmos outro valor no parâmetro da mesma tabela e coluna, e por esse valor ter sido sobrescrito pelo primeiro, no caso, Litware, ele é vazio.

Deste modo, quando este parâmetro é processado e aplicado ao contexto, ele não tem nenhum valor para ser exibido, pois já foi sobrescrito. Como o Proseware é diferente do Litware e estamos trabalhando com booleans, o resultado acaba sendo o vazio.

E no segundo exemplo, a função aplica a cor vermelha aos produtos da marca Litware. Como somente a categoria de produtos “Home e appliances” corresponde a este requisito, é a única a ser exibida.

Valores iguais da mesma coluna retornam resultado.

É importante destacar que a CALCULATE só age como ALL quando a coluna passada no filtro interno, após a expressão, é igual ao filter context do gráfico que aplicamos nossa medida.

Substituindo por Product Category, veja que o filtro muda. 

CALCULATE se comportando como ALL.

Podemos afirmar com toda certeza que a sobrescrita ocorre apenas em casos especiais do seu uso e que no geral, precisamos usar a função ALL.

Veja esta construção e perceba o que ocorre: 

Diferença de resultados com FILTER e ALL na medida.

Mesmo a construção da expressão contendo a função ALL, não houve aquele comportamento clássico de remoção, uma vez que o contexto é diferente do parâmetro de filtro.

Tome cuidado. Na construção acima, perceba que eu especifiquei tabela e coluna no parâmetro da ALL. Veja o que ocorre, por exemplo, quando removo a coluna deixando apenas a tabela.

Removendo a coluna do parâmetro da função All.

Convém lembrar que a execução de qualquer expressão DAX começa da mais interna para a externa. Quando limito ALL dentro da FILTER pela coluna Brand ela não age na CALCULATE,  o que reduz seu campo de ação.

Desta forma, quando removo a coluna Brand da função ALL e aplico no contexto de filtro do gráfico por categoria de produtos, o velho comportamento volta a aparecer e todo o filtro é removido, sendo aplicado somente o resultado para o produto da marca Contoso.

Adicionei um pequeno ponto neste último exemplo dizendo o seguinte: como houve uma junção de dois filtros, filter context e calculate, o que o PowerBI fez internamente foi um processo de Merge. O que transformou esta união em um único filtro.

CASOS DE USO  E SOLUÇÕES  COM A FUNÇÃO CALCULATE

Depois de entendermos o funcionamento básico da função e como ela se comporta manipulando os contextos de filtro, existem alguns casos onde o uso requer maior atenção e a definição exata do que queremos como resultado da expressão construída.

Um caso muito comum onde este nível de atenção é necessário, acontece quando desejamos que a medida criada retorne um valor em porcentagem.

O processo de criação é simples, mas deve-se prestar atenção nas colunas que iremos utilizar. Veja esta construção e sua aplicação no gráfico. 

Cardinalidade entre filtros na medida com a função ALL.

No primeiro exemplo temos todo o resultado funcionando em ordem e retornando o esperado. 

Como o filter context aplicado é primariamente a cor, e o secundário a classe, ALL na construção da medida age corretamente, entregando a porcentagem por cor. 

Como a “cardinalidade” entre o contexto de filtro e a função ALL é de 1:1, ELA NÃO ATUA no contexto secundário, classe de produtos. Então, temos um resultado por cor e outro por classe.

Agora veja o que ocorre quando aplico mais uma coluna ao mesmo contexto, adicionando mais um filtro a esta tabela.

Aplicando a coluna Class da tabela Product, os resultados são diferentes.

O que ocorreu, afinal? Quando construímos a medida, utilizamos a função ALL para “travar” o valor, removendo o filtro do filter context, utilizando a coluna Color por querer calcular a porcentagem de vendas por cor.

Quando aplicamos outra coluna ao contexto, não há interação da função ALL com essa coluna, no caso aqui, Class. Portanto, o cálculo é alterado, o que nos dá um resultado isolado para Class e outro para Color. Uma primeira solução para o problema seria adicionar as colunas desejadas dentro da função ALL, deste modo.

Solução aplicando as duas colunas na função ALL. Medida mrPctColorClass.

A melhor construção para esta situação, isto é, quando temos uma tabela com diversas colunas e não sabemos quais serão utilizadas no filter context, seria como na imagem abaixo:

Melhor prática quando há mais de uma coluna da mesma tabela na medida.

A mesma aplicação vale quando houver duas tabelas na mesma calculate. Se for trabalhar com porcentagem, utilize ALL nas duas tabelas envolvidas. 

Melhor prática quando há mais de uma tabela na mesma medida.

Desta forma, estamos removendo qualquer contexto de filtro que poderia ser criado, incluindo todas as colunas, evitando o “erro” do primeiro exemplo.

Entenda que não foi um erro do PowerBI ou da criação, é o comportamento normal e esperado devido a construção da medida que utilizamos.

EXTRA – UTILIZANDO TABELA FATO NA SOLUÇÃO DE PORCENTAGEM

Vimos no exemplo anterior que a medida que formos adicionando colunas de diferentes tabelas à expressão precisamos da função ALL. Quando trabalhamos com uma ou duas, não é tão cansativo elaborar o código, mas se precisarmos de mais colunas de outras tabelas? Iríamos adicionar uma lista de tabelas dentro da ALL?

Existe uma forma de contornar esta necessidade utilizando a tabela fato do seu modelo de dados sem precisar especificar as diversas tabelas existentes. Veja no exemplo abaixo 

Solução extra, utilizando o conceito de “Expansão de tabela”.

Como podem perceber dentro do gráfico, nenhuma das duas colunas ou suas tabelas foram envolvidas diretamente na medida criada. O que vimos ali foi como uma propagação dos filtros pelas tabelas relacionadas entre si até chegarem a fato, no caso:

  1. Date → Sales
  2. Product Category → Product Subcategory → Product → Sales

Como elas possuem um relacionamento em cadeia (neste post falo um pouco sobre), o filtro consegue agir nas tabelas “ramificadas”.

IMPORTANTE: Este é um conceito mais avançado em DAX que se chama Expanded Tables. Mostrei o exemplo com o intuito de ajudar e simplificar o trabalho de quem utiliza a linguagem no Power BI.

UTILIZANDO ALL E VALUES – OTIMIZANDO PORCENTAGENS

Quando criamos a medida acima utilizando a tabela fato, descobrimos que não precisamos obrigatoriamente especificar uma tabela, desde que ela tenha algum grau de relacionamento.

Porém, existe uma situação em específico que mesmo utilizando a tabela fato, somos obrigados a especificar outra tabela desejada.

Se atente ao cenário do gráfico abaixo: 

Exemplo da medida acima, utilizando a tabela fato Sales.

Analisando a medida e vendo a porcentagem de vendas da categoria, conseguem perceber que o total da porcentagem é exibido apenas na coluna do “Grand Total”. A princípio, não é um problema, mas e se quiséssemos um total para cada ano e não o acumulado?

Para realizar esse requisito, temos que seguir dois passos simples:

  1. Remover todos os filtros da tabela fato utilizando a função ALL
  2. Recuperar apenas o filtro para colunas de datas; no caso, ano, utilizando a função VALUES.

RELEMBRANDO: a função VALUES é uma table function que age da mesma forma que a função ALL, com as seguintes diferenças: não remove o contexto de filtro e considera blanks nos resultados.

Após esta breve explicação, vamos entender na prática o que ocorre quando utilizamos as duas funções na mesma medida. 

Utilizando as funções VALUES E ALL em tabelas e colunas diferentes. Repare no resultado.

Como podemos perceber nos exemplos acima, conseguimos consolidar o resultado por ano e o resultado geral em apenas um gráfico construindo a medida de forma correta.

Importante: como já foi citado aqui, no início do post, a função CALCULATE é capaz de alterar o contexto de filtro impondo o filtro dela, mas isso só ocorre após os argumentos passados no filtro serem processados pelo engine do PBI.

Como vimos, trabalhar com medidas que calculam porcentagens utilizando CALCULATE não é complexo, mas precisa ser bem específico. Ter cuidado com os argumentos de filtro e com o tipo de resultado que se deseja é a chave para criar medidas corretamente.

CONCLUSÃO

Esse artigo foi uma revisão completa da função CALCULATE dentro do Power BI.

Pela complexidade da função e da sua importância, dividirei esse artigo em dois para não ficar extenso.

A função é capaz de manipular e interagir com o contexto de filtro alterando seu resultado exibido, o que exige muita atenção para não ter problemas na exibição.

Muito cuidado quando for trabalhar com porcentagens e múltiplas colunas, lembre-se que a cardinalidade é de 1:1 entre a coluna que está no parâmetro e a coluna que está no contexto de filtro. Dependendo, avalie o uso da tabela inteira como parâmetro de função, como a ALL.

Por fim, considere o melhor caso para o uso da função VALUES dependendo do tipo de resultado desejado na exibição de porcentagens.

OLÁ!

Se você chegou até aqui e gostou, deixe seu like e compartilhe este artigo com seus amigos! Ajude este blog a crescer.

Deixe seu comentário com sua opinião, dúvida ou sugestão. Vamos engrandecer a comunidade.

Assine a newsletter para não perder nenhum artigo.

SIGA NAS REDES SOCIAIS!

Muito Obrigado! Volte Sempre!