Power BI DAX Mesma medida, resultados diferentes

Hoje apareceu uma dúvida interessante no grupo de Power BI e como já falamos um pouco sobre contexto e transição de contexto nesse post, vamos continuar tratando essas dúvidas pra ir esclarecendo melhor o que a engine do DAX faz e porque esses “problemas” acontecem.

O cara por trás do DAX

Agradecimento especial ao Fred! Sem ele esse post não seria possível! Fred trouxe a dúvida e a resposta e ainda me ajudou a entender toda essa resolução. Obrigado, Fred! Quer saber quem é o Fred? Linkedin dele aqui!

O Problema a ser resolvido

A dúvida foi a seguinte:

Na dCalendario tem uma hierarquia com as colunas ano, mês e dia. Também nessa tabela tenho uma outra coluna "mês/ano".

Quero fazer uma comparação de um mês com o mesmo mês do ano de 2018. Pra isso aplico a função CALCULATE com filtro dCalendario[Ano] = 2018.

Porém, somente visuais em que utilizo a hierarquia ano-mes-dia são filtrados por essa função. Se utilizo a coluna "mês/ano" a fórmula retorna blank.

Veja na imagem abaixo a diferença entre as duas tabelas. Por que o filtro funciona diferente pra "mês/ano"?

Perceba que na tabela da esquerda os valores aparecem, enquanto que na da direita não.

Isso acontece pelo seguinte; antes de mais nada é importante lembrar que cada datapoint, ou seja, cada ponto de cálculo é calculado independentemente, ou seja, cada célula é calculada independentemente e cada uma tem seu próprio contexto de filtro.

Antes de seguirmos, lembrem-se de que quando passamos filtros tipo “dCalendario[Ano] = 2018” o que na verdade o DAX interpreta é “FILTER( ALL(dCalendário[Ano]), dCalendário[Ano] = 2018”, logo a fórmula completa seria:

Sabendo disso, ao calcular o valor para coluna ano 2020 TotalSales o resultado é 10.450, enquanto que nessa mesma linha para TotalSales 2018 o valor é 12.174.

Tabela da Esquerda

O que acontece é que, TotalSales 2018 tem um filtro na coluna dCalendario[Ano] , dessa forma, no contexto de filtro onde existe dCalendario[Ano] sofrerá interferência da função ALL(), e o que ALL() faz? Remove todo e qualquer contexto de filtro no report substituindo pela expressão que ele aplica, no nosso caso, dCalendario[Ano] = 2018, como na imagem abaixo.

Pra ser mais claro, quando TotalSales 2018 estiver sendo executada, ela vai remover o filtro de ano de 2020 e colocar 2018.

Tecnicamente falando, ao executar TotalSales temos esse comportamento:

dCalendario[Ano] = 2020
     && (dCalendario[mesabrev] = "Jan" ||
         dCalendario[mesabrev] = "Fev" ||
         dCalendario[mesabrev] = "Mar" ||
         dCalendario[mesabrev] = "Abr" … etc)

Enquanto que, ao executar TotalSales2018, temos esse:

dCalendario[Ano] = 2018
     && (dCalendario[mesabrev] = "Jan" ||
         dCalendario[mesabrev] = "Fev" ||
         dCalendario[mesabrev] = "Mar" ||
         dCalendario[mesabrev] = "Abr" … etc)

Ou seja, como não há filtro sendo alterado por ALL() ou CALCULATE() os campos de dCalendário[mesabrev] se mantém estáveis, obedecendo a hierarquia recém alterada pela CALCULATE() em TotalSales2018.

Tabela da Direita

Na tabela da direita nós não temos nenhum campo sendo alterado pela CALCULATE(), logo o contexto de filtro permanece imutável. Dessa forma, quando TotalSales2018 é aplicada no contexto da tabela da direita, nenhum dado é retornado, pois não existem dados visíveis naquele contexto.

Qual seria o contexto, exatamente, nessa tabela da direita?

Bem, o contexto …

Valor Total

Vamos explicar primeiro o Total, perceba que o valor total é exibido, 12.174.

O Total aparece porque ali o contexto é simplesmente o valor total da medida, que no caso de TotalSales2018 é toda tabela de Sales, filtrada pelo FILTER() da calculate, que diz dCalendario[Ano] = 2018. Então o DAX vai pegar toda tabela de Sales, vai filtrar = 2018, vai pegar o resultado do SUMX e vai retornar no datapoint referente ao Total. Ou seja, no datapoint de Total não existem filtros externos sendo aplicados (que no caso seriam o slicer 2020 e o eixo da tabela na coluna mes/ano).

Valores nas linhas da tabela

Porém, como cada datapoint é calculado de maneira independente, como já falamos, ao tentar recuperar o valor para o datapoint referente a primeira linha, Jan/2020, o que o DAX vai fazer descrevo logo abaixo, porém é importante entender que, esse acesso de verificação, filtragem e retorno da informação é executado ao mesmo tempo, de forma concomitante, simultaneamente.

• acessar a tabela SALES,
• verificar os filtros externos, no caso dCalendário[Ano] = 2020, porque temos um slicer na tela,
• e também já vai estar filtrada no eixo da tabela por Jan/2020, no caso dCalendario[Mes/Ano] = “Jan/2020”,
• vai então aplicar o filtro de dCalendario[Ano] = 2018
• vai pegar o resultado e aplicar o SUM (resultado da medida TotalSales2018, que é invocada no CALCULATE() )

O resultado da medida TotalSales2018 vai dar blank, porque não existe nenhuma linha na nossa base de dados que possa ser filtrada por Jan/2020 e ano 2018 ao mesmo tempo. Pense numa planilha de excel.

Inicialmente temos toda base de dados, toda tabela:

Daí ao considerarmos os filtros externos, temos Ano = 2020 no slicer que já reduz pra:

Daí o visual da tabela da direita, ao utilizar o campo dCalendario[mes/ano] em seu eixo aplica, automaticamente, um filtro na coluna de mes/ano. Considerando a primeira linha de jan/2020, o que vai acontecer na nossa base de dados é que ela vai ser filtrada para aquele datapoint.

nota: ignorem o valor das imagens do excel, eu mudei depois que já tinha tirado todos prints do power bi, aí ia dar maior trabalhão…

É esse o resultado que está sendo visto nesse momento pelo DAX para as linhas destacadas da tabela da direita:

Mas por que esse contexto se aplica também para coluna onde está TotalSales2018?

Bem, o contexto de filtro inicial é definido pelos elementos externos, e depois aplicados às medidas (não exatamente depois, ocorre em tempo de execução, mas pensa assim pra facilitar por agora), no entanto, usando CALCULATE() podemos alterar o contexto de filtro em vigência nos datapoints desejados.

Lembrando, nosso contexto de filtro atual é: dCalendario[Ano] = 2020 e mais o eixo da tabela.

Portanto, se quisermos mesmo trazer o valor de TotalSales2018 na tabela da direita, o que precisamos fazer é uma medida assim:

Veja o resultado da tabela da direita, usando a medida que criamos:

Por que essa nova medida funciona?

TotalSales2018mes/ano funciona pelo seguinte, quando utilizamos ALL() o contexto de filtro vigente que existe na tabela dCalendário é removido, então o contexto de filtro do slicer que diz ano 2020 é removido e o contexto de filtro imposto pelo eixo mes/ano da tabela também é removido.

Ao mesmo tempo o filtro de VALUES( dCalendario[mes abrev] ) é verificado.
Nesse caso aqui, VALUES() vai retornar uma tabela, de valores únicos dessa coluna mes abrev. Que no caso, pra primeira linha da tabela da direita, vai ser JAN, ou seja, uma tabela de uma linha e uma coluna.

Nesse momento existe um detalhe importante. Por que usamos [mes abrev] e não [mes/ano]?

Usamos mes abrev por ser uma coluna relacionada, mas que funciona como um “coringa”, porque JAN existe para vários anos, enquanto que, se utilizarmos mes/ano vamos, obrigatoriamente, retornar na nossa VALUES() o valor de Jan/2020.

Aí você se pergunta, mas eu não passei ALL() antes? Por que então VALUES() simplesmente não retorna todos VALUES() da dCalendário[mes abrev]?

Respondo… Porque VALUES() não sabe que existe ALL(), porque elas acontecem ao mesmo tempo! Dessa forma, quando VALUES() acontecer, o contexto de filtro do slicer no nosso report e do eixo da tabela ainda existem! Logo, se utilizarmos VALUES() em mes/ano o resultado será sempre Jan/2020, e é por isso que utilizamos o coringa [mes abrev], porque dessa forma vamos ter o resultado como JAN.

Esse resultado, JAN, assim como todos resultados dos outros demais filtros da CALCULATE(), serão armazenados pra depois que todos filtros forem avaliados acontecer a interseção e verificar a tabela resultado e depois aplicar a medida de CALCULATE() sobre essa tabela resultante.

ALL() só está removendo o contexto de filtro para a expressão da própria, porém VALUES() ainda sofre pelo contexto de filtro atual, de forma que, se utilizarmos mes/ano na nossa medida TotalSales2018mes/ano o resultado não daria certo.

Isso acontece porque, como o contexto de filtro ainda está lá, usando VALUES() com mes/ano o valor resultante da VALUE() será Jan/2020 e Jan/2020 é sempre Jan/2020, mesmo que você remova todos filtros da galáxia, Jan/2020 será sempre Jan/2020 e nunca existirá nenhum resultado em 2018 pra nenhum mês de 2018 onde Jan/2020 possa gerar algum resultado. Isso é o que chamamos de interseção vazia. Por esse motivo é que utilizamos [mes abrev] em VALUES(), pois mes abrev, considerando o contexto de filtro atual, irá retornar JAN, e JAN sim existe em todos os anos!

Pense assim, se você usar VALUES() em mes/ano você vai ter como resultado uma tabela de uma coluna e uma linha, que depois será utilizada na interseção, e o resultado dessa tabela será Jan/2020, conforme mostro na parte esquerda da imagem abaixo.
Enquanto que, se você fizer a mesma coisa, mas utilizando [mes abrev] o resultado será Jan.

Perceba na imagem abaixo que, embora o resultado de VALUES() venham do mesmo contexto, em um o resultado é Jan/2020 e na outra é Jan. Portanto lembre-se, todo filtro é uma tabela! Essas tabelas depois serão utilizadas na interseção, como já expliquei acima.

O último argumento, dCalendario[Ano] = 2018, vai criar a mesma coisa, uma tabela de uma coluna e uma linha com o valor 2018. Esse valor depois será avaliado com os valores de todos outros filtro pra gerar a interseção, que vai gerar a tabela resultado, que depois será utilizada pra calcular a medida!

Conclusão

Juntando tudo, o que acontece é que:

• com ALL() em dCalendario as colunas da tabela vão ignorar o contexto de filtro e ficar sem nenhum filtro
• um filtro, que nada mais é do que uma tabela, será criada com o resultado da coluna dCalendario[mes abrev] recebendo JAN
• um filtro, que nada mais é do que uma tabela, será criada com o resultado da coluna dCalendario[ano] recebe 2018

E essa avaliação de contexto de filtro que descrevi acima ocorre pra cada datapoint, pra cada célula, linha e coluna da tabela. Portanto, na nossa primeira linha Jan/2020 na coluna TotalSales2018mes/ano o valor resultante será o de Jan para 2018!

Pronto, vencemos o contexto de filtro do DAX! Obrigado pra você que ficou até aqui!!!