Leonardo Karpinski

Leonardo Karpinski

Mestre do Power BI, criador do Curso Express de Power BI e Curso Completo de Power BI. Formou mais de 16 mil alunos nos últimos anos e participou de projetos em grandes empresas nacionais e multinacionais.

Guia Definitivo: Contextos de Avaliação em DAX e Função CALCULATE

Fala galera!! Tudo bem?

No post de hoje, vou falar sobre contexto de avaliação e a importantíssima função CALCULATE! No final, iremos realizar um case com a situação do cotidiano de analistas/consultores. Então, acompanhando o guia completo você irá saber tudo de:

  • Contexto de Filtro
  • Contexto de Linha
  • Função CALCULATE


Como eu sempre falo, é importante lembrar as fases do nosso processo de BI:
ETL → Modelagem (Relacionamento + DAX) → Relatório → Publicação
O foco hoje para seguirmos com nosso guia será na fase de Modelagem (Relacionamento + DAX).

Importante:
Gostaria que vocês fixassem que mais importante que DAX é entender o contexto de avaliação. Isso é primordial para você saber o que vai apresentar nos seus relatórios e se questionado os valores saber explicá-los!

Contexto de Filtro

Vou explicar o contexto de filtro de forma prática! Para isso, vamos usar um modelo simples no Power BI, com 3 tabelas “dimensão” e 2 tabelas “fato”. No relacionamento entre tabelas vemos que a tabela dimensão dProduto filtra a tabela fato fVendas pelo relacionamento entre elas (coluna ID Produto).

Figura 1: Relacionamento entre as tabelas dProduto e fVendas

A forma prática de entender o comportamento desse relacionamento é pelo visual. Iremos utilizar a medida Total Vendas e a coluna Produtos para criar uma tabela.

Figura 2: Medida Total de Vendas

Medida:
Total Vendas =
SUM ( fVendas[Valor Venda] )

Figura 3: Tabela Produto x Total de Vendas

A fórmula DAX da medida é igual em todas as linhas de Produto, porém ela respeita o filtro de cada linha. O que acontece é que cada uma das linhas (Produto) tem um diferente contexto. Vou adicionar a coluna ID Produto na nossa tabela para facilitar a visualização. Agora, vamos fazer um filtro na tabela fVendas em Dados utilizando o produto 39 de exemplo.

Figura 4: Filtro do ID Produto 39 em Dados

Veja que das 690 linhas originais da tabela temos 27 linhas filtradas (para o item ID 39). Conseguimos ver essa informação na parte de baixo da tela. Infelizmente, ainda não temos o recurso de visualizar a soma, contagem, etc no canto inferior direito (igual no Excel) no ambiente de Dados. Se tivéssemos, teríamos o resultado de R$ 63.694, 10 que é o valor na tabela que criamos com a medida.

Figura 5: Destaque para a soma dos 29 items do IDO Produto 39

O Total, é avaliado independente dos outros valores. No caso, ele calcula como se não houve-se nenhum filtro aplicado. O valor desse total, não é a soma de todas as linhas! O que ele faz, é limpar todos filtros para fazer o cálculo, ou seja, soma o valor da tabela de vendas inteira.

Bom, vamos fixar com mais um exemplo. Iremos utilizar o Ano, a medida de Total de Vendas e a medida de Qtd Clientes Compraram em uma nova tabela.

Figura 6: Medida Qtd Clientes Compararam

Medida:
Qtd Clientes Compraram =
DISTINCTCOUNT ( fVendas[ID Cliente] )

Figura 7: Tabela para análise de Qtd de Clientes

Veja que o valor de total para a quantidade de cliente não é a soma das outras linhas! A contagem distinta é uma medida não aditiva. Temos cliente que compraram em 2017 e também em 2018. Se olharmos o total não podemos contabilizar esses clientes mais de uma vez, ou seja, está correto. Concorda?

Outro exemplo, agora com uma matriz. País em linhas, Ano em colunas e o Total de Vendas em valores.

Figura 8: Tabela para análise de composição de contextos

Me diz, aí. Como o Power BI faz para calcular o valor de R$ 9.718,56 ?? Quantos contextos de filtro temos aplicados?? Se você respondeu 2 (País = “Germany” e Ano = “2019”), está correto! O contexto de filtro é uma composição do conjunto de filtros aplicado ao relatório. No total, temos 16 possibilidades de filtro gerado nessa matriz.

Importante:
O contexto de filtro final é resultante da combinação de cada um dos filtros aplicados.

Contexto de Linha

Vamos ver na prática da mesma maneira que fizemos para contexto de filtro! No nosso exemplo, faremos um ajuste nos valores de venda. Para isso, iremos criar a coluna Valor Ajustado na tabela fVendas.

Figura 9: Medida Valor Ajustado

Medida:
Valor Ajustado = 
fVendas[Valor Venda] * 1,5 

Figura 10: Coluna de valor ajustado adicionado na tabela fVendas

A conta é feita linha a linha na nossa tabela. Para cada linha é feita uma iteração. Vamos também criar uma nova coluna Total Vendas na tabela dCliente

Figura 11: Fórmula para Coluna de Total de Vendas

Total Vendas =
SUM ( fVendas[Valor Venda] )

Figura 12: Coluna de Total de Vendas Vazia

Qual o valor que você imagina que será calculado para cada linha?

Figura 13: Coluna de Total de Vendas Preenchida

Se você falou que seria um valor igual para todos, acertou! É o valor total da tabela de vendas. O que acontece é que a tabela de clientes não está fazendo nenhum filtro. Esse é o contexto de linha! Para conseguirmos aplicar esse filtro, devemos utilizar a função CALCULATE.

Função CALCULATE

É uma das principais (se não a principal) função que utilizamos no Power BI. Ela é capaz de modificar o contexto original (aplicando filtros ou utilizando funções que modificam o contexto). Além disso, pode realizar a transição de um contexto de linha para um contexto de filtro.

Bom, vamos pegar o exemplo da coluna que acabamos de criar. Agora, ao invés de calcularmos direto o valor do Total de Vendas com a função SUM, iremos utilizar também a função CALCULATE:

Figura 14: Fórmula Coluna de Total de Vendas com CALCULATE

Total Vendas =
CALCULATE ( SUM ( fVendas[Valor Venda] ) )

Figura 15: Coluna de Total de Vendas com CALCULATE

Vamos fazer alguns exemplos com a função CALCULATE utilizando em forma de medida. A sintaxe da função é relativamente simples Medida = CALCULATE( [Medida], Filtro 1, Filtro 2, Filtro 3, …, Filtro N ).

Vamos criar a medida Vendas 2018 e utilizar em uma matriz com País e Total Vendas.

Figura 16: Medida Vendas 2018

Medida:
Vendas 2018 = //"Shift + Enter" → para utilizar a identação padronizada
CALCULATE(
    [Total Vendas];
    dCalendario[Ano] = 2018
)

Figura 16: Matriz País, Total de Vendas e Vendas 2018

Aqui temos o contexto de filtro original (por País) e o contexto de linha criado pela CALCULATE na medida (Ano = 2018). O contexto final é a composição dos outros dois (País + Ano = 2018).

Agora outro exemplo, para isso vamos criar a medida Vendas France:

Figura 16: Medida Vendas France

Medida:
Vendas France = 
CALCULATE(
    [Total Vendas];
    dCliente[País] = "France"
)

Se colocarmos a medida na matriz, o que vai acontecer?

Figura 17: Matriz País, total de Vendas, Vendas 2018 e Vendas France

Por que o Power BI faz isso? Porque o filtro do CALCULATE sobrescreveu o filtro de contexto. Isso acontece, porque, por trás do que vemos escrito na medida o Power BI adiciona o ALL que retira os filtros de contexto aplicado. Assim, a mesma medida que escrevemos poderia ser rescrita da seguinte forma:

Figura 18: Medida Vendas France com CALCULATE

Medida:
Vendas France =
CALCULATE(
    [Total Vendas];
    //dCliente[País] = "France"
    FILTER(
        ALL(dCliente[País])
        dCliente[País] = "France"
    )
)

As duas medidas, são exatamente iguais em relação a cálculos. Eu , pessoalmente, acho a primeira forma mais fácil de ler (é mais resumida).

E se eu quisesse que o valor aparecesse somente na linha de França? Para isso, podemos usar a função KEEPFILTERS:

Figura 19: Medida Vendas France com Keepfilters

Medida:
Vendas France =
CALCULATE(
    [Total Vendas];
    KEEPFILTERS( dCliente[País] = "France" )
)

Dessa forma ela não sobrescreve o filtro de contexto. Olhando na linha de Alemanha, por exemplo, iremos ver que não tem nenhum valor. Isso ocorre, pois não existe nenhum total de venda para a intersecção de País = “Germany” e País = “France”.

Figura 20: Matriz com Medida Vendas France Modificada

Outra forma de obtermos o mesmo valor é utilizando a função VALUES. Mas segundo Marco Russo e Alberto Ferrari, para otimizar processamento é recomendado utilizar KEEPFILTERS.

Medida:
Vendas France =
CALCULATE(
    [Total Vendas];
    //KEEPFILTERS( dCliente[País] = "France")
    FILTER(
    VALUES(dCliente[País])
        dCliente[País] = "France"
    )
)

Você deve estar se perguntando quais as diferenças entre as funções VALUES, ALL e ALLSELECTED! Fiz um resumo para vocês, fica assim:


– ALL: retorna uma tabela removendo/ignorando os filtros aplicados na coluna/tabela passado como argumento

– ALLSELECTED: retorna uma tabela removendo os filtros aplicados na coluna/tabela passada como argumento, porém respeito os filtros externos

– VALUES: retorna uma tabela considerando/devolvendo os filtros aplicados na coluna/tabela passado como argumento

Para diferenciar elas de forma prática, vamos fazer um exemplo criando a Qtde de Clientes utilizando VALUES que irá retornar uma tabela de clientes. Após isso, faremos a contagem de linha dessa tabela.

Figura 21: Medida Qtd Clientes VALUES

Medida:
Qtd Clientes VALUES =
VAR vClientes =
    VALUES ( fVendas[ID Cliente] ) // Tabela com os clientes
RETURN
    COUNTROWS ( vClientes )

Isso é exatamente igual a utilizar DISTINCTCOUNT (tanto resultado quanto performance). Agora, se utilizarmos a função ALL teremos:

Figura 22: Medida Qtd Clientes ALL

Medida:
Qtd Clientes ALL =
VAR vClientes =
    ALL ( fVendas[ID Cliente] ) // Tabela com os clientes
RETURN
    COUNTROWS ( vClientes )

Figura 23: Medida Qtd Clientes VALUES

Então, para VALUES respeitamos o contexto de filtro e para ALL não.

Vamos fazer um novo exemplo, para explicar o funcionamneto de ALLSELECTED. Para isso iremos criar uma nova matriz com País e Total Venda, Vendas ALL País e Vendas ALLSELECTED.

Figura 24: Medida Vendas ALL País

Medida:
Vendas ALL País =
CALCULATE(
    [Total Vendas];
    ALL ( dClientes[País])
)

Figura 25: Medida Vendas ALLSELECTED País

Medida:
Vendas ALLSELECTED País =
CALCULATE(
    [Total Vendas];
    ALLSELECTED ( dClientes[País])
)

Figura 26: Resultado sem aplicação de filtro de segmentação

Figura 27: Resultado com aplicação de filtro de segmentação

A diferença entre ALL e ALLSELECTED é que o ALLSELECTED respeita filtros aplicados a outros visuais no relatório.

Vou criar agora a medida Vendas ALL Produto e colocar na nossa matriz. O que você acha que irá acontecer?

Figura 28: Medida Vendas ALL Produto

Medida:
Vendas ALL Produto =
CALCULATE(
    [Total Vendas];
    ALL ( dProduto)
)

Figura 29: Matriz País, Total Vendas, Vendas ALL Produto

Veja que ele respeitou o filtro de país! Isso ocorre porque não falamos para remover os contextos de País, o que formulamos foi para remomer filtros de Produto. Para funcionar corretamente esse tipo de filtro, precisamos escolher corretamente o argumento que iremos filtrar e em qual visual iremos utilizar a medida.

Mais um exemplo para fixarmos bem esse assunto, vamos colocar uma segmentação de Subcategoria e utilizar a medida Vendas ALL Produto

Figura 30: Matriz País, Total Vendas, Vendas ALL Produto e Segmentação por Subcategoria de Produto

Como Subcategoria pertence a tabela dProduto a remoção do filtro feita por ALL funciona nesse caso.

Importante:
Cuidado com os argumentos e visuais utilizados para ALL, ALLSELECTED e VALUES. Teste e veja se o que você pensou está sendo executado de forma correta.

Em outro exemplo, vamos remover o filtro de Produto e considerar o de Subcategoria. Para isso, vamos utilizar o conjunto de ALL com VALUES.

Figura 31: Medida Vendas ALL Produto Alterada

Medida:
Vendas ALL Produto =
CALCULATE(
    [Total Vendas];
    ALL(dProduto);
    VALUES(dProduto[Subcategoria]
)

Figura 32: Matriz com Subcategoria, Total Vendas e Vendas ALL Produto

E se eu retirar a função ALL, o que acontece?

Figura 33: Nova medida sem o ALL

Medida:
Vendas ALL Produto =
CALCULATE(
    [Total Vendas];
    ALL(dProduto);
)

Figura 34: Matriz com a Medida Modificada

Ele vai continuar considerando de Subcategoria. O que é exatamente igual o original.

O filtro seria: (Subcategoria Bluetooth + Produto NT Active Headphone E202 Red) Visual + (Subcategoria Bluetooth) Medida com VALUES

Outro exemplo, vamos calcular o total de vendas para os clientes que compraram mais de R$ 10.000,00. Como fazer? Para isso, devemos definir a granularidade do filtro (função FILTER) em conjunto com a função CALCULATE.

Figura 35: Medida Total Vendas Clientes > 10.0000

Medida:
Total Vendas Clientes > 10.000 =
CALCULATE(
    [Total Vendas];
    FILTER(
        dCliente;
        SUM ( fVendas [Valor Venda]) > 10000
)

Figura 35: Matriz Ano, Total Vendas, Total Vendas Clientes > 10.000

Será que está correto? Os valores estão iguais, então se fizemos de forma correta, espera-se que nenhum cliente tenha comprado menos de R$ 10.000,00. Vamos analisar as tabelas de cliente para conferir:

Figura 36: Verificação dos Valores

Não deu certo! O que aconteceu? Na forma que escrevemos o filtro foi para SUM (fVendas[Valor Venda]). Esse valor é o total do Ano! Para corrigirmos temos que usar um novo CALCULATE dentro da fórmulas, que fica assim:

Figura 37: Medida Total Vendas Clientes > 10.000 Corrigida

Medida:
Total Vendas Clientes > 10.000 =
CALCULATE(
    [Total Vendas];
    FILTER(
        dCliente;
        CALCULATE(SUM ( fVendas [Valor Venda])) > 10000
)

Figura 38: Tabela com Valores Corrigidos

Isso é o mesmo que:

Figura 39: Medidas Total Vendas Clientes > 10.000 com [Total Vendas]

Medida:
Total Vendas Clientes > 10.000 =
CALCULATE(
    [Total Vendas];
    FILTER(
        dCliente;
        [Total de Vendas] > 10000
)

Veja que o segundo termo de [Total Vendas] não está colorido como no primeiro. Isso aconteceu, pois o Power BI está entendendo como a coluna que criamos lá no inicio com o nome Total Vendas. Para darmos sequência, vamos renomear a coluna criada para Vendas Cliente e criar a medida novamente.

Figura 40: Coluna Total Vendas Renomeada para Vendas Cliente

Corrigindo a coluna [Vendas Cliente] para a medida [Total Vendas]:

Figura 41: Medida Corrigida

Isso acontece, pois uma medida sempre possui um CALCULATE por fora dela. Assim, ela sempre realiza a transição do contexto de linha para filtro.

Figura 42: Matriz Ano, Total Vendas e Total Vendas Clientes > 10000 utilizando a Medida [Total Vendas] para filtrar

Porque os valores das somas da linha não batem com o total? Imagine que um cliente comprou R$ 9.000,00 em 2017, o mesmo valor em 2018 e 2019. Nesse caso, ele não entra no somatório em cada um dos anos se avaliarmos separadamente, mas no total que não temos filtro de ano ele comprou 3 x R$ 9.000,00 = R$ 27.000,00 daí então entraria para esses contexto.

E se eu quiser que o total seja a soma das linhas de ano? Tem como fazer? Sim, para isso usamos a função SUMX. Essa fórmula é uma iteradora, e para obtermos o resultado temos que iterar pelo ano.

Figura 43: Medida Total Vendas Clientes >10.000 Ajustado

Medida:
Total Vendas Clientes > 10000 Ajustado =
SUMX(
    VALUES(dCalendario[Ano]);
    [Total Vendas > 10.000]
)

Figura 44: Matriz com a Medida Ajusta para Somar as Linhas

Case

O case que vou fazer com você é um problema do cotidiano de analista/consultor. Imagine que você precisa calcular o tempo médio entre a venda e devolução do produto, mas não possui as datas na mesma tabela. Além disso, precisamos calcular a soma de valor devoluções de produtos por pré vendedor. Porém, não temos a informação de pré vendedor na tabela de devoluções.

O que fazer ?

O primeiro passo é: Não desesperar! Vamos olhar os relacionamento e como estão nossas tabelas em Modelo:

Figura 45: Modelo com os Relacionamentos das Tabelas do Case

Vamos trabalhar primeiro em encontrar quem é o pré vendedor para cada venda. Para isso temos algumas soluções:

– Conseguir na consulta do Sistema de devoluções quem o pré vendedor referente aquela venda. Isso está descartado, pois queremos resolver com as informações que temos em mão;
– No Power Query, mesclar vendas e devoluções pelo número do pedido;
– Em dados, criar uma nova coluna e utilizar LOOKUPVALUE para trazer a informação via DAX;
– Por medidas (relacionamento virtual);
– Tentar fazer o relacionamento entre as tabelas no modelo (fica ambíguo/sentido duplo/tracejado). Esse é o pior cenário, e você deve evitar ao máximo isso!

Dica:
Em testes, o LOOKUPVALUE via DAX se mostrou mais performático do que mesclar as consultas no Power Query! Além disso, um relacionamento “físico” (LOOKUPVALUE e Mesclar) é sempre melhor que um relacionamento feito por medidas (virtual). Eu pessoalmente, só utilizei uma vez o relacionamento virtual nesses tipos de problemas. Nas outra vezes, consegui fazer via relacionamento “físico”.

Aqui, iremos utilizar a opção de criar uma nova coluna cdPreVendedor na tabela de Devolucoes utilizando LOOKUPVALUE :

Figura 46: Criando Nova Coluna para Adicionar a Informação de Pré Vendedor

Figura 47: Fórmula da Nova Coluna

Fórmula:
cdPreVendedor = LOOKUPVALUE( Vendas [cdPrevendedor]; Vendas [numPedido]; devolucoes [numPedidoOriginal])

Figura 48: Resultado da Tabela

Com o relacionamento criado conseguiríamos fazer o cálculo por Pré Vendedor com a medida:

Figura 49: Medida Valor Devolvido

Medida:
Valor Devolvido =
SUM( Devolucoes [Valor] )

Já por medidas, temos as seguintes maneiras:

Figura 50: Medida Valor Devolvido V1

Medida:
Valor Devolvido v1 =
CALCULATE(
    SUM( Devolucoes [Valor] );
    TREATAS(
        VALUES( Vendas[numPedido]);
        Devolucoes [ numPedidoOriginal]
    )
)

Figura 51: Medida Valor Devolvido V2

Medida:
Valor Devolvido v2 =
CALCULATE(
    SUM( Devolucoes [Valor] );
    INTERSECT(
        VALUES( Devolucao[numPedidoOriginal]);
        VALUES( Vendas [numPedido])
    )
)

Figura 52: Medida Valor Devolvido V3

Medida:
Valor Devolvido v3 =
CALCULATE(
    SUM( Devolucoes [Valor] );
    Devolucoes [ numPedidoOriginal] IN VALUES (Vendas[numPedido])
)

Nesse caso, não conseguimos usar a função RELATED ou USERELATIONSHIP. Isso ocorre, pois não criamos um relacionamento entre as tabelas de Devolucoes e Vendas no modelo.

O resultado fica:

Figura 53: Resultado de devoluções por pré vendedor

Como calcular a média da diferença entre datas em tabelas diferentes?

Podemos criar um relacionamento virtual. Conseguimos o resultado com isso, porém estamos resolvendo além do cálculo um problema de modelagem.

Figura 54: Medida Tempo Médio v1

Medida:
Tempo Médio v1 =
AVERAGEX(
    Devolucoes;
    VAR vDataVenda = LOOKUPVALUE ( Vendas [ DataVenda]; Vendas [numPedido]; Devolucoes [ numPedidoOriginal])
    RETURN
    DATEDIFF( vDataVenda; Devolucoes[DataDevolucao]; DAY)
)

E a outra forma, seria semelhante a coluna que criamos para pré vendedor, mas agora será para a DataVenda utilizando LOOKUPVALUE:

Figura 55: Adicionando a coluna de DATAVENDA na tabela Devolucao

Com o relacionamento feito, devemos alterar nossa medida para:

Figura 56: Medida Tempo Médio v1 Alterada

Medida:
Tempo Médio v1 =
AVERAGEX(
   Devolucoes;
   DATEDIFF( vDataVenda; Devolucoes[DataDevolucao]; DAY)
)

O relacionamento que criamos via LOOKUPVALUE é mais performático que a criação do relacionamento virtual, pois é somente uma busca de informação que naturalmente deveria estar ali. Assim, só consome memória quando atualizamos o modelo. No relacionamento virtual, forçamos o processamento dessa relação toda vez que for utilizado.

Figura 57: Modelo com os Relacionamentos das Tabelas do Case após Criação das Colunas

Figura 58: Resultados do Case

Pessoal, acredito que com o guia de hoje mais o case vocês assimilaram muito conteúdo! Estão feras em contexto de filtro, contexto de linha, função CALCULATE e de quebra ainda aprenderam a fazer relacionamentos entre tabelas para solucionar problemas no dia a dia!!
Espero que tenham gostado. Não deixe de entrar em contato, caso tenha alguma dúvida ou sugestão de tema.

Grande abraço,
Leonardo

COMPARTILHE ESSE POST

Compartilhar no facebook
Compartilhar no linkedin
Compartilhar no twitter
Compartilhar no pinterest