Fala, pessoal! Tudo bom?!
Quer aumentar seu arsenal de conhecimento sobre DAX? Quer parar de fazer gambiarras para obter interseções de conjuntos? Darei exemplos reais de aplicação de cada uma dessas funções: CALCULATETABLE, INTERSECT, EXCEPT, TREATAS, IN, e muito mais!
Ah, uma observação importante: O conteúdo de hoje é para quem está no nível intermediário para avançado no Power BI, beleza?! Ou seja, você já precisa saber o que uma função calculate faz, por exemplo. Se precisar relembrar, dá uma olhada nesse material aqui.
Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.
Objetivos
Você lembra dos 7 pilares para construção de projetos de BI ?
O foco do conteúdo de hoje será em Cálculos utilizando medidas DAX.
Muita gente faz gambiarra para conseguir mostrar o valor Total junto dos demais valores (vendas por produto, por exemplo) no mesmo gráfico. Vou te mostrar como fazer isso utilizando algumas funções específicas. Mais que isso, também vou ensinar a como apresentar a categoria “Outros” quando algum filtro for aplicado num visual. Assim, num gráfico de barras, por exemplo, teremos os produtos selecionados, a categoria “Outros” e o Total. Veja um Spoiler do que faremos:
Ah, também criaremos medidas para calcular a quantidade de clientes recorrentes, novos e perdidos! Sua análise de carteira de clientes ficará incrível depois disso!
Teoria
Iniciaremos com uma teoria básica sobre o que faremos com as funções INTERSECT e EXCEPT.
Você lembra da Teoria dos Conjuntos lá da Matemática?! Intersecção, Exceção, etc? Se não lembra, não tem problema, vamos te relembrar!
Tabelas
Temos as seguintes tabelas:
A tabela fDados é a nossa tabela fato e a dCliente é a nossa tabela dimensão. Repare que nem todos os ID’s Cliente da tabela fato encontram-se na tabela dimensão e vice-versa.
Função INTERSECT
Criaremos uma tabela física (via DAX) chamada Intersecção em Modelagem –> Nova tabela:
Intersecção =INTERSECT ( VALUES ( dCliente[ID Cliente] ), VALUES ( fDados[ID Cliente] ) )
Ao colocarmos a coluna ID Cliente dessa nova tabela que criamos – chamada Intersecção num visual, veremos que o resultado será exatamente aqueles 6 ID’s (de um a 6) que falamos:
Perceba que precisei selecionar a opção Não resumir para mostrar todos os ID’s, caso contrário o PBI iria somar os ID’s.
Resumindo, o que fizemos foi representar essa região pintada desses dois conjuntos:
Função EXCEPT
Agora, o que faremos é encontrar os ID’s que existem na tabela dCliente mas não existem na tabela fDados, beleza?
Exceção Dim -> Ft =EXCEPT ( VALUES ( dCliente[ID Cliente] ), VALUES ( fDados[ID Cliente] ) )
Repare que nesse caso a ordem importa. Veja o resultado:
Fazendo o contrário, teremos:
Exceção Ft -> Dim =EXCEPT ( VALUES ( fDados[ID Cliente] ), VALUES ( dCliente[ID Cliente] ) )
E o resultado quando invertemos a ordem dos argumentos na função EXCEPT é esse:
Função CALCULATETABLE
Vamos supor que você precise encontrar o ID dos clientes que compraram dos EUA.
A coluna com País só existe na tabela dCliente e se tentássemos utilizar a função FILTER não conseguiríamos:
Se você tentar replicar essa medida, aparecerá um erro. Isso ocorre porque o segundo argumento da função FILTER precisa estar presente na tabela do primeiro argumento. Repare que não temos uma coluna de País na tabela fato então não nos resta outra opção: vamos utilizar a CALCULATETABLE.
Essa função vai nos permitir filtrar uma tabela com base numa dimensão. Porém, antes de aplicar essa função no lugar da FILTER, vamos precisar ativar os relacionamentos entre as tabelas (até agora não fizemos isso):
Agora sim, poderemos aplicar a função CALCULATETABLE, veja:
ID Clientes que Compraram (EUA) =CALCULATETABLE ( VALUES ( fDados[ID Cliente] ), dCliente[País] = “EUA” )
Observe na terceira tabela o resultado com apenas os ID’s encontrados que correspondem ao filtro “EUA”. Como estamos filtrando a tabela fato não faz sentido que o ID igual a 9 (em cinza) apareça no resultado, certo? Veja:
Dica:
A função CALCULATETABLE é uma função que modifica contexto (filtra) tabelas.
– O primeiro argumento é uma tabela
– Do segundo argumento em diante são os filtros a serem aplicados
Bom, vamos para os exemplos usando uma base completa.
Cases sobre Clientes
Evento duplo
Nosso objetivo será encontrar a quantidade de clientes que compraram dois produtos específicos – trata-se de trabalhar com um evento duplo.
Nosso modelo é aquele clássico – Star schema com tabelas Dimensões para cliente, produto e data e uma tabela fato com as informações das vendas efetuadas. Nossa base de dados possui dados de Janeiro de 2017 a Abril de 2019. Veja o modelo com os relacionamentos:
Nós temos também a quantidade de clientes que compraram cada produto e colocamos esses valores numa tabela utilizando a função DISTINCTCOUNT( ) – numa medida chamada Clientes.
A seguir teremos então: um Cartão com essa medida Clientes, uma tabela com o Nome do produto e Clientes e ao lado, outra tabela com o ID Cliente (da tabela dCliente)
Nosso primeiro desafio será identificar quantos (e quais) clientes compraram determinado produto – o Produto 66.
Vamos fazer isso usando a função CALCULATE:
Clientes Prod 66 =VAR vClientesProd66 =
CALCULATE ( [Clientes], dProduto[ID Produto] = 66 )
RETURN
vClientesProd66
Beleza! Conseguimos, veja:
A coisa complica quando adicionamos mais um evento: também quero saber quantos (e quais) clientes compraram o Produto 8. Vamos tentar duas opções:
Usando “&&”:
VAR vClientesProd66_8 =
CALCULATE ( [Clientes], dProduto[ID Produto] = 66 && dProduto[ID Produto] = 8 )
RETURN
vClientesProd66_8
Usando “||”:
Nao_da_certo Prod 66 e 8 (E) =VAR vClientesProd66_8 =
CALCULATE ( [Clientes], dProduto[ID Produto] = 66 || dProduto[ID Produto] = 8 )
RETURN
vClientesProd66_8
O resultado ficou assim:
Você pode até tentar usar o && e || para adicionar outra condição no filtro mas não conseguirá obter o resultado correto. Vamos entender o porquê disso? O que não recomendo é:
Bom, vamos à explicação!
Repare que não tivemos valores quando utilizamos “&&” (equivalente ao “E”) porque não é possível um produto ser uma coisa e outra ao mesmo tempo, certo?! Parece óbvio, mas dá pra se confundir com esses operadores lógicos às vezes…
Quando usamos o “||” (equivalente ao “OU”), tivemos um valor total maior que aquele que encontramos ao filtrar apenas o Produto 66. Basta observar os valores totais na última linha da tabela: 190 > 188. Ou seja, nesse caso o que obtivemos então foi a União (soma) dos ID’s dos clientes que compraram o Produto 66 ou Produto 8. Então, essas duas alternativas (E / OU no filtro da Calculate) não servem para o que queremos (a interseção).
Vamos repetir o que fizemos para o Produto 66 só que para o Produto 8:
Clientes Prod 8 =VAR vClientesProd8 =
CALCULATE ( [Clientes], dProduto[ID Produto] = 8 )
RETURN
vClientesProd8
Bom, agora sim vamos utilizar aquelas funções você aprendeu, a INTERSECT e CALCULATETABLE:
Clientes Prod 66 e 8 =VAR vClientesProd66 =
CALCULATETABLE ( VALUES ( fVendas[ID Cliente] ), dProduto[ID Produto] = 66 )
VAR vClientesProd8 =
CALCULATETABLE ( VALUES ( fVendas[ID Cliente] ), dProduto[ID Produto] = 8 )
VAR vClientesAmbos =
INTERSECT ( vClientesProd66, vClientesProd8 )
RETURN
COUNTROWS ( vClientesAmbos )
Perfeito agora, né?! Observe a tabela como ficou:
Vamos ao nosso próximo desafio!
Recorrência
Vamos supor que você precise calcular quantos e quais clientes compraram no mês atual e anterior, ou seja, quer obter os clientes recorrentes. Você também precisa saber quantos clientes deixaram de comprar em cada mês.
Clientes Recorrentes
Nossa primeira medida será Clientes Recorrentes 1M e para construí-la você precisará criar algumas variáveis :
Clientes Recorrentes 1M =VAR vClientesAtuais =
VALUES ( fVendas[ID Cliente] )
VAR vClientesPM =
CALCULATETABLE (
VALUES ( fVendas[ID Cliente] ),
PREVIOUSMONTH ( dCalendario[Data] )
)
VAR vClientesRecorrentes =
INTERSECT ( vClientesAtuais, vClientesPM )
RETURN
COUNTROWS ( vClientesRecorrentes )
Criamos a variável vClientesAtuais para obter a tabela de clientes atuais e isso é obtido utilizando a função VALUES da coluna ID Cliente da tabela fato.
Também criamos a varável vClientesPM (PM de “Previous Month” – mês passado) para encontrar os clientes que compraram no mês anterior. Repare que o segundo argumento da CALCULATETABLE é a função PREVIOUSMONTH para ‘retroagir’ um mês na tabela dCalendario.
Clientes Perdidos
Para encontrar os clientes perdidos faremos a mesma coisa. Copiaremos a medida Clientes Recorrentes 1M e renomearemos o nome para Clientes Perdidos 1M e faremos algumas pequenas mudanças:
Clientes Perdidos 1M =VAR vClientesAtuais =
VALUES ( fVendas[ID Cliente] )
VAR vClientesPM =
CALCULATETABLE (
VALUES ( fVendas[ID Cliente] ),
PREVIOUSMONTH ( dCalendario[Data] )
)
VAR vClientesRecorrentes =
EXCEPT ( vClientesPM, vClientesAtuais )
RETURN
COUNTROWS ( vClientesRecorrentes )
Repare que para obter os clientes perdidos tivemos que trocar a função INTERSECT para EXCEPT e também inverter a ordem dos argumentos. Vamos ver como ficaram essas duas medidas na tabela?
O primeiro valor da coluna Clientes Perdidos 1M (valor 29) representa a quantidade de clientes que compraram em Jan/2017 mas não compraram em fev/2017. Repare na figura acima que destacamos como fazer a ‘prova real’ para conferir se os valores estão corretos.
Bom, fechou né?! Agora você já pode criar várias métricas para analisar sua carteira de clientes!
Você pode estar se perguntando:
Será nosso próximo desafio!
Clientes novos
Nosso objetivo será encontrar, por exemplo, no mês de Março, quantos clientes compraram apenas nesse mês, ou seja, que não compraram em Janeiro nem em Fevereiro – no ano de 2017. Lembra que nossos dados começam em 2017?
A medida se chamará Clientes Novos e ficará assim:
Clientes Novos =VAR vDataInicial =
MIN ( dCalendario[Data] )
VAR vClientesAtuais =
VALUES ( fVendas[ID Cliente] )
VAR vClientesAntigos =
CALCULATETABLE (
VALUES ( fVendas[ID Cliente] ),
FILTER ( ALL ( dCalendario ), dCalendario[Data] < vDataInicial )
)
VAR vClientesNovos =
EXCEPT ( vClientesAtuais, vClientesAntigos )
RETURN
COUNTROWS ( vClientesNovos )
Em resumo o que fizemos foi: tirar dos clientes atuais aqueles que são antigos (por isso usamos o EXCEPT).
Vamos explicar um pouquinho qual o raciocínio por trás da variável vClientesAnteriores.
Para um cliente ser considerado “novo” ele necessariamente não pode ter comprado em nenhum outro período da nossa base de dados, certo?! Então, para obter o número de clientes anteriores precisamos olhar para tudo que vem antes da data do contexto atual.
Primeiro precisamos ‘enxergar’ todas as datas da dCalendário. E como a gente consegue isso?! Usando a função ALL que remove o contexto atual, certo?! Por isso que a inserimos no primeiro argumento da função FILTER, na coluna Data da dCalendario. Entendeu?!
Depois, precisamos fazer com que a gente só veja as datas anteriores ao início do contexto. Ou seja, se queremos ver apenas clientes que compraram no passado – antes de Mar/2017, precisamos remover esse mês (e as datas futuras) dessa conta!
O contexto atual é dado por dCalendario[Data], certo?! E o início do contexto é dado pelo que?! Pelo mínimo desse valor! Então usamos o MIN na variável dDataInicial por isso! Assim conseguiremos garantir, linha a linha, que estamos contando apenas clientes que compraram antes de Mar/17 ao usarmos o operador “<“.
Dali pra frente é moleza! Já sabemos qual o conjunto dos clientes atuais e anteriores, agora só precisamos extrair do conjunto Clientes Atuais aqueles que são “Antigos” usando a função EXCEPT com os argumentos nessa ordem! Pensa assim: hoje tenho 5 blusas, 3 delas foram compradas antes desse mês, o que restará são as blusas que comprei esse mês. Isto é, 5-3=2 novas !
Veja como ficou a medida Clientes Novos na nossa tabela:
Case sobre Produtos Selecionados
Outros
Nossa última tarefa será criar uma categoria “Outros” para produtos não selecionados no filtro (segmentador de dados) e inserir no gráfico de barras um Totalizador.
Temos já pronto a medida Total Vendas:
Total Vendas =SUM ( fVendas[Valor Venda] )
Precisamos criar uma nova tabela via DAX:
dProdutoAux =UNION (
VALUES ( dProduto[Nome Produto] ),
ROW ( “Nome Produto”, “Outros” ),
ROW ( “Nome Produto”, “Total” )
)
Essa tabela criada possuirá uma coluna com todos os produtos e mais duas linhas (Total e Outros). Será ela que utilizaremos no Eixo do gráfico de barras.
Criaremos também uma medida chamada Total Vendas Outros para obter todos os valores referentes aos produtos não selecionados.
Vamos por partes e no final mostrarei a fórmula completa, ok?! A primeira parte:
Essa parte representa a mesma ideia que fizemos no exemplo anterior, usando EXCEPT: de todos os produtos que tiveram vendas (uso do ALL na tabela de Produto), removeremos todos os produtos selecionados e o que restará são os “OUTROS” produtos.
A segunda parte será obter o valor do Total Vendas dos produtos selecionados e para isso precisaremos de uma variável chamada vProdutosEixo:
Veja que precisamos mostrar no gráfico apenas os produtos selecionados. Você lembra que criamos uma tabela auxiliar chamada dProdutosAux ? Precisamos fazer uma intersecção dos valores (nomes) dessa tabela com os produtos selecionados. E como a gente faz isso?! Usando a função INTERSECT! Essa função vai fazer um “Relacionamento virtual” entre as tabelas.
Depois disso, calculamos o valor de vendas dos produtos selecionados (vTotalSelecionados). Repare que no filtro da CALCULATE inserimos a variável vProdutosEixo criada anteriormente!
Ao aplicar o Return na vTotalSelecionados, conseguimos ver que funcionou:
Mas cadê a barra com “Outros”? Vamos lá!
Criando a variável vTotalOutros…
Perceba agora que o filtro da CALCULATE é vProdutosNaoSelecionados que criamos lá em cima (veja a parte 1).
Precisamos indicar para o PBI qual valor utilizar para cada item do eixo então precisaremos do SELECTEDVALUE para indicar o contexto e o SWITCH para escolher qual variável usar. Se o meu contexto (valor do eixo X) for Outros, preciso usar a medida vTotalOutros, caso contrário usaremos a vTotalSelecionados.
No gráfico ficou assim:
Total
Por último, calcularemos o Total de Vendas Fixo numa medida separada:
Total Vendas Fixo =CALCULATE ( [Total Vendas], ALL ( dProduto ) )
O uso do ALL você já sabe: foi para qualquer filtro que esteja aplicado.
Tudo tranquilo por aí?!
Voltaremos na nossa medida Total Vendas Outros para adicionar mais um par de argumentos na função SWITCH:
Prontinho! Agora ficou perfeito, veja:
Como prometido, segue a fórmula completa da variável Total Vendas Outros:
Total Vendas Outros =VAR vProdutosSelecionados =
VALUES ( dProduto[Nome Produto] )
VAR vProdutosNaoSelecionados =
EXCEPT ( ALL ( dProduto[Nome Produto] ), vProdutosSelecionados )
VAR vProdutosEixo =
INTERSECT ( vProdutosSelecionados, VALUES ( dProdutoAux[Nome Produto] ) )
VAR vTotalSelecionados =
CALCULATE ( [Total Vendas], vProdutosEixo )
VAR vTotalOutros =
CALCULATE ( [Total Vendas], vProdutosNaoSelecionados )
RETURN
VAR vTotalOutros =
CALCULATE ( [Total Vendas], vProdutosNaoSelecionados )
VAR vEixoContexto =
SELECTEDVALUE ( dProdutoAux[Nome Produto] )
RETURN
SWITCH (
vEixoContexto,
“Outros”, vTotalOutros,
“Total”, [Total Vendas Fixo],
vTotalSelecionados
)
A única mudança que fizemos foi deixar o SELECTEDVALUE numa variável separada chamada vEixoContexto, ok?!
Viu?! Com um pouco de esforço e conhecimento intermediário em DAX conseguimos resolver nosso desafio sem gambiarras!
Relacionamentos físicos e virtuais
Observe que já existe um relacionamento físico entre dCliente e fVendas:
Vamos supor que por algum motivo você precise deixar esse relacionamento desativado.
Se tivermos uma tabela com o ID Cliente e Total Vendas, o que vai acontecer com ela quando desativarmos esse relacionamento da figura acima?
Problema: O valor Total Vendas ficará constante para cada linha da tabela.
Como faremos para deixar o valor correto na tabela?!
Solução 1: Usar o USERELATIONSHIP!
Solução 2: Usar o IN!
Solução 3: Usar o INTERSECT!
Solução 4: Usar o TREATAS!
Aplicação do USERELATIONSHIP
A solução 1 então seria criar essa medida:
Total Vendas USERELATIONSHIP =CALCULATE (
[Total Vendas],
USERELATIONSHIP ( dCliente[ID Cliente], fVendas[ID Cliente] )
)
Então a tabela apresentaria corretamente o valor das vendas:
E a pergunta que fica é:
Esse relacionamento é FÍSICO ou VIRTUAL?
É físico!
É físico porque existe um relacionamento entre a dCliente e a fVendas mas ele está inativo. Quando usamos o USERELATIONSHIP nós ‘ativamos’ esse relacionamento temporariamente para uso em algum medida.
Mas e se você não puder deixar ter um relacionamento físico (ativo ou inativo)? Nesse caso utilizaremos poderemos utilizar qualquer uma das demais soluções – 2, 3 e 4. Começaremos com a solução 2: uso do IN!
Aplicação do IN
Criaremos uma medida chamada Total Vendas Relac. Virtual e iremos modificá-la de acordo com a solução a ser mostrada, ok?!
Utilizando IN:
Total Vendas Relac. Virtual =VAR vClientes =
VALUES ( dClienteRLS[ID Cliente] )
VAR vClientesFiltradosIN =
FILTER ( VALUES ( fVendas[ID Cliente] ), fVendas[ID Cliente] IN vClientes )
RETURN
CALCULATE ( [Total Vendas], vClientesFiltradosIN )
Resultado com o uso do IN:
Aplicação do INTERSECT
Já para a solução 3 (uso do INTERSECT), teremos:
Total Vendas Relac. Virtual =VAR vClientes =
VALUES ( dClienteRLS[ID Cliente] )
VAR vClientesFiltradosINTERSECT =
INTERSECT ( VALUES ( fVendas[ID Cliente] ), vClientes )
RETURN
CALCULATE ( [Total Vendas], vClientesFiltradosINTERSECT )
Você pode ler essa fórmula assim: Os clientes da tabela dimensão (vClientes) estão filtrando a tabela fato. O resultado na tabela será exatamente o mesmo em todas as soluções , ok?!
Aplicação do TREATAS
A última solução (4) será através do TREATAS. A fórmula ficou um pouco diferente:
Total Vendas Relac. Virtual =VAR vClientes =
VALUES ( dClienteRLS[ID Cliente] )
VAR vClientesFiltradosTREATAS =
TREATAS ( vClientes, fVendas[ID Cliente] )
RETURN
CALCULATE ( [Total Vendas], vClientesFiltradosTREATAS )
O primeiro argumento do TREATAS será a tabela que fará o filtro e depois a coluna que será filtrada.
Das 4 opções se você precisar escolher a mais performática sempre escolha o relacionamento físico (uso do USERELATIONSHIP). É a opção que retornará os valores mais rápido em grandes bases!
Agora se não tiver como, no seu modelo, fazer isso fisicamente, a ordem de performance (da melhor para pior) para relacionamentos virtuais é: TREATAS >> INTERSECT >> IN.
Ufa, agora acabou!
Fizemos bastante coisa hoje, hein?!
Espero que tenha gostado do conteúdo! Sempre que precisar ele estará disponível aqui para consultá-lo, beleza?!
Um abraço,
Leonardo.
