Power BI DAX Mesma medida, resultados diferentes

Hoje apareceu uma dúvida interessante no grupo de Power BI e como já falamos um pouco sobre contexto e transição de contexto nesse post, vamos continuar tratando essas dúvidas pra ir esclarecendo melhor o que a engine do DAX faz e porque esses “problemas” acontecem.

O cara por trás do DAX

Agradecimento especial ao Fred! Sem ele esse post não seria possível! Fred trouxe a dúvida e a resposta e ainda me ajudou a entender toda essa resolução. Obrigado, Fred! Quer saber quem é o Fred? Linkedin dele aqui!

O Problema a ser resolvido

A dúvida foi a seguinte:

Na dCalendario tem uma hierarquia com as colunas ano, mês e dia. Também nessa tabela tenho uma outra coluna "mês/ano".

Quero fazer uma comparação de um mês com o mesmo mês do ano de 2018. Pra isso aplico a função CALCULATE com filtro dCalendario[Ano] = 2018.

Porém, somente visuais em que utilizo a hierarquia ano-mes-dia são filtrados por essa função. Se utilizo a coluna "mês/ano" a fórmula retorna blank.

Veja na imagem abaixo a diferença entre as duas tabelas. Por que o filtro funciona diferente pra "mês/ano"?
TotalSales
TotalSales 2018

Perceba que na tabela da esquerda os valores aparecem, enquanto que na da direita não.

Isso acontece pelo seguinte; antes de mais nada é importante lembrar que cada datapoint, ou seja, cada ponto de cálculo é calculado independentemente, ou seja, cada célula é calculada independentemente e cada uma tem seu próprio contexto de filtro.

Antes de seguirmos, lembrem-se de que quando passamos filtros tipo “dCalendario[Ano] = 2018” o que na verdade o DAX interpreta é “FILTER( ALL(dCalendário[Ano]), dCalendário[Ano] = 2018”, logo a fórmula completa seria:

Sabendo disso, ao calcular o valor para coluna ano 2020 TotalSales o resultado é 10.450, enquanto que nessa mesma linha para TotalSales 2018 o valor é 12.174.

Tabela da Esquerda

O que acontece é que, TotalSales 2018 tem um filtro na coluna dCalendario[Ano] , dessa forma, no contexto de filtro onde existe dCalendario[Ano] sofrerá interferência da função ALL(), e o que ALL() faz? Remove todo e qualquer contexto de filtro no report substituindo pela expressão que ele aplica, no nosso caso, dCalendario[Ano] = 2018, como na imagem abaixo.

Pra ser mais claro, quando TotalSales 2018 estiver sendo executada, ela vai remover o filtro de ano de 2020 e colocar 2018.

Tecnicamente falando, ao executar TotalSales temos esse comportamento:

dCalendario[Ano] = 2020
     && (dCalendario[mesabrev] = "Jan" ||
         dCalendario[mesabrev] = "Fev" ||
         dCalendario[mesabrev] = "Mar" ||
         dCalendario[mesabrev] = "Abr" … etc)

Enquanto que, ao executar TotalSales2018, temos esse:

dCalendario[Ano] = 2018
     && (dCalendario[mesabrev] = "Jan" ||
         dCalendario[mesabrev] = "Fev" ||
         dCalendario[mesabrev] = "Mar" ||
         dCalendario[mesabrev] = "Abr" … etc)

Ou seja, como não há filtro sendo alterado por ALL() ou CALCULATE() os campos de dCalendário[mesabrev] se mantém estáveis, obedecendo a hierarquia recém alterada pela CALCULATE() em TotalSales2018.

Tabela da Direita

Na tabela da direita nós não temos nenhum campo sendo alterado pela CALCULATE(), logo o contexto de filtro permanece imutável. Dessa forma, quando TotalSales2018 é aplicada no contexto da tabela da direita, nenhum dado é retornado, pois não existem dados visíveis naquele contexto.

Qual seria o contexto, exatamente, nessa tabela da direita?

Bem, o contexto …

Valor Total

Vamos explicar primeiro o Total, perceba que o valor total é exibido, 12.174.

O Total aparece porque ali o contexto é simplesmente o valor total da medida, que no caso de TotalSales2018 é toda tabela de Sales, filtrada pelo FILTER() da calculate, que diz dCalendario[Ano] = 2018. Então o DAX vai pegar toda tabela de Sales, vai filtrar = 2018, vai pegar o resultado do SUMX e vai retornar no datapoint referente ao Total. Ou seja, no datapoint de Total não existem filtros externos sendo aplicados (que no caso seriam o slicer 2020 e o eixo da tabela na coluna mes/ano).

Valores nas linhas da tabela

Porém, como cada datapoint é calculado de maneira independente, como já falamos, ao tentar recuperar o valor para o datapoint referente a primeira linha, Jan/2020, o que o DAX vai fazer descrevo logo abaixo, porém é importante entender que, esse acesso de verificação, filtragem e retorno da informação é executado ao mesmo tempo, de forma concomitante, simultaneamente.

  • acessar a tabela SALES,
  • verificar os filtros externos, no caso dCalendário[Ano] = 2020, porque temos um slicer na tela,
  • e também já vai estar filtrada no eixo da tabela por Jan/2020, no caso dCalendario[Mes/Ano] = “Jan/2020”,
  • vai então aplicar o filtro de dCalendario[Ano] = 2018
  • vai pegar o resultado e aplicar o SUM (resultado da medida TotalSales2018, que é invocada no CALCULATE() )

O resultado da medida TotalSales2018 vai dar blank, porque não existe nenhuma linha na nossa base de dados que possa ser filtrada por Jan/2020 e ano 2018 ao mesmo tempo. Pense numa planilha de excel.

Inicialmente temos toda base de dados, toda tabela:

Daí ao considerarmos os filtros externos, temos Ano = 2020 no slicer que já reduz pra:

Daí o visual da tabela da direita, ao utilizar o campo dCalendario[mes/ano] em seu eixo aplica, automaticamente, um filtro na coluna de mes/ano. Considerando a primeira linha de jan/2020, o que vai acontecer na nossa base de dados é que ela vai ser filtrada para aquele datapoint.

essa é a visão do datapoint Jan/2020

nota: ignorem o valor das imagens do excel, eu mudei depois que já tinha tirado todos prints do power bi, aí ia dar maior trabalhão…

É esse o resultado que está sendo visto nesse momento pelo DAX para as linhas destacadas da tabela da direita:

Mas por que esse contexto se aplica também para coluna onde está TotalSales2018?

Bem, o contexto de filtro inicial é definido pelos elementos externos, e depois aplicados às medidas (não exatamente depois, ocorre em tempo de execução, mas pensa assim pra facilitar por agora), no entanto, usando CALCULATE() podemos alterar o contexto de filtro em vigência nos datapoints desejados.

Lembrando, nosso contexto de filtro atual é: dCalendario[Ano] = 2020 e mais o eixo da tabela.

Portanto, se quisermos mesmo trazer o valor de TotalSales2018 na tabela da direita, o que precisamos fazer é uma medida assim:

Veja o resultado da tabela da direita, usando a medida que criamos:

Por que essa nova medida funciona?

TotalSales2018mes/ano funciona pelo seguinte, quando utilizamos ALL() o contexto de filtro vigente que existe na tabela dCalendário é removido, então o contexto de filtro do slicer que diz ano 2020 é removido e o contexto de filtro imposto pelo eixo mes/ano da tabela também é removido.

Ao mesmo tempo o filtro de VALUES( dCalendario[mes abrev] ) é verificado.
Nesse caso aqui, VALUES() vai retornar uma tabela, de valores únicos dessa coluna mes abrev. Que no caso, pra primeira linha da tabela da direita, vai ser JAN, ou seja, uma tabela de uma linha e uma coluna.

Nesse momento existe um detalhe importante. Por que usamos [mes abrev] e não [mes/ano]?

Usamos mes abrev por ser uma coluna relacionada, mas que funciona como um “coringa”, porque JAN existe para vários anos, enquanto que, se utilizarmos mes/ano vamos, obrigatoriamente, retornar na nossa VALUES() o valor de Jan/2020.

Aí você se pergunta, mas eu não passei ALL() antes? Por que então VALUES() simplesmente não retorna todos VALUES() da dCalendário[mes abrev]?

Respondo… Porque VALUES() não sabe que existe ALL(), porque elas acontecem ao mesmo tempo! Dessa forma, quando VALUES() acontecer, o contexto de filtro do slicer no nosso report e do eixo da tabela ainda existem! Logo, se utilizarmos VALUES() em mes/ano o resultado será sempre Jan/2020, e é por isso que utilizamos o coringa [mes abrev], porque dessa forma vamos ter o resultado como JAN.

Esse resultado, JAN, assim como todos resultados dos outros demais filtros da CALCULATE(), serão armazenados pra depois que todos filtros forem avaliados acontecer a interseção e verificar a tabela resultado e depois aplicar a medida de CALCULATE() sobre essa tabela resultante.

ALL() só está removendo o contexto de filtro para a expressão da própria, porém VALUES() ainda sofre pelo contexto de filtro atual, de forma que, se utilizarmos mes/ano na nossa medida TotalSales2018mes/ano o resultado não daria certo.

resultado errado usando mes/ano em VALUES()

Isso acontece porque, como o contexto de filtro ainda está lá, usando VALUES() com mes/ano o valor resultante da VALUE() será Jan/2020 e Jan/2020 é sempre Jan/2020, mesmo que você remova todos filtros da galáxia, Jan/2020 será sempre Jan/2020 e nunca existirá nenhum resultado em 2018 pra nenhum mês de 2018 onde Jan/2020 possa gerar algum resultado. Isso é o que chamamos de interseção vazia. Por esse motivo é que utilizamos [mes abrev] em VALUES(), pois mes abrev, considerando o contexto de filtro atual, irá retornar JAN, e JAN sim existe em todos os anos!

Pense assim, se você usar VALUES() em mes/ano você vai ter como resultado uma tabela de uma coluna e uma linha, que depois será utilizada na interseção, e o resultado dessa tabela será Jan/2020, conforme mostro na parte esquerda da imagem abaixo.
Enquanto que, se você fizer a mesma coisa, mas utilizando [mes abrev] o resultado será Jan.

Perceba na imagem abaixo que, embora o resultado de VALUES() venham do mesmo contexto, em um o resultado é Jan/2020 e na outra é Jan. Portanto lembre-se, todo filtro é uma tabela! Essas tabelas depois serão utilizadas na interseção, como já expliquei acima.

O último argumento, dCalendario[Ano] = 2018, vai criar a mesma coisa, uma tabela de uma coluna e uma linha com o valor 2018. Esse valor depois será avaliado com os valores de todos outros filtro pra gerar a interseção, que vai gerar a tabela resultado, que depois será utilizada pra calcular a medida!

Conclusão

Juntando tudo, o que acontece é que:

  • com ALL() em dCalendario as colunas da tabela vão ignorar o contexto de filtro e ficar sem nenhum filtro
  • um filtro, que nada mais é do que uma tabela, será criada com o resultado da coluna dCalendario[mes abrev] recebendo JAN
  • um filtro, que nada mais é do que uma tabela, será criada com o resultado da coluna dCalendario[ano] recebe 2018

E essa avaliação de contexto de filtro que descrevi acima ocorre pra cada datapoint, pra cada célula, linha e coluna da tabela. Portanto, na nossa primeira linha Jan/2020 na coluna TotalSales2018mes/ano o valor resultante será o de Jan para 2018!

Pronto, vencemos o contexto de filtro do DAX! Obrigado pra você que ficou até aqui!!!

Power BI DAX.do

Se você quer testar códigos DAX pelo celular, sem precisar do Power BI, ou se você quiser testar código sem precisar de criar uma base de dados ou modelo, ou se ainda você quer simplesmente rodar códigos que encontra no site do SQLBI.com, pra tudo isso você pode usar o DAX.do.

Esse é um novo site do pessoal do SQLBI.com e é uma ferramenta pra ajudar as pessoas a aprenderem e ensinarem DAX.

No DAX.do você já tem um modelo de dados prontinho, na verdade dois modelos, um do Contoso Database e outro do DAX Guide, que é o modelo de dados utilizado pra exemplos e testes do outro site desse mesmo pessoal, o DAX.guide.

Na verdade o DAX.do vem como uma ferramenta pra facilitar o uso do site deles, agora que todos exemplos de seus artigos podem ser facilmente executados no DAX.do

Mão na massa

O DAX.do é bem fácil de usar, mas você precisa entender que todos resultados precisam ser uma tabela. Portanto, se quiser ver o resultado de uma medida simples, como

SUM( Sales[Net Price] )

não será possível, porque esse código não retorna uma tabela. No entanto, se você tentar

EVALUATE
{ SUM ( Sales[Net Price] ) }

o código vai rodar.

Se você precisar entender melhor qual sintaxe você precisa usar no DAX.do dê uma lida em EVALUATE().

Examplo de código que testei hoje 🙂

Conheça

Referências

https://www.sqlbi.com/blog/marco/2021/02/17/introducing-dax-do/

Power BI DAX Time Intelligence

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.

Highlights

  • colunas calculadas são calculadas a nível de dados, enquanto que medidas são calculadas a nível de relatório, portanto, nada que seja feito no relatório irá alterar o resultado do que foi calculado nas colunas calculadas.
  • colunas calculadas são contexto de linha, enquanto medidas são contexto de filtro
  • todas medidas aplicam, implicitamente, calculate()
  • todas medidas transformam row context em filter context

Time Intelligence

  • funções DAX de time intel não criam datas, apenas andam pra frente ou para trás em datas já existentes na tabela de data
  • funções DAX de time intel não se aplicam a células (rows), apenas a filtros (tabelas)
  • funções de time intel sempre trabalham a nível de data
  • funções de time intel removem os filtros existentes no contexto para conseguirem calcular seus resultados adequadamente (melhor explicado ao longo do texto) (new filters overwrite the old filters)
  • funções de time intel funcionam apenas em datas regulares (ano, trimestre, mês, dia). Não funciona em granularidades irregulares, como semana, por ex.
  • funções de time intel funcionam apenas em calendários regulares.

Funções de time intel vem do MDX, logo, são funções de manipulação de filtros (filter context, filter manipulation).

Importante entender que funções de time intel manipulam filtros, e todos filtros são tabelas, logo funções de time intel manipulam tabelas. Elas também não conseguem produzir novas datas, não é possível utilizar a função Dateadd() para criar uma data aleatória que não exista na sua tabela de data, portanto, para que Dateadd() funcione é preciso que, antes, essa data que você quer atingir já exista dentro da sua tabela de data. Logo, perceba que as funções de inteligência de tempo não vão criar uma data nova, mas sim “shiftar”, ir pra frente ou pra trás, dentro das suas datas já existentes na sua tabela de data.

No exemplo da imagem acima, a expressão DAX cria, com summarize(), uma tabela de duas colunas. Pense em Summarize() como um group by, criando uma tabela de duas colunas, trazendo resultados únicos desses dois campos. Na sequência utiliza addcolumns() para adicionar mais uma coluna, chamada LastYearSales, que é o resultado de uma expressão calculate() que retorna a soma de Sales Amount no mesmo período do ano anterior, usando sameperiodlastyear().

O objetivo com essa medida é trazer, pra cada registro da tabela criada, que foi nossa tabela de duas colunas, Year e Quarter, o valor de quanto foi vendido no ano anterior para cada linha dessa tabela.

Addcolumns() é uma função iteradora, ela vai linha a linha executando o código. Então a tabela é criada com summarize() no row context, e depois, quando é a hora de executar calculate() o contexto é transformado em contexto de filtro.

Quando sameperiodlastyear() é executado uma lista de datas será retornada de acordo com a tabela que temos, nesse caso uma lista de datas referente ao Quarter 2 de 2012, que vai de 01/04/2012 a 30/06/2012. Lembrando que esse ciclo de cálculo se repetirá linha a linha. Outro ponto importante é saber que funções de time intelligence sempre funcionam a nível de data, e mesmo que nesse caso a granularidade seja de Ano e Quarter, o retorno da função de time intelligence será uma lista de datas.

Uma vez que o filtro da função de time intel é adicionado ao contexto do dax, o filtro anterior é removido do contexto, isso porque nenhum registro, nenhuma linha, nenhum resultado sobreviveria a um filtro como esse, onde o ano é 2013 e 2012 ao mesmo tempo. Por causa disso, o contexto anterior é removido e o cálculo é feito com base na nova lista de datas. Depois disso é verificado quais linhas de Sales Amount sobrevivem a esse filtro, aquelas que forem selecionadas serão então agregadas, nesse caso em SUM(). Somente depois de agregada, será inserida de volta no contexto original.

Filtros e Time Intelligence

Uma das partes mais importantes é entender como as funções de time intel aplicam seus filtros. Isso porque esses filtros são os responsáveis por garantir o bom funcionamento, e o resultado de função de time intel. Por isso, saiba que:

  • toda função de time intel aplica, implicitamente, um all() em toda tabela de data

Ou seja, toda vez que você chama uma função de time intel ela aplica implicitamente um All( Date ), e faz isso pra garantir que o filtro do contexto atual não atrapalhe na hora de conseguir a lista de datas necessárias para executar a função de time intel. Por isso, é importante sempre garantir que sua dimensão dCalendário, no caso desse post estamos chamando de Date, esteja sempre marcada como date-table, pois dessa forma, quando a função de time intel for aplicar o ALL() ela saberá qual tabela filtrar!

Vamos analisar a medida acima. Perceba que o argumento que passamos na sameperiodlastyear() é apenas Date[Date], ou seja, apenas a coluna de date. Sabendo que toda medida trabalha em filter context e que todo filtro é uma tabela, esse filtro então representa uma tabela de uma única coluna, Date[Date]. No entanto, no nosso exemplo não existe nenhum filtro sobre a coluna de Date[Date], na verdade, os filtros no contexto atual são sobre Year e Quarter. Portanto, se a função de time intel não aplicasse o All( Date ) ele jamais seria capaz de retornar a lista de datas que precisamos. No nosso contexto o ano é 2013 e o quarter 2, e precisamos pegar o sameperiodlastyear() desse período, porém, como nosso contexto aplica filtro sobre Year e Quarter, por mais que fosse passado um Calculate() com Filter em Date[Date] o filtro não seria desfeito, pois as colunas filtradas são Year e Quarter, o que impossibilitaria para o DAX retornar a lista de Datas que precisamos, que vai de 01/04/2012 a 30/06/2012. Para prevenir esse comportamento, toda função de time intel aplica All( Date ), dessa forma desfaz, remove o filtro do contexto inicial, abrindo toda tabela, dessa forma agora tem condições de encontrar a lista de datas que precisa.

Porém, para garantir que esse comportamento aconteça o DAX precisa de algumas condições, sendo:

  • uma tabela de datas com datas únicas e com anos completos, de 01/01 a 31/12, sem gaps, sem falhas, na lista de datas
  • a coluna de data deve ser do tipo date ou datetime
  • a tabela de data deve ser, preferencialmente, marcada como date-table
  • caso sua tabela de data não faça relacionamento usando a tabela de data e sim um id de data (campo numérico) sua tabela de data deve ser obrigatoriamente marcada como data-table, ou então não será possível realizar cálculos com funções de time intel
  • caso sua tabela de data faça relacionamento usando o campo date, isso ajuda a indicar pra o DAX de que aquela é a tabela a ser utilizada pelas funções de time intel
  • caso sua data contenha time (datetime) o valor do tempo deve ser sempre o mesmo entre todas linhas de date[date]

Caso você não tenha uma tabela marcada como date-table e o Power BI não consiga identificar através dos relacionamentos, ou de suas tabelas de datas escondidas (assunto pra outro post), o resultado da sua função de time intel será obrigatoriamente incorreto. Para contornar isso, garanta que você passe em sua medida um ALL( Date ), para ignorar quaisquer filtros na tabela de data e conseguir recuperar a lista de datas desejada.

Referências

https://youtu.be/8v4eqssS4a4 – Learn Dax Part II by Jeffrey Wang

https://dax.guide/sameperiodlastyear/

Conversa com Fred, o cara é fera mesmo =)

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).

geoprahy table

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

Power BI DAX Eixo personalizado de data

clique para ampliar

Como criar um eixo de data, separado por semana, de forma que os itens não se agrupem, não se agreguem, pela data?

Meu cenário aqui é redes sociais, eu preciso ver as métricas de posts, por semana e por dia, porém em um mesmo dia vários posts são feitos e eu não posso agrupar todos eles no mesmo dia, preciso, de alguma forma, separar cada post mesmo eles estando no mesmo dia.

Inicialmente eu resolvi isso colocando um id pro post por dia, de forma que seria possível identificar se aquele era o post 1, ou 2, ou 3, ou 4, do dia. E aí vim com essa solução:

clique para ampliar

Nessa solução eu uso 3 campos no eixo, o de semana, a data em si e o identificador do post. Resolve, mas… e se eu quiser usar apenas 2 eixos (como solicitado pelo cliente)?

Pra isso precisei criar 2 colunas calculadas. Que chamei de EIXO e EIXO2.

eixo
eixo 2

Na EIXO, a coluna é do tipo TEXTO e não tem nada de mais nela. Na verdade essa coluna só vai servir pra ordenarmos a EIXO 2.

Na EIXO 2, a coluna é do tipo DATE, com formatação custom para dd.mm, e ela é ordenada por (sort by column) pela EIXO.

eixo 2

Dessa forma, utilizando a EIXO 2, foi possível atender exatamente a necessidade do cliente.

Então, se precisar criar um eixo customizado, não esqueça desses recursos.

Rápida retrospectiva de 2020

Já trabalho com análise de dados e construção de datamarts e reports há 10 anos, mas meu trabalho incluia, além da parte de dados, uma grande quantidade de tempo dedicado para automação, processos e projetos.

Em 2020, pouco antes do começo da pandemia, resolvi que focaria 100% do meu tempo em me especializar somente na parte de dados. Foi o que eu fiz. Comecei estudando, estudei muito. Esse ano foram 5 cursos, e mais um certificado Microsoft.

Cursos

  • Formação Microsoft Power BI, 14h, Raizzer.com, 05/2020
  • Formação BI Academy, 120h, Raizzer.com, 06/2020
  • Curso Express de Power BVI, 13h, Leo Karpinski, 07/2020
  • Analista de Dados Essensial, 21h, Vithor Silva, 07/2020
  • Introducing DAX Video Course, 3h, SQLBi.com, 10/2020

Certificados

Mudei de emprego, agora trabalho 100% do meu tempo com Power BI. Para 2021 o objetivo é me tornar referência em DAX.

Feliz ano novo e boas festas, com saúde para todos!

Bootcamp de Power BI Segunda Edição

Fala aí pessoal, beleza!? Não sumi não, hein, rs! Estou por aqui, trabalhando muito e com preguiça pra caramba de escrever no blog, mas já tenho algumas coisas preparadas, pra 2021 o foco vai ser somente DAX!

Enquanto 2021 não chega, vamos de #bootcampPowerBi segunda edição!

A primeira missão já está entregue e você pode conferir meu post sobre ela aqui: https://www.linkedin.com/feed/update/urn:li:activity:6741564296418910208/.

Tem um monte de curso fazendo bootcampo de Power BI. Esse que eu faço é o do Leonardo Karpinski e é muito legal de fazer, porque te desafia a aprender rápido, tem prazo de entrega curto e tudo mais, é tipo vida real.

Eu não posso dar detalhes do que foi solicitado, nem passar a base de dados, por questão de propriedade intelectual do Leo, mas abaixo o dash que criei em Power BI pra entregar a missão#01. Ainda faltam mais duas missões e depois vem a segunda fase, que será em grupo!

Passei!!! Fui aprovado no Exam DA-100: Analyzing Data with Microsoft Power BI

Fala pessoal!!! Notícia boa, passei no exame! Depois de estudar por vários meses, fiz o DA-100: Analyzing Data with Microsoft Power BI nesse sábado agora, dia 14/11/2020 e passei!

Minha opinião

Sinceramente, achei a prova bastante cansativa, com enunciados bastante longos. Dá pra ver que os enunciados são propositalmente feitos para que diferentes respostas, ou conceitos de respostas, possam ser extraídos da mesma pergunta. Isso acontece também nas perguntas “cenário”, que são aquelas perguntas que depois de responder você não consegue voltar e revisar.

Falando nisso, minha prova foi dividida em 48 questões iniciais, sem conexão uma com a outra. Depois de terminar as 48 você dava um YES lá pra passar pra próxima fase da prova, que consistia de perguntas com cenários, ou seja, vinha um cenário gigante e mais 4 perguntas pra cada cenário. No total eu respondi 3 cenários, com 4 perguntas cada.

Falando a respeito de revisar perguntas, eu não revisei nenhuma pergunta, isso porque eu preferi optar pela estratégia do “se eu acho que é, então é”, e deu certo, consegui 912 pontos em 1000, quase fechei a prova. Portanto, se você já cansou de estudar e domina bem os conceitos e conhece Power BI, confie em você mesmo.

A prova tem 3 horas de duração. Eu terminei faltando ainda 56 min pra terminar, então tem tempo mais que de sobra.

Comportamento durante a prova

Fiz a prova de casa mesmo, tem essa opção. Pra conseguir fazer a prova de casa você tem que seguir uma série de regras, fiquem atentos a isso se quiserem fazer a prova de casa.

Uma das partes mais difíceis da prova é justamente essa. Você está o tempo todo, 100% do tempo, sendo filmado. Sua camera e microfone precisam estar abertos e você não pode beber água, não pode sair da cadeira, nem cobrir sua boca com as mãos, nem falar nada… Isso pra mim foi muito difícil, porque eu toda hora fico coçando nariz, orelha, olho, kkkkkkkk, então precisei me conter bastante.

Pontos importantes que caíram na prova

É muito difícil de lembrar as perguntas, porque são muitas, são longas e também porque contratualmente quem faz a prova não pode falar o que caiu, mas todos os pontos que estudei caíram e tudo que estava na prova eu tinha estudado.

De qualquer forma, eu recomendo estar bastante preparado para questões de:

  • RLS – Row Level Security, todo processo de RLS, desde a criação e utilização do modelo estático e dinâmico, concessão de permissões no PBI Service, etc.
  • Modelagem de Dados, todos os pontos, caiu muita coisa de Modelagem. Pode estudar bem tudo de “Model the Data”
  • Permissões de Workspace, caiu bastante coisa de quem pode fazer oque aonde, nível de acesso, etc
  • Muito data profiling
  • Algumas perguntinhas bem difíceis sobre AI Visuals (key influencers, decomposition tree)

Conclusão

Pessoal, pra todos que estejam interessados em tirar essa certificação, tudo que está aqui no Blog é relevante. Principalmente tudo que está escrito, os links e referências que coloquei aqui https://powerbixpert.com/power-bi-da-100-certification/.

Espero que meus posts possam ajudar vocês. Em todos eles tem as referências que usei e, na dúvida, usem o material oficial da Microsoft.

Ah, e o blog continua, claro, daqui pra frente deve vir bastante coisa de Power BI por aí.

Power BI Data Lineage

Como vocês sabem, eu estou fazendo uns testes pela internet afora pra testar meus conhecimentos em Power BI e vou aproveitar pra escrever um pouco sobre algumas perguntas que me chamaram atenção.

Link para os testes: https://www.learndatainsights.com/power-bi-quiz-da-100/power-bi-quiz-da-100-deploy-deliverables/

A Pergunta

A pergunta foi a seguinte: “Quais dessas ações podem ser realizadas usando a view de Data Lineage do Power BI?”. Resposta na imagem abaixo.

Data Lineage é uma visão no Power BI Service (app.powerbi.com) que mostra o relacionamento entre todos itens de uma Workspace. A imagem abaixo é da minha própria workspace, numa conta FREE (apesar da Microsoft dizer no site deles de que Data Lineage requer PRO).

Pra ativar essa visão é só ir na workspace e mudar ali no lugar de view pra Lineage (view, ali do lado do search, na direita da imagem).

Minhas considerações

A primeira opção fala da notificação dos contatos das pessoas relacionadas nas listas de contato de workspaces com reports que usem algum dataset compartilhado como fonte de dados. Isso quer dizer que, caso seja necessário fazer algum ajuste num datasource, seja ele um dataset compartilhado ou um datasource original (como um SQL, por exemplo) é possível sim notificar todas as pessoas impactadas em todas workspaces que usam esse datasource, conforme ilustra imagem abaixo.

  • Notificar contatos não estará disponível se o dataset no qual você está realizando a análise de impacto estiver localizado em uma workspace clássica.
  • Métricas de uso não são compatíveis com workspaces clássicas ou personal workspaces.

A segunda opção fala de “ver o número e lista de tabelas contidas dentro de um dataset ou dataflow”. Sim, também é possível. Nesse caso basta selecionar o dataset desejado, clicando no nome do dataset, e daí serão exibidas as tabelas e o número delas. Como na imagem abaixo, temos 8 tabelas.

Terceira opção, open report, simples, só clicar no report e clicar em open ou clicar na setinha.

Quarta opção, navegar pra settings de um dataset de qualquer dataset, que também é possível. No Data Lineage os datasets, na verdade quase todos artefatos presentes num Data Lineage, tem esse dropdown menu de opções. Veja na imagem abaixo.

Referências

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-data-lineage
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-dataset-impact-analysis

Power BI Query Reference

Como vocês sabem, eu estou fazendo uns testes pela internet afora pra testar meus conhecimentos em Power BI e vou aproveitar pra escrever um pouco sobre algumas perguntas que me chamaram atenção.

Link para os testes: https://www.learndatainsights.com/power-bi-quiz-da-100/power-bi-da100-exam-quiz-prepare-the-data-da-100/

A Pergunta

A pergunta foi a seguinte: “separar uma query em várias usando REFERENCE melhora a performance?”. Resposta na imagem abaixo.

Minhas considerações

Essa pergunta é super simples e básica, mas requer atenção. Quando você fala de REFERENCE ou DUPLICATE de uma query você tem alguns pontos a considerar.

DUPLICATE vai aumentar o tamanho do seu modelo, REFERENCE por outro lado, não. No entanto, REFERENCE não vai deixar seu modelo mais performático, pelo contrário.

O que acontece quando você usa Referencia é o seguinte (segundo a Microsoft, ver link nas referencias): “Quando uma consulta referencia uma segunda consulta, é como se as etapas da segunda consulta fossem combinadas e executadas antes das etapas da primeira consulta“.

Na prática, é o seguinte, imagina que você tem a Query1 e criar 3 outras queries usando a Query1 como Referencia. Seu modelo vai ficar como na imagem abaixo:

Diagram showing the Query Dependencies view, displaying queries described in the previous paragraph.

Quando você der Refresh (imagina um modelo Import) o que vai acontecer é que a Query1 vai rodar 3 vezes… Uma vez ela roda e depois roda a Query2, depois roda ela denovo e na sequencia a Query3, depois roda a Query1 novamente e depois a Query4. A imagem abaixo ilustra esse comportamento.

Diagram showing a modified version of the Query Dependencies view, displaying Query 2, Query 3, and Query 4.

Ponto importante sobre Reference: você não pode usar resultados da query referenciada na query original, ou seja, resultados da Query2 não podem ser usados na Query1, porque isso criaria um problema de “circular reference”.

Referências

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries
https://radacad.com/reference-vs-duplicate-in-power-bi-power-query-back-to-basics

Power BI Edit Queries Parameters

Como vocês sabem, eu estou fazendo uns testes pela internet afora pra testar meus conhecimentos em Power BI e vou aproveitar pra escrever um pouco sobre algumas perguntas que me chamaram atenção.

Link para os testes: https://www.learndatainsights.com/power-bi-quiz-da-100/power-bi-da100-exam-quiz-prepare-the-data-da-100/

A Pergunta

A pergunta foi a seguinte: “quais são alguns dos casos para utilização de parâmetros dentro do Edit Queries?”. Resposta na imagem abaixo.

Minhas considerações

De cara descartamos a primeira opção, porque ela diz que “usados dentro das variáveis do DAX”, e parâmetros do Edit Queries (Power Query) não são usados com DAX, e sim com M.

A segunda opção é um dos casos de uso mais comuns de parâmetros, pra definir uma string de conexão junto ao datasource, explico melhor aqui.

Terceira opção é uma pegadinha, porque existe parâmetros para what-if, mas não são os mesmos parâmetros que fala no enunciado da questão, que diz “edit queries parameters”.

Na quarta opção temos de que Edit Query Parameters, ou parâmetros do Power Query, são utilizados para filtrar dados do datasource e esse é realmente um dos principais usos de parâmetros do Power Query, criar valores, listas, para que possamos filtrar de uma forma mais organizada nossos dados.

Quinta opção, que eu errei em não marcar, diz que podemos substituir valores conforme especificados pelos parâmetros. O conceito geral para esse tipo de parâmetro, segundo a Microsoft, é “A parameter serves as a way to easily store and manage a value that can be reused”, ou seja, uma forma fácil de armazenar algum valor que pode ser reutilizado.

Sobre utilizar parâmetros pra dar “replace values” eu nunca tinha utilizado nem parado pra pensar muito nisso, mas faz todo sentindo. Inclusive, no Power Query, sempre que você tem uma telinha como a da imagem abaixo, perceba que você tem sempre essa opção de selecionar o dropdown, nesse caso “ABC”, e selecionar se quer manter o ABC Text ou se quer usar Parâmetros.

Se a tela acima não estiver aparecendo pra você dessa mesma forma, com esse dropdown, é porque você precisa ativar o checkbox da imagem abaixo.

Referências

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
https://powerbixpert.com/2020/07/06/power-bi-query-parameters/

Power BI Assume Referential Integrity

Como vocês sabem, eu estou fazendo uns testes pela internet afora pra testar meus conhecimentos em Power BI e vou aproveitar pra escrever um pouco sobre algumas perguntas que me chamaram atenção.

Link para os testes: https://www.udemy.com/course/exam-70-778-microsoft-power-bi-practice-test-2019/

A Pergunta

A pergunta foi a seguinte: “usando Get Data, o que faz a opção de marcar “Assume Referential Integrity”?”.

Minhas considerações

Eu já estudei bastante essa parte de modelagem do Power BI, mas na hora de responder essa pergunta eu fiquei em dúvida entre a última e a terceira opção. Antes de explicar esse ponto da minha dúvida, deixa eu repassar os outros pontos que, pra mim, são mais óbvios.

Screenshot of an Edit Relationship dialog to select Assume referential integrity.
switch, checkbox, caixinha, pra marcar “assume referential integrity”
  • Primeira opção diz que as queries rodam mais eficiente no data source, sim, é verdade, porque você passa de OUTER JOIN pra INNER JOIN, então você garante que tudo que está num lado será encontrado no outro (pensa em duas tabelas se relacionando).
  • Segunda opção diz não é suportado em fontes de dados que nao sejam SQL Server e Azure SQL. Errado. Qualquer base que rode como Direct Query pode usar essa função de Assume Referential Integrity.
  • Quarta opção, sim, só funciona com Direct Query.
  • Quinta opção, já expliquei sobre isso no ponto um.
  • Terceira e Sexta opção, vou comentar mais pra frente.

Sobre o ponto 6, que diz “quando houver problemas com a Referential Integrity nos dados isso irá resultar em query error”. O que na verdade acontece é você até pode habilitar Referential Integrity num relacionamento onde ela não existe, e isso não vai dar nenhum erro. Na verdade o relacionamento vai continuar funcionando, porém os dados exibidos serão afetados, ou seja, haverá inconsistência nos dados apresentados no seu relatório. Por exemplo, considere o relacionamento da imagem abaixo.

Screenshot of Orders table and Depots table.

Só pra ser claro, em casos como o da imagem acima, você não deve habilitar Referential Integrity, mas caso ela esteja habilitada, o que vai acontecer é:

  • Se você tiver um visual mostrando apenas a soma de QTY da tabela ORDERS, o resultado vai ser 40.
  • Agora, se você adicionar nesse visual um campo da tabela DEPOTS, daí esse resultado vai mudar pra 30. Isso porque a linha de 10 que é NULL é removida do relacionamento pela Referential Integrity.

Sobre o ponto 3, que diz “num relacionamento one-to-many (1:N), o lado MANY não pode ter blanks e todos valores no lado ONE tem que ter um valor correspondente no lado MANY”. Isso está certíssimo, na verdade essa é a definição conceitual de Referential Integrity, ou seja, ao ativar/habilitar a Referential Integrity você na verdade está dizendo pro Power BI assim “pode ativar, eu sei que todos meus registros do lado ONE tem valores no lado MANY”.

Referências

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity