Fala galera, tudo bem? Hoje o post é referente a Live #1 do Mestre!!
Vou fazer um tutorial completo da linguagem DAX e um desafio no final usando tudo que demonstrei no tutorial. Além disso, o desafio é baseado em um problema real que um dos meus clientes teve.
Então, acompanha até o final para aprender sobre:
- Introdução sobre DAX
- Diferença entre medidas implícitas e explícitas
- Contextos de avaliação
- Função CALCULATE
- Função ALL
- Função ALLSELECTED
- Função VALUES
- Desafio para calcular Churn Médio
Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.
Introdução DAX
Sempre gosto de reforçar com vocês as 4 fases dentro do processo de BI:
- ETL → Extract (extrair), Transform (transformar) e Load (carga)
- Modelagem
- Visualização
- Compartilhamento
A linguagem DAX está inserida dentro da fase de modelagem do processo. Normalmente na ETL utilizamos a linguagem M que é própria do Power Query. Para as visualizações temos o termo “drag and drop” arrastar e soltar que é referente a montagem dos visuais e o compartilhamento relaciona-se principalmente com o Power BI Online/Serviço.
Então, utilizamos a linguagem DAX para criar todos os nosso cálculos do projeto. DAX é uma sigla criada para Data Analysis eXpressions e tem bastante expressões que lembra as do Excel, porém todas as fórmulas são em Inglês.
Ahhh mas você deve estar pensando “Onde que eu posso usar esse tal do DAX!??”. Existem 3 situações onde o DAX aparece:
- Colunas calculadas
- Cálculo de medidas
- Tabelas
Bom, legal!! Mas você pode querer saber também qual é o maior poder do DAX? O maior poder do DAX é para criar medidas, mas vou mostrar exemplo de todos.
Colunas
Vamos fazer um exemplo para criar uma coluna calculada:
Figura 1: Criação de Coluna com DAX
Etapas: Em Dados, selecionar a tabela fVendas → Em "Ferramentas de coluna" clicar em "Nova coluna" Fórmula DAX:Valor Venda $ =
fVendas[Valor Venda] / 4.2
O que acontece quando fazemos isso? Acabamos de aumentar nosso modelo deixando o arquivo maior, afetando o processamento (diminuindo performance). Para exemplificar outro problema de colunas, vamos analisar uma nova coluna criada:
Figura 2: Criação de Coluna de Total de Vendas
Etapas: Em "Dados", selecionar a tabela dProduto → Em "Ferramentas de coluna" clicar em "Nova coluna" → Selecionar a medida [Total Vendas] Fórmula DAX:Total Vendas =
SUM ( fVendas[Valor Venda] )
Com esses valores vamos montar um gráfico de colunas com uma segmentação por data na página:
Figura 3: Criação dos Gráficos para Análise
Etapas: Gráfico de colunas clusterizado: Eixo → Produto, Valores → Valor Vendas (soma) Segmentação de dados: Campo → Ano
Ao mudarmos o intervalo da segmentação dos dados, veja o que acontece com os valores de vendas:
Figura 4: Alteração na segmentação de dados
Nada! Exatamente isso….nada! Isso acontece, pois a coluna criada não é influenciada pela alteração do contexto de data (são estáticas). Ou seja, temos mais um ponto de atenção aqui.
O terceiro problema, que se deve ter muita atenção, é com o nível de agregação das colunas. Alguns exemplos de fórmulas onde pode ocorrer esse problema, são: contagem distintas, média, percentuais.
De forma geral, as medidas são utilizadas nos campos de eixo e de segmentação nos visuais.
Medidas
Existem 2 tipos de medidas as implícitas e explícitas. A implícita funciona quando eu pego uma coluna de valores (numéricos) e utilizo em algum visual. Automaticamente o Power BI faz uma conta (figura abaixo com exemplos) com esse campo quando vai para o visual.
Figura 5: Cartões com o campo de ID Produto
Etapas: Em "Visualizações" clicar em "Cartão" → Para todos os cartões o colocar "ID Produto" em "Campos"
Figura 6: Tipos de conta para mostrar no visual
As medidas explícitas, são contas feitas a partir de fórmulas feitas em DAX! É uma ótima prática (sim, melhor do que boa…hehe) criar medidas explícitas para os valores que serão usados nos visuais.
Vamos exemplificar uma medida com os valores de faturamento:
Figura 7: Formas de criar medidas
Figura 8: Área de edição e construção de fórmulas
Etapas: 1. Clicar com o botão direito em cima da tabela ou pasta onde a medida será criada → Clicar em "Nova medida" ou 2. Com a tabela ou pasta onde será criada selecionada clicar em "Página Inicial" → Clicar em "Nova medida" Fórmula DAX:Faturamento =
SUM ( fVendas[Valor Venda] )
Algumas vantagens de utilizar medidas explícitas, são:
- Performance
- Nomenclatura
- Referenciar a medida em outras medidas do relatório
Vantagens das medidas explícitas em relação as colunas:
- Performance: as medidas só são processadas (utilização de CPU) no momento da utilização e para os contextos desejados
- São dinâmicas
- Não consome memória
Contextos de avaliação
No Power BI, as medidas são dinâmicas (como falei anteriormente). Comparando com o Excel, aqui você não precisa fazer o SOMASE e especificar algum argumento. As informações passadas em linhas/colunas/eixos/segmentação irão formar um contexto que a medida respeita. Acredite em mim, não é difícil….mas necessita prática.
Vamos fazer um exemplo? Imagine que você queira analisar os valores de faturamento por subcategoria de produto. No Power BI não precisamos especificar em uma fórmula isso, a própria medida irá respeitar o contexto do visual:
Figura 9: Tabela de apoio (contextos para medida)
Etapas: Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
Você lembra como é calculado o faturamento? Não? Vamos olhar novamente a fórmula de faturamento:
Faturamento =SUM ( fVendas[Valor Venda] )
Veja que em nenhum momento especificamos algo sobre subcategoria. Porém, a medida respeita esse contexto dentro do visual.
Vamos montar outro exemplo, para fixar:
Figura 10: Tabela de apoio 2 (contextos para medidas)
Etapas:
Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
E nesse caso, quantos contextos de avaliação temos? Aqui temos 1 filtro, mas temos 4 contextos de avaliação!!! Os 4, são: France, Germany, United Kingdom e Total.
Legal, né?!! Conheço vocês e devem estar se perguntando….”O que o Power BI está fazendo?” Bom, nada melhor que um exemplo. Vamos analisar France (França) separadamente. France é um valor da coluna País na tabela dCliente. Então para o contexto de France é como se fosse aplicado um filtro na coluna dessa tabela.
Para ficar claro, vamos em dados aplicar esse filtro na nossa tabela:
Figura 11: Exemplo de contexto filtrando a tabela dCliente
Figura 12: Filtro aplicado pelo relacionamento ativo
Voltando no nosso caso, agora fica mais fácil entender que para cada linhas de País temos um contexto. Pense no que vimos até aqui. Quantas linhas de País estão sendo avaliadas no Total? 360 linhas que é o total de clientes (olhe novamente a figura 11 no canto inferior esquerdo).
Outro exemplo! Imagine que você queira saber quantos clientes diferentes compraram da empresa (cobertura de clientes/positivação de clientes). Isso pode ser feito fazendo uma nova medida com a contagem de valores distintas de clientes (DISTINCTCOUNT):
Fórmula DAX:Positivação Clientes =
DISTINCTCOUNT ( fVendas[ID Cliente] )
Figura 13: Tabela de apoio 3 (contexto de medidas)
Agora, quero que você pare e avalie o total!
193 ≠ 192 + 90 – ????
Lembre-se que o total não é a soma das outras linhas. Ela é o resultado da medida (nesse caso “Positivação Clientes”) sem nenhum filtro aplicado. É racional pensarmos que é possível 192 clientes diferentes terem comprado Headphones e 90 clientes diferentes terem comprado MP4&MP3. Ou seja, existem clientes que compraram tanto Headphones quanto MP4&MP3 e no total eles não devem ser contados duas vezes.
Calculate
Essa é nossa rainha do DAX!!! Ela é poderosa, porque pode alterar contextos de avaliação.
Imagine que no visual de faturamento por país (aquele que criamos agora pouco) que criamos queremos avaliar esse faturamento comparado ao ano de 2018. Assim, teremos duas colunas de faturamento. Para podermos criar um contexto diferente entre as duas colunas precisamos do auxilio da CALCULATE. Vamos criar a nova medida para o contexto de 2018:
Fórmula DAX:Faturamento 2018 =
CALCULATE ( [Faturamento]; dCalendario[Ano] = 2018 )
Figura 14: Tabela de apoio 4 (contexto com CALCULATE)
Etapas: 1. Criar a medida de Faturamento 2018 2. Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
Aqui é bom que fica claro que temos contextos visíveis (as linhas de País) e também o contexto adicional mudado com o auxilio da CALCULATE para a coluna de Faturamento 2018. O valor de faturamento para essa coluna é naturalmente menor, pois o intervalo de tempo é menor.
Outra forma de utilizar a função CALCULATE é para remover filtros. Esse recurso é bastante utilizado para comparar valores com seus totais, por exemplo. Imagine que você queira apresentar o percentual do faturamento de um país frente ao total. Esse é um exemplo clássico para usar a nossa rainha CALCULATE junto com ALL que é o argumento que permite sair de contextos aplicados pelo visual.
Com a tabela que temos, podemos fazer um comparativo entre uma medida com o ALL (que saí do contexto de avaliação) com outra medida que respeita o contexto. Para isso, vamos fazer:
Fórmula DAX:Faturamento Fixo ALL =
CALCULATE ( [Faturamento]; ALL ( fVendas ) )
Figura 15: Tabela de apoio 5 (avaliação de contexto com e sem ALL)
Etapas: 1. Criar a medida de Faturamento Fixo ALL 2. Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
Bom, com esse valor fixo podemos criar nossa medida de percentual do resultado pelo total com outra medida e utilizar no visual:
Fórmula DAX:% Faturamento =
DIVIDE ( [Faturamento]; [Faturamento Fixo ALL] )
Figura 16: Tabela de apoio 5 (avaliação de contexto com comparação de percentual)
| Dica: Não tentem decorar todas as fórmulas e copiar mecanicamente o que aprendeu. Pare e entenda a lógica, pois tem bastante apoio de “ajuda” dentro do software e a biblioteca das fórmulas explicadas (muitas com exemplo) pela Microsoft. Link: https://docs.microsoft.com/pt-br/dax/dax-function-reference |
Para testar seu conhecimento quero que me responda o que acontece com os valores da coluna de Faturamento se usarmos uma segmentação de dados na página para o ano de 2018:
Figura 17: Interação entre segmentação de dados e visuais
Me fala, aí! Você acha que o valor muda ou não!??
Figura 18: Resultado de filtro aplicado
Veja que os valores da coluna de Faturamento Fixo ALL não mudaram. Já a de Faturamento respeitou o novo contexto aplicado e a % Faturamento também mudou, pois depende da Faturamento.
Podemos fazer uma nova medida utilizando ALL, mas agora para ignorar filtros na tabela de clientes:
Fórmula DAX:Faturamento Fixo ALL =
CALCULATE ( [Faturamento]; ALL ( dCliente ) )
Figura 19: Tabela com a nova medida de Faturamento Fixo ALL
Pergunto novamente! O que acontece se filtramos o ano de 2018 nesse caso!???
Figura 20: Resultado de filtro aplicado
Perceba que aqui os valores foram filtrados! Aqui foi ignorado os filtros de contexto de dClientes (que não contém datas).
| Importante: Treine bastante com ALL em seus projetos para entender e praticar os conceitos dessa função que é essencial em muitos casos. |
Além de tudo isso, você consegue usar mais de um ALL em suas fórmulas. Vamos fazer 2 exemplos! No primeiro iremos usar ALL para ignorar das colunas específicas da mesma tabela e no segundo para ignorar duas tabelas por completo:
Fórmula DAX:Faturamento Fixo ALL =
CALCULATE ( [Faturamento]; ALL ( dCliente[País]; dCliente[ID Cliente] ) )
Fórmula DAX:Faturamento Fixo ALL =
CALCULATE ( [Faturamento]; ALL ( dCliente ); ALL ( dProduto ) )
Imagine agora um cenário onde você quer avaliar cada um dos países com um país em específico ( no nosso caso será País = França). Acredito que seja uma avaliação bem comum no seu dia a dia ter que comparar os valores entre unidades, e isso é bem parecido com o que vamos fazer.
Precisamos da medida que ignora todos os filtros e o país é França:
Fórmula DAX:Faturamento France Fixo =
CALCULATE ( [Faturamento]; ALL ( dCliente ); dCliente[País] = “France” )
Figura 21: Tabela com a nova medida de Faturamento France Fixo
Etapas: 1. Criar a medida de Faturamento France Fixo 2. Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
Existem diversas formas de escrever o mesmo raciocínio em DAX com ALL. Uma das formas mais utilizadas é com a função FILTER:
Fórmula DAX:Faturamento France Fixo =
CALCULATE (
[Faturamento];
FILTER ( ALL ( dCliente ); dCliente[País] = “France” )
)
O resultado das duas medidas é o mesmo!
Além da ALL, temos outra função bem importante para avaliação de contextos a ALLSELECTED. Essa função remove filtros de contexto de colunas e linhas na consulta atual, mantendo todos os outros filtros externos (segmentação de dados na página, por exemplo). Vamos fazer uma medida e utilizar na nossa tabela de comparação:
Fórmula DAX:Faturamento Fixo ALLSELECTED =
CALCULATE ( [Faturamento]; ALLSELECTED ( dCliente ) )
Figura 22: Tabela comparativa entre ALL e ALLSELECTED
Etapas: 1. Alterar medida de Faturamento France Fixo com FILTER 2. Criar a medida de Faturamento Fixo ALLSELECTED 3. Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
A terceira função que utilizamos em CALCULATE para alterar contextos é a VALUES. Essa função retorna uma tabela de coluna única que contém os valores distintos da coluna especificada e utilizamos essa coluna como filtros. Vamos para o exemplo?
Fórmula DAX:Faturamento VALUES =
CALCULATE ( [Faturamento]; VALUES ( dCliente ) )
Figura 23: Tabela comparativa entre Faturamento e Faturamento com VALUES
Etapas: 1. Criar a medida de Faturamento VALUES 2. Em "Visualizações" selecionar "Tabela" → Atribuir os campos conforme imagem
Veja que nesse caso a VALUES respeita o filtro do contexto.
Desafio: Análise de Churn Médio
Para praticarmos tudo que foi aprendido, iremos montar um relatório fazendo a análise de Churn Médio de uma empresa fictícia. A taxa de Churn representa a quantidade de clientes que deixaram a empresa em um determinado período.
O valor total de Churn, é a soma dos valores de Churn:
Fórmula DAX:Churn =
SUM ( fChurn[Valor Mensal] )
Para analisar esse indicador, vamos comparar o realizado da data com a média do valor de Churn. No “Análise” do visual de gráfico, o Power BI dá a opção de usarmos uma linha média como indicador:
Figura 24: Criação do Gráfico com a linha média de Churn
Etapas: 1. Criar a medida de Churn 2. Em "Visualizações" selecionar "Gráfico de área" → Atribuir os campos conforme imagem 3. Em "Análise" clicar em "Adicionar" → Alterar cor e transparência
O problema de usar diretamente esse tipo de análise é que o comparativo do realizado é feito com todo o período. Além disso, se utilizarmos um filtro e quisermos mostrar somente janeiro de 2017, por exemplo, a linha para comparação continua sendo a do período todo.
Em DAX, vamos criar o Churn que respeite os limites de mês por partes. Primeiro precisamos do total do Churn:
Fórmula DAX:Churn Médio =
CALCULATE ( [Churn]; ALL ( dCalendario ) )
Agora vamos dividir esse valor total pelos dias que tiveram Chrun (pode ser diferente do total de dias do período, pois algum dia pode não ter tido Churn):
Churn Médio =CALCULATE (
[Churn] / DISTINCTCOUNT ( fChurn[Data Churn] );
ALL ( dCalendario )
)
O resultado da nossa medida já no visual, ficará:
Figura 25: Visualização do gráfico com a linha de análise e a medida
Etapas: 1. Criar a medida de Churn Médio 2. Atribuir a medida Churn Médio em Valores
Imagine o cenário que você utiliza segmentação de datas na sua página, e que esse gráfico respeite os filtros. Qual função podemos utilizar para que funcione corretamente esse contexto? Lembra da ALLSLECTED?? É ela mesma!!!
Fórmula DAX:Churn Médio =
CALCULATE (
[Churn] / DISTINCTCOUNT ( fChurn[Data Churn] );
ALLSELECTED ( dCalendario )
)
E a última pergunta: Como avaliar em contexto de mês separadamente? Lembra da VALUES?? Se não lembra, volta um pouco no post e leia novamente, pois é ela que vamos usar:
Fórmula DAX:Churn Médio =
CALCULATE (
[Churn] / DISTINCTCOUNT ( fChurn[Data Churn] );
ALLSELECTED ( dCalendario );
VALUES ( dCalendario[Mês/Ano] )
)
E o resultado do nosso gráfico com a medida final fica assim:
Figura 26: Gráfico com a medida final
Veja o poder que temos com ALL/ALLSELECTED em conjunto com VALUES. Se alguns exemplos onde provavelmente você terá que usar essas funções:
- Acumulado de uma valor por categoria
- Contribuição de de uma equipe de vendedores por supervisor
- Combinação de média avaliada por mês
E aí, imagina na sua rotina algum desses desafios?
Espero que tenham aprendido e tido uma boa visão sobre DAX. Pratique bastante para fixar os conceitos e conseguir usar nos desafios da sua rotina esse conhecimento!! Se ficar com alguma dúvida ou tenha uma sugestão de tema para as Lives, deixa nos comentários.
Valeu pessoal! Abraço,
Leonardo
