Pular para o conteúdo
EstuDocs

Linguagem DAX Avançado

Módulo 01 - Funções Básicas

1. Introdução ao curso de DAX Avançado

2. Download dos materiais

3. Exercício: Calculando medidas básicas de soma e contagem

4. Solução: Calculando medidas básicas de soma e contagem

Aula 1
- Calculando medidas básicas de soma e contagem

5. O problema da agregação em colunas calculadas

  • tentar usar colunas calculadas ao invés de medidas…

  • colunas calculadas não podem ser utilizadas em nenhum visual, pois não trazem o cálculo correto. Só “servem” para ser analisada a nível de uma coluna na tabela, ex abaixo:

alt text

  • abaixo temos a diferença ao tentar usar uma coluna calculada dentro de um visual (visual de tabela)

alt text

  • quando queremos utilizar algo num eixo / legenda, precisamos usar uma coluna (calculada). Quando queremos colocar valores nos gráficos, aí devemos usar medida

alt text

6. Exercício: Segmentando os clientes com SWITCH

Aula 3
- Segmentando os clientes com SWITCH (colunas e medidas)
* Não é possível fazer segmentação dinâmica quando usamos coluna calculada (ex: tabela aula 3 com coluna "Segmentação Coluna Calculada"). Ao aplicar os filtros externos (segmentação de dados), os números não mudam.

7. Solução PT 1: Segmentando os clientes com SWITCH

Segmentação Coluna Calculada =
SWITCH(
    TRUE(), -- sai do switch quando encontra a resposta verdadeira -> TRUE()
    [Total Vendido] >= 1000000 && [% Margem] >= 0.5, "Platinum",
    [Total Vendido] >= 1000000 && [% Margem] < 0.5, "Premium",
    [Total Vendido] >= 500000 && [% Margem] >= 0.5, "Gold",
    [Total Vendido] >= 500000 && [% Margem] < 0.5, "Silver",
    [Total Vendido] > 0, "Bronze",
    [Total Vendido] = 0, "New",
    "Erro"
)

8. Solução PT 2: Segmentando os clientes com SWITCH

  • coluna calculada -> valor estático, não considera filtros externos

  • medida -> valor dinâmico, considera filtros externos

  • para exibir a contagem das categorias, precisamos da coluna calculada

alt text

  • para alterar a ordem de exibição no visual (ex: na tabela), precisamos criar uma outra coluna calculada informando como ficará a ordem das categorias (segmentação de clientes)

alt text

alt text

  • mesmo usando coluna calculada para exibir a contagem das categorias, ainda assim os valores não são dinâmicos, ex:

alt text

alt text

alt text

  • para esses casos, teremos aula mais a frente para conseguir alterar de forma dinâmica, com transição de contexto

10. Funções de tabela: FILTER, ALL e VALUES

FILTER: retorna uma tabela filtrada, ex: filtrar a tabela vendas com quantidade de itens mais altos


Vendas Altas =
FILTER(
    fVendas,
    fVendas[Quantidade] > 100
)

alt text

  • a função FILTER pode retornar uma tabela física no modelo, porém não faz sentido. Faz sentido usar a FILTER dentro da CALCULATE para usar nas tabelas que já possuem os relacionamentos, ao invés de criar uma tabela física nova filtrada…
ALL: retorna os valores únicos (remove os duplicados) de 1 coluna ou tabela


Notas = ALL(fVendas[No. Venda])

alt text

  • pode retornar mais de 1 coluna, ex:
Notas = ALL(fVendas[No. Venda], fVendas[ID Cliente])
VALUES: igual a ALL, porém aceita apenas 1 argumento (1 coluna ou tabela)
  • a diferença entre ALL e VALUES não é perceptível em tabelas físicas, somente ao aplicar contexto de filtros dentro de medidas. Diferença entre medidas abaixo:
Aula 4
- Funções de tabela: FILTER, ALL e VALUES + AVERAGEX
* distinctcount retorna o mesmo valor de countrows com values
ex:
DISTINCTCOUNT(fVendas[ID Cliente])
COUNTROWS(VALUES(fVendas[ID Cliente])) -- como VALUES retorna uma tabela com valores únicos, countrows desse values terá o mesmo comportamento de um distinct count
Função ALL ignora/remove todos os filtros/contextos aplicados. A função VALUES considera


Cobertura Clientes VALUES =
COUNTROWS(
    VALUES(fVendas[ID Cliente])
)


Cobertura Clientes ALL =
COUNTROWS(
    ALL(fVendas[ID Cliente])
)

alt text

11. Calculando médias com AVERAGEX

alt text

12. Exercício: Calculando o Ticket Médio usando AVERAGEX

13. Solução: Calculando o Ticket Médio usando AVERAGEX

Aula 5
- Exercício + Solução: Calculando o Ticket Médio com AVERAGEX (2 soluções possíveis, usando DIVIDE e usando AVERAGEX)
  • toda média pode ser calculada fazendo uma divisão (função DIVIDE) ou utilizando a função AVERAGEX

Módulo 02 - Função CALCULATE

1. Exercício: Revisando sobre Contexto de Filtro

- O Power BI trabalha com contexto e relacionamento entre as tabelas. Por ex abaixo: filtrando o estado 'Arizona' e o ano '2018', essas colunas das tabelas dCliente e dCalendario filtram a tabela fVendas. Nesse exemplo, a tabela fVendas retornará 126 linhas, conforme abaixo.

alt text

alt text

2. Solução: Revisando sobre Contexto de Filtro

Aula 6
- Exercício + Solução: Revisando sobre Contexto de Filtro

3. Filtrando expressões com a CALCULATE

  • a função CALCULATE interfere nos contextos/filtros originais aplicados (visuais, slicers…)

4. Exercício: Filtrando com condicionais “E” e “OU”

alt text

  • filtros aplicados para cada argumento da CALCULATE (separados por vírgula) ou com && -> condicional “E” - intersecção de conjuntos/valores

  • filtros aplicados com || ou IN -> condicional “OU” - união/soma de conjuntos/valores

  • condicional OU soma, condicional E restringe

alt text

5. Solução: Filtrando com condicionais “E” e “OU”

Aula 8
- Filtrando expressões com a CALCULATE
- Exercício: Filtrando com condicionais “E” e “OU”

6. Mantendo filtros com KEEPFILTERS

Aula 9


Qtd Contoso =
CALCULATE(
    [Qtd Linhas Vendas],
    dProduto[Marca] = "Contoso"
)
  • por trás dessa medida, é aplicado um ALL em marca e depois aplicado um filtro na mesma coluna aplicando o texto “Contoso”

alt text

  • a função ALL mostrará o mesmo valor em todos os contextos. A função KEEPFILTERS mostrará apenas no contexto onde é aplicado o filtro

alt text

7. Removendo os filtros com ALL e REMOVEFILTERS

Aula 1 - Base Live79 - Inicio.pbix


-> REMOVEFILTERS -> similar a função ALL (remove todos os filtros)

8. CUIDADO: escolha corretamente os argumentos da ALL

Aula 2 - Base Live79 - Inicio.pbix

  • a ALL remove os filtros da tabela ou coluna(s) selecionada(s).

  • ex: imagem abaixo

alt text

9. ALL VS ALLSELECTED

Aula 2 - Base Live79 - Inicio.pbix


ALLSELECTED mantem os filtros externos aplicados

10. Exercício: Calculando o percentual sobre subtotais

11. Solução: Calculando o percentual sobre subtotais

12. HASONEVALUE x HASONEFILTER x ISINSCOPE x ISFILTERED

Aula 5 - Base Live79 - Inicio.pbix
  • funções DAX booleanas (retornam verdadeiro ou falso)

alt text

  • ISINSCOPE -> usada muito para manipular valores em DRE

13. Utilizando ISINSCOPE para ajustar os percentuais

  • ajustar subtotal

alt text

14. Exercício: Fazendo rateio de metas

  • tabela que nós temos tem a meta do ano

15. Solução: Fazendo rateio de metas

16. Usando variáveis em DAX

  • variáveis ajudam na leitura e manutenção de medidas

17. Diluindo metas anuais por dia útil

18. Perigos no uso de variáveis

  • variáveis são CONSTANTES

  • não conseguimos aplicar filtros ao utilizar as variáveis (CALCULATE!!!)

  • variáveis dentro do primeiro argumento da CALCULATE não vai funcionar, pois é um valor constante. Dentro do primeiro argumento da CALCULATE deve ser uma expressão, uma medida…

  • variáveis não permitem alterar contexto nela (por isso uma medida - no 1º argumento - com a CALCULATE não funciona)

alt text

alt text

alt text

Módulo 03 - Funções de Inteligência de Tempo

1. Desabilitando Auto Date/Time e criando hierarquia de datas

  • por padrão, o Power BI cria uma tabela de datas escondida sempre que observa uma coluna de data

  • no DAX Studio conseguimos visualizar essas tabelas de datas escondidas

  • essas tabelas escondidas ocupam espaço no modelo

alt text

alt text

alt text

  • para boas práticas, sinalizar a tabela dCalendario como tabela de data do modelo

2. Exercício: Criando medidas de Inteligência de Tempo

3. Solução: Criando medidas de Inteligência de Tempo

4. Escondendo datas futuras

  • tentar resolver dentro das medidas geralmente não é a melhor solução. Por ex: tentar aplicar um filtro se a medida possui dados > 0, ou IF ([NomedaMedida])…

  • a melhor resolução é criar uma coluna calculada na dCalendario se as datas possuem dados e aplicar a coluna no filtro lateral do visual

Possui Dados? = dCalendario[Data] <= MAX(fVendas[Data Venda])

alt text

  • DE:

alt text

  • PARA:

alt text

  • podemos também restringir os valores aplicando filtro na medida, porém aplicando filtro sobre as datas, não sobre a medida da CALCULATE (1º argumento). Ex:
Total Vendido YTD =
CALCULATE(
    [Total Vendido],
    DATESYTD(dCalendario[Data]),
    dCalendario[Possui Dados?] = TRUE() // ERRADO: não fará diferença, pois o filtro aplicado aqui está sobre a medida [Total Vendido]
)


Precisamos aplicar o filtro sobre a tabela de datas. Para isso, temos a função CALCULATETABLE


Total Vendido YTD =
CALCULATE(
    [Total Vendido],
    CALCULATETABLE(
        DATESYTD(dCalendario[Data]),
        dCalendario[Possui Dados?] = TRUE()
    )
)


* Criar uma coluna na tabela dCalendario para manter apenas as datas onde possuímos dados na fVendas, aplicando essa coluna no visual necessário
* OU aplicar um filtro diretamente na medida através da fórmula DAX CALCULATETABLE
CALCULATE aplica filtros em medidas
CALCULATETABLE aplica filtros em tabelas


Não conseguimos utilizar FILTER no exemplo acima, pois a FILTER permite filtrar no segundo argumento apenas as colunas disponíveis no primeiro argumento dentro da FILTER
Ex:
FILTER(
    DATESYTD(dCalendario[Data]),
    dCalendario[Data] = xxx
)


dCalendario[PossuiDados] não estaria disponível para ser filtrada com a FILTER

5. Exercício: Calculando o valor do ano anterior (modo raiz)

6. Solução: Calculando o valor do ano anterior (modo raiz)

7. Versão alternativa com FILTER para o exercício anterior

8. Calculando o valor do mês anterior (modo raiz)

  • Cuidado ao manipular colunas com ordenação!!!

  • Medida criada conforme “padrão” para trazer o valor total fixo, porém nesse caso não funcionou.

alt text

  • Não funcionou pois a coluna dCalendario[Nome do Mês] está sendo ordenada pela coluna Mês. Nesse caso a coluna mês também faz parte do cálculo (é exibida em Performance Analyzer)

alt text

  • Acrescentando a coluna Mês no ALL, aí temos o valor fixo conforme objetivo.

alt text

9. Cálculo de acumulado infinito com ALL/ALLSELECTED

10. Exercício: Calculando o acumulado do ano (modo raiz)

11. Solução: Calculando o acumulado do ano (modo raiz)

12. Totais e médias móveis com DATESINPERIOD

13. Deixando o período de forma dinâmica

  • Utilizar parâmetro em modelagem

  • Ao invés de deixar 3 meses fixos, o usuário pode alterar esses valores através de um slicer na tela

alt text

14. [Extra] Guia visual para entender funções de inteligência de Tempo

15. [Extra] Como calcular média móvel por semana do ano com as funções WINDOW e ORDERBY

  • PENDENTE

16. [Extra] Facilitando o entendimento de funções iteradoras com CONCATENATEX

  • PENDENTE

Módulo 04 - Usando DAX em Visuais

1. Destacando o maior e o menor valor no gráfico

  • Como a coluna Mês Abrev. está ordenada pela coluna Mês, necessário incluir essa coluna na fórmula ALL
VAR vMaiorValor =
MAXX(
    ALL(dCalendario[Ano], dCalendario[Mês Abrev.], dCalendario[Mês]),
    [Total Vendido]
)

alt text

2. Ajuste importante do exercício anterior

  • Utilizar ALLSELECTED dentro das funções MAXX e MINX para considerar maior e menor valor de acordo com o ano selecionado (slicer)

3. Exercício: Destacando o primeiro e o último valor no gráfico

4. Solução: Destacando o primeiro e o último valor no gráfico

5. Destacando valores acima da média

6. Destacando valores de forma dinâmica

  • Criar um botão para o usuário selecionar o que ele quer destacar: min, máx, acima da média, etc…

  • Necessário ter uma tabela com essas opções

Módulo 05 - Outras Aplicações de DAX

1. Transição de contexto com CALCULATE

  • Coluna calculada tem um contexto de linha, não de filtro, por ex: criar uma medida “SUM(fVendas[Quantidade])” na tabela dProdutos. O resultado será o total de quantidade da tabela fVendas.

  • Podemos alterar esse contexto (transição de contexto) utilizando CALCULATE. Nesse caso, muda de um contexto de linha para contexto de filtro

Qtd Vendida =
CALCULATE(
    SUM(fVendas[Quantidade])
)
  • Se usarmos uma medida na coluna calculada, é aplicado o contexto de filtro (valores “corretos”). Medidas usam implicitamente uma CALCULATE por fora.

alt text

  • Funções sujeitas a transição de contexto: toda função que opera linha a linha (contexto de linha) ex: SUMX, AVERAGEX, funções iteradoras…

alt text

alt text

  • CALCULATE permite que um contexto de linha seja transformado em um contexto de filtro

alt text

Reforço Contexto de Filtro - Aulas Hashtag (DAX AVANÇADO)

2. Contextos de Avaliação

  • Contexto de Linha

alt text

  • Contexto de Filtro

alt text

3. Testando os conhecimentos de Contexto de filtro

  • Medidas dentro de uma matriz -> contexto de filtro

alt text

4. Testando os conhecimentos de Contexto de filtro (Parte 2)

  • Média de faturamento = AVERAGE([Faturamento])

alt text

20. Transição de Contexto (Parte 1)

21. Transição de Contexto (Parte 2)

2. Utilizando medidas para filtrar outras medidas

  • Quantos clientes compraram acima de 100 mil reais
Clientes Grandes =
CALCULATE(
    [Cobertura Clientes],
    FILTER(
        dCliente,
        [Total Vendido] > 100000
    )
)

3. Exercício: Calculando o Total Vendido para meses bons

4. Solução: Calculando o Total Vendido para meses bons

CALCULATE(
    [Total Vendido],
    FILTER(
        VALUES(dCalendario[Mês/Ano]),
        [Total Vendido] > 7000000
    )
)

5. Segmentação Dinâmica com Relacionamentos Virtuais

6. Ajustando os totais - PT 1

7. Ajustando os totais - PT 2

8. Criando rankings com RANKX e TOPN

  • Se passarmos no primeiro argumento apenas a tabela dProduto, o contexto será o próprio produto analisado, retornando o valor 1. Por isso, é necessário utilizar a ALL
RANKX(
    ALL(dProduto),
    [Total Vendido]
)

9. Ranking dinâmico

10. Exercício: Segmentando de forma dinâmica com TOPN

11. Solução: Segmentando de forma dinâmica com TOPN

12. Ranking de duas colunas com CROSSJOIN

13. Desempatando rankings