Power BI DAX Fundamentos com Jeffrey Wang

Aprender os conceitos é muito importante pra conseguir extrair o máximo do DAX, por conta disso, estou estudando mais a fundo a teoria e, como sempre, vou deixar aqui todas minhas anotações sobre o que ando estudando. Nesse primeiro post, assisti por algumas vezes o vídeo que deixo na referência, recomendo que assistam também.

Highlights

• Todos filtros no DAX são tabelas (all DAX filters are tables)
• Filtros caminham entre relacionamentos entre as tabelas
• Sempre que possível use ALL com Filter e defina uma coluna dentro de ALL e não a tabela inteira (isso vai melhorar a performance)
  • Especialmente se a tabela fato é muito grande ou se está usando um tabelão

Qual a diferença entre essas medidas?

A medida 1 é uma “syntax sugar” e que na verdade é a exatamente a mesma coisa que a medida 3. Quando dizemos exatamente a mesma coisa, queremos dizer que, em tempo de execução a medida 1 é transformada na medida 3. Não há nenhuma diferença de performance, nem de resultados.

Já a medida 2 é diferente da medida 1 e 3, porque nela não tem o ALL(). Por isso, o resultado será diferente. Na medida 2, como ela não tem o ALL, ela mesma será filtrada caso seja colocada em um visual onde exista algum outro filtro na tabela relacionada no FILTER(), nesse caso a tabela Geografia.

(na próxima imagem, US Sales 1 refere-se a medida 1 e Sales US 1.1 refere-se a 3 e US Sales 2 refere-se a 2)

nota: as medidas da imagem acima são feitas com base em SUM( Sales[Sales Amount] ) e não com base em Distinctcount( Sales[CustomerKey[ ), como na primeira imagem.

Na imagem acima, perceba que no visual da esquerda, que é uma table, perceba que não temos nenhuma coluna ou linha, ou seja, nenhum “eixo” (breakdown by date) que seja Geografia, portanto, todas medidas retornam exatamente o mesmo resultado.

Porém, na table da direita, perceba que nas linhas temos Country, que é um “eixo” da tabela Geografia (breakdown by country), nesse caso a medida 2 não vai trazer exatamente o mesmo resultado que as outras medidas, porque ela é filtrada antes de exibir seu resultado. Portanto:

US Sales 2 = CALCULATE ( SUM( Sales[Sales Amount]), Filter( Geography, [Country] = “United States” ))

Perceba que, pensando na linha Austrália, ao executar essa medida acima, que é a medida 2, ela será filtrada por country = Austrália, antes de filtrar Country = United States, de forma que, quando chegar na hora de aplicar o filtro de United States, nada vai retornar. Por isso o resultado blank. Obviamente, quando a linha for United States, haverá um retorno, pois United States = United States.

Como filtro em DAX funciona?

Todos filtros no DAX são tabelas (all DAX filters are tables, isso aqui não sou eu que estou dizendo, é o Jeffrey Wang).

Portanto, podemos dizer que, no caso da imagem acima, o gráfico em questão, está sendo filtrado por 3 tabelas (color, gender, product category name).

A primeira tabela, Color, tem um coluna, com dois registros, blue e red. A segunda tabela, Gender, tem uma coluna com apenas um registro, M. A terceira tabela, Product Category, perceba que ela vem da aba de Filters, tem apenas uma coluna, com dois registros, Clothing e Components.

Além dos filtros já descritos, o próprio visual aplica filtros em cada um dos datapoints. No caso desse gráfico, cada datapoint ainda é filtrado por uma tabela de um registro e três colunas (year, quarter, month).

Todos esses filtros são tabelas dentro do Filter Context.

Como filtros são aplicados em DAX?

Através dos relacionamentos. As tabelas relacionadas serão “juntadas”, usando joins, claro. Nesse ponto é importantíssimo estar atento à cardinalidade dos relacionamentos. Relacionamentos 1:N e N:N impactam o resultado do seu filtro, a depender dos registros do seu modelo, pois se comportam de forma diferente um do outro.

Então os filtros são aplicados através dos relacionamentos. Você pega a tabela que quer filtrar, verifica seus relacionamentos, e então verifica quais tabelas estão relacionadas e quais você quer filtrar. Lembrando que filtros podem ser tabelas com apenas um registro, ou vários, ou com uma ou várias colunas. (row, multi-row, multi-column).

O filtro então é feito usando técnica de semi-join, de forma que apenas os resultados da tabela da esquerda serão mantidos, uma vez que eles tenham resultados positivos nos filtros da tabela da direita. Como se fosse um inner join entre as tabelas, mas sem trazer nenhuma coluna das tabelas de filtro.

Isso será feito linha a linha (row by row), para cada filtro aplicado, até que todos os filtros sejam testados e validados. Apenas as linhas que testarem positivo para todos filtros serão mantidas, as outras serão descartadas. Somente depois disso é que a agregação será realizada (sum, count, avg, etc).

Por exemplo, considere a imagem abaixo.

Nos itens destacados temos um visual, que é um visual de tabela, na linha temos Country e na coluna temos US Sales 2. Sabemos que US Sales 2 = CALCULATE ( SUM( Sales[Sales Amount]), Filter( Geography, [Country] = “United States” )). O que será feito nesse caso, considerando o que aprendemos até agora é, o DAX vai scannear a tabela de SALES para coluna Sales Amount, verificar qual tipo de relacionamento ela tem com a tabela Geografia, fazer um semi-join, nesse semi-join vai filtrar tudo que for Country = United States por conta de que no visual de tabela tem Country na linha, vai verificar em SALES todos registros que são válidos pra essa condição, descartar os registros de SALES que não satisfazem essa condição e trazer esse resultado, depois vai pegar esse resultado, verificar se dentro deles tem novamente Country = United States porque essa é a condição do FILTER() dentro da Calculate(), vai descartar os registros que não satisfazem e manter o que atendem. Depois disso tudo, vai pegar a coluna de Sales Amount e vai realizar um SUM() em cima dela. O resultado será exibido no visual.

Como isso funciona nas medidas que temos?

Voltando então à nossa imagem inicial…

Agora que já entendemos mais da lógica, sabemos que a medida 1 é apenas um atalho, mas como nela não existem tabelas sendo filtradas, ela obrigatoriamente será sempre transformada, em tempo de execução, na medida 3, que é onde temos um filtro apropriadamente de tabela.

Portanto, na medida 3 temos um filtro feito sobre uma coluna única, single row, single column. Enquanto que, na medida 2, temos um filtro feito sobre uma tabela inteira, e dessa forma o DAX irá retornar todas as colunas da tabela (na imagem abaixo a estrutura de geography).

Outra diferença entre a medida 2 e 3 é que na medida 3 temos ALL(), isso significa dizer que, todo e qualquer filtro no report será ignorado e substituído pela expressão do filtro, que nos caso é “United States”. É por isso que, na imagem abaixo, o valor de Sales para United States se repete, mesmo em linhas da tabela em que Country claramente não é United States. Como na imagem abaixo.

Ainda sobre o ALL(), quando falamos que ele substitui os filtros, como fez nas medidas US Sales 1, a visão que você pode ter é essa:

Importante entender que, apenas o filtro de Geografia está sendo substituído. Qualquer outro filtro ainda se aplica a essas medidas. Por exemplo, caso você adicione um slicer de Customer, o filtro resultante desse slicer de customer irá sim impactar o resultado dessas medidas, como é o caso da imagem abaixo.

Portanto, na medida 2, onde não existe ALL(), a medida será filtrada ANTES por qualquer filtro aplicável, e depois, filtre o Country = “United States”, por isso, na tabela da esquerda todas medidas se comportam exatamente da mesma forma, pois nela não há nenhum tipo de filtro em geografia, enquanto que na tabela da direita existe, e esse filtro de Country irá filtrar a tabela Geografia, impactando diretamente a medida 2.

Performance da Medida 2

A medida 2 pode ser melhor escrita da seguinte forma:

CALCULATE( SUM( Sales[Sales Amount] ), KEEPFILTERS( Geography[Country] = “United States”))

Porque, dessa forma, filtro é feito através de uma única coluna (“single column filter”) ao invés de considerar todas a tabela, como a medida 2 original considera.

KEEPFILTERS significa “não sobrescreva” (don’t overwrite), ou seja, se existe alguma filtro em Country, mantenha ele e só então adicione meu novo filtro, que no caso é Country = “United States”. Dessa forma, perceba na imagem abaixo, as linhas do visual de tabela já são os Country, então já existe um filtro ali.

Medidas dentro de uma Iteração

Utilizar medidas dentro de uma iteration pode ocasionar resultados indesejados. Dá uma olhada na medida acima.

UPDATE: Na sequência explico de forma mais objetiva, tal como Wang explicou no vídeo, porém ao final desse tópico abordo a questão de Transição de Contexto (contribuição do Fred), que detalha melhor esse comportamento do DAX.

Na imagem acima existe uma medida chamada Current Date que traz o MAX de Date[Date], e outra medida chamada de YTD Sales que calcula o Total Sales de forma a acumular o valor de Total Sales ao longo do ano. O problema é que essa medida simplesmente não funciona conforme esperado.

Perceba que o resultado é o mesmo para todas as linhas.

Conceitualmente DAX calcula cada datapoint de forma indepente (é por isso que muitas vezes o total de uma tabela não é a soma das linhas da tabela, porque o total é calculado de forma independente). Portanto, cada vez que você invoca uma medida DAX ela será recalculada considerando somente aquele contexto específico.

Dessa forma, considerando a medida YTD Sales 1, temos uma iteração em FILTER() que será calculada linha a linha (row by row), e pra cada linha, toda expression de FILTER() é reavaliada (lembrando que a expressão de filter() é: Year( [Date] ) = Year( [Current Date] ) && [Date] <= [Current Date]), e nessa expressão temos uma medida, e essa medida está sendo reavaliada também linha a linha. Portanto, quando essa expressão estiver acontecendo no nível da primeira data a medida [Current Date] não será o max da data externo (como intuitivamente acreditamos), mas sim será o mesmo valor que a linha em que está sendo avaliada, dessa forma todas as linhas serão válidas e o resultado será sempre o total geral de [Total Sales] de acordo com o retorno de todas linhas de Date.

Como resolver esse problema?

Para problemas como esse o uso de variáveis é o recomendado. A variável vai armazenar o valor da medida antes da iteração começar.

UPDATE START: Transição de Contexto

Quando se usa medidas, ou o próprio Calculate(), a transição de contexto é realizada. Portanto, quando utilizamos a função Filter(), que é uma função de contexto de linha, e colocamos dentro desse Filter() uma medida, a transição de contexto é realizada. Porém, ao utilizarmos a variável antes da função, ela inibe a transição de contexto. A variável armazena o valor recebido e ao utilizarmos essa variável em qualquer função iteradora, como SUMX() ou FILTER(), a transição de contexto não irá acontecer. Portanto, se na nossa medida original (conforme imagem abaixo) fosse retirada a medida e colocado no lugar um MAX(Date[Date]) funcionaria sem problemas, porém, já se tentarmos com LASTDATE(), que é uma função de inteligência de tempo, o problema continuaria, pois LASTDATE() é uma função de inteligência de tempo e ativa a transição de contexto (como qualquer função de inteligência de tempo).

Isso acontece porque, “embutido” dentro das medidas há sempre um Calculate(), e o Calculate() quebra o contexto de linha e transforma ele num contexto de filtro. Dessa forma, utilizando medidas, cada linha da tabela do Filter() será transformado de um contexto de linha em um contexto de filtro, isso é o que chamamos de Transição de Contexto.

UPDATE END **

Medidas Não Intencionais (Dense Measure)

Muitas vezes medidas retornam blank, porém algumas medidas podem acabar retornando alguma coisa, como zero ou 1 ou -1. Tome cuidado com essas medidas, pois elas não são performáticas e podem ser extremamente lentas em datasets maiores.

No exemplo da imagem acima, sempre que não houver algum registro de LY Sales, o resultado será -1.

Uma maneira de corrigir isso seria criando uma medida como Divide( [CY Sales] – [LY Sales], [LY Sales]), no entanto essa não é a forma mais performática, pois a medida [LY Sales] estaria sendo avaliada duas vezes por cada datapoint.

Para melhorar a performance, o mais indicado é utilizar variáveis, como na imagem abaixo.

Referências

https://www.youtube.com/watch?v=bJtRB86n9tk – DAX Overview with Jeffrey Wang – Portland Power BI UG