DAX – Funções Tabulares

INTRODUÇÃO

Na revisão anterior falei sobre os fundamentos da linguagem DAX, seu funcionamento dentro do PowerBI, como ele se relaciona com as tabelas e sua estrutura básica, no caso, os inputs que utilizamos para manipular dados.

Como a linguagem é capaz de aceitar e retornar tanto valores tabulares, tanto valores lineares, vamos ver neste post como e quais as limitações encontradas ao manipular tabelas dentro das expressões de análise de dados.

ÍNDICE:

1. INTRODUÇÃO
2. FUNÇÕES TABULARES – FILTER
3. ALL, VALUES E DISTINCT
4. ALLEXCEPT – UM BREVE RESUMO E EXEMPLO
5. LIMITAÇÃO NAS FUNÇÕES TABULARES
6. VALUES & DISTINCT
7. COMO DETECTAR ANOMALIAS COM VALUES E DISTINCT
8. CONCLUSÃO

FUNÇÕES TABULARES – FILTER

As funções tabulares são utilizadas para facilitar a manipulação de tabelas dentro do PowerBI puxando um grande volume de dados para a expressão que criaremos. Por utilizar esse grande conjunto de dados com diversas colunas, temos de tomar muito cuidado com as análises que iremos criar.

E qual a justificativa para essa precaução ser importante? Como DAX tem a facilidade de trabalhar tanto com valores escalares (linha única) quanto tabulares, algumas funções como as de agregação não aceitarão tabelas como parâmetro; pedirão apenas uma coluna.

Quando se trata de funções como Filter, All(variações), Addcolumn, Values e etc. Solicitarão uma tabela para iterar e daí, retornar o valor após determinada ação dentro da coluna passada.

Veja o exemplo abaixo ao qual crio uma medida que calcula o total das vendas com pedidos maior do que 3 unidades.

Perceba que na variável FILTERQTD utilizo a função Filter passando a tabela ‘Sales’ e filtrando pela coluna ‘Quantity’. Além disso, posso utilizá-la na Sumx como primeiro parâmetro, criando minha medida.

Tenha cuidado quando for manipular diferentes tabelas na hora de criar sua expressão, existem algumas limitações devido a cardinalidade  na relação entre elas.

O bloco de expressão abaixo ilustra essa situação com a medida criada para filtrar as vendas de produtos da cor vermelha. 

Note que a minha tabela fonte foi a ‘Sales’ e utilizei a função Related para buscar na ‘Product’ a cor vermelha. O contrário não seria permitido.

Tanto a primeira quanto a segunda retornam erros quando aplicadas à Return. Sendo que a expressão da variável FLTRED acusa falha no relacionamento antes mesmo da conclusão. 

Quando aplicadas ao gráfico de matriz.

DICA: Quando for trabalhar com grandes volumes de dados dentro de um filtro qualquer, utilize  a expressão mais complexa do código na função interna para otimizar performance.

Exemplo: Vamos supor que haja a necessidade de criar uma tabela com os produtos que possuem o preço unitário três vezes maior que o custo. Poderíamos utilizar os seguintes modelos: 

Ambas chegarão ao mesmo resultado, porém, a primeira possui melhor performance quando o volume de dados é muito grande. Como o PowerBI processa o filtro mais interno primeiro (veremos mais a frente com calculate), esta construção é muito mais produtiva.

Conceituando: podemos dizer que Filter, é tanto tabular quanto iteradora, uma vez que age nas linhas da tabela, capturando os valores que satisfaçam sua condição no segundo parâmetro. 

Lembre-se de que ela não retorna valor escalar, logo, tenha atenção quando for utilizá-la dentro de uma medida. Para evitar erros, trabalhe com variáveis, vide imagem abaixo:

Percebam que primeiro criei uma table expression (ocorre quando uma tabela é definida por uma função sem ter uma forma física) e à atribuí a minha Sumx. Criando a medida.

ALL, VALUES E DISTINCT

Avançando no assunto vamos ver três outras formas de manipular os dados tabulados e mais, como podemos criar melhores análises com estes recursos do grupo.

Todas as três aceitam tanto tabela quanto coluna em seus parâmetros sendo a ALL capaz de receber mais de uma coluna por vez e removendo os filtros quando aplicada em uma medida. Vale destacar que quando utilizadas, retornam apenas os valores visíveis sendo a função Values a única que considera blank.

Vamos ver na prática alguns exemplos para entendermos melhor as funções.

Começando com a função ALL, que é uma das mais importantes, além de remover os contextos de filtro, ela retorna todos os valores da tabela e caso aplicado, da coluna. Veja a expressão abaixo e sua aplicação no gráfico.

Note que estou trazendo duas colunas da tabela Sales para a variável e aplicando na TOTALAM. Agora, veja o resultado no gráfico.

Como afirmado anteriormente, o filtro que seria aplicado à medida por categoria de produtos foi simplesmente ignorado por conta da função ALL. Além disso, esse resultado é obtido pelo fato de termos apenas os valores distintos considerados.

NOTA: Caso queira criar uma tabela calculada ou reduzida da tabela original para trabalhar com um volume menor, não é necessário utilizar esta função. Como não há um contexto de filtro, ela se torna dispensável.

Por exemplo: recriei a tabela de vendas adicionando uma coluna com as vendas maiores do que 3000. Observe que a coluna HighSales retorna apenas o resultado da expressão cujos produtos atendem o filtro.

Se reparar na última coluna, ela não atende a nenhum outro contexto, tanto que, cada linha possui um OrderNumber e o mesmo valor é replicado.

E no exemplo abaixo, provando que o uso de All indiferente:

Entenderam? Sem o contexto de filtro, ALL é completamente dispensável na criação desse tipo de estrutura dentro do seu modelo de dados.

Um bom uso para essa função é na criação de medidas com objetivo de porcentagem. Como o filter context é completamente removido (uma vez que só possui sentido neste contexto), ela se encaixa perfeitamente bem nesta situação.

A expressão é bem simples, recordando que estamos tratando de uma função tabular, não se esqueça de colocar este parâmetro.

Percebam a diferença  como os totais são gerados no gráfico de matriz.

Se fôssemos criar esse mesmo cálculo em uma coluna calculada, teríamos que trabalhar da mesma forma que estou mostrando aqui na criação de medidas. O que no longo prazo, com o crescimento do modelo, ele se tornaria muito lento. Esse é o motivo para uso de medidas.

Como o PowerBI permite atribuir medidas criadas às variáveis, fica muito mais fácil trabalhar com elas.

Aplicando ao gráfico, temos o quanto cada categoria representa nas vendas totais.

O mesmo se aplica à margem de lucro de cada categoria de produto:

Novamente, apliquei às variáveis as medidas que já existiam e após, no gráfico.

Na forma reduzida, temos a representação de cada categoria de produto nas vendas totais e mais, a margem de lucro que cada uma possui.

ALLEXCEPT – UM BREVE RESUMO E EXEMPLO

Já a variação ALLEXCEPT, funciona um pouco diferente. Ela mostra no resultado apenas as colunas que estão fora da lista de exceção. É como se disséssemos: “todas as colunas exceto estas”.

Se eu quiser criar uma cópia da Produto excluindo as colunas: Manufacturer, brand, Class e Style, a estrutura ficaria assim.

Tenha cuidado quando for trabalhar com ALLEXCEPT em tabelas que possuem relacionamentos, se não houver a relação estabelecida, a função retornará erro na execução.

Observe o meu modelo, não há a tabela Currency, mas há a coluna CurrencyKey na Sales.

Utilizando-a para deixar apenas as colunas de quantidade e preço na tabela, a fórmula ficaria assim:

A coluna CurrencyKey não se encontra na construção, mas, caso estivesse, acusaria este erro:

“Todos os argumentos dentro de uma ALLEXCEPT devem estar relacionados ou contidos dentro de uma relação na tabela utilizada como primeiro argumento. Além disso, ela deve estar no lado “MANY” do relacionamento 1:N”.

Como eu não coloquei a tabela Currency, não pude utilizá-la nesta construção. Porém, se repararem no exemplo anterior, o próprio engine do PowerBI excluiu esta coluna do resultado final.

LIMITAÇÃO NAS FUNÇÕES TABULARES

Não é possível utilizar ambas as funções, seja como um parâmetro, seja como uma table expression em funções de agregação, do tipo: SUM, AVG E ETC.

Caso tenha essa necessidade, terá de criar uma tabela virtual com as colunas desejadas e daí, trabalhar com as agregações destas colunas.

tbSalesQtPrice = 
    ALL('Sales'[Net Price], 
        'Sales'[Quantity])

VALUES & DISTINCT

Após o deep dive na função ALL vamos analisar o funcionamento dessas duas que também são muito importante e úteis dentro do PowerBI. Vamos entender o comportamento de VALUES E DISTINCT.

É importante ressaltar que ALL, VALUES E DISTINCT trabalham da mesma forma, avaliando os valores distintos nas colunas. O que muda e o que vai determinar na escolha de cada uma são os seguintes fatores:

1. A forma que desejamos tratar blank;
2. Se queremos valores visíveis ou não.

Vamos aos exemplos para ficar mais claro como elas agem quando aplicadas em medidas.

NOTA: para os exemplos utilizei a função COUNTROWS transformando o resultado em valor scalar.

Estas medidas serão aplicadas num filtro por categoria. Notem que o resultado para as que foram construídas utilizando VALUES E DISTINCT  e compare com a que utilizei ALL.

Aplicando no gráfico abaixo.

E o que este comportamento quer dizer?

Ele está diretamente ligado a segunda regra de escolha que mencionei acima, se desejamos valores distintos e visíveis ou não. Então, conceituando, as três funcionam com a seguinte característica.

1. ALL – retorna os valores distintos e visíveis, considerando blank.
2. VALUES – retorna os valores distintos e visíveis também considerando blank. Mas, diferente de ALL, não remove o contexto de filtro.
3. DISTINCT – valores distintos e que estão visíveis mas desconsiderando blank.

Criando colunas calculadas com as mesmas funções, veja o que acontece:

Como estamos num contexto de linha e não de filtro, as três retornam apenas a quantidade de subcategorias distintas para cada produto com uma categoria atribuída.

NOTA: existe um post (aqui) que fala sobre os contextos, sua transição e como trabalhar de maneira correta em cada um deles.

COMO DETECTAR ANOMALIAS COM VALUES E DISTINCT

Quando trabalhamos com dados, existem algumas situações que podem causar uma certa dor de cabeça quando estamos importando modelos de dados do banco para o PowerBI que é a inadequação da modelagem.

Quando o trabalho de ETL é mal feito e temos um modelo inconsistente com alguns valores perdidos, leia-se NULL no banco de dados, eles tendem a prejudicar a análise e tornar o resultado inconsistente. Então, sempre que desconfiar de um, utilize uma destas funções para detectar se há algum problema. 

Abaixo, avalie como funciona cada uma delas para aplicar em suas expressões. Vamos considerar o seguinte cenário:

No meu modelo existe a relação entre as tabelas Product – Sales (1:N – respectivo). Se por um acaso houver um delete de um registro no lado Product, o engine do PowerBI ao atualizar este modelo, criará uma linha com valor blank no lado N (tabela Sales).

Após o teste, fui no meu banco de dados e deletei a cor “Silver” da tabela Product e atualizei o modelo de dados.
Repare que agora há uma linha que não possui registro. Essa é a linha em branco que corresponde aos produtos da cor “Silver”.

Como dito no início, VALUES e DISTINCT se diferenciam pela maneira que tratam os valores em branco. Sendo que a primeira computa e os retorna dentro do contexto.

Podemos ver que até quando criamos uma medida com o total filtrado por cor, o resultado é processado, ainda que não apareça ao qual pertence.

Essa situação pode ser muito útil para detectar problemas de relação nos modelos.

Ainda que “saibam” o valor que não está correspondendo, em uma coluna calculada ele não é mostrado sob nenhuma forma, temos alguns motivos:

• O PowerBI não consegue identificar o valor, mesmo sendo passado; o que é normal.
• Não há contexto de filtro em colunas calculadas, o que não permite o cálculo adequado, como ocorreu acima.
• E por não existir um contexto de filtro, o PowerBI não consegue computar esse valor órfão e retornar.

E fiz um teste eliminando mais uma cor do banco na tabela Product e o que o PowerBI fez foi agrupar em um único blank row.

E caso se depare ou desconfie que o modelo esteja com algum tipo de inconformidade, o ideal é utilizar a VALUES pela coluna da chave. Como feito na medida abaixo, utilizando a coluna ProductKey.

A justificativa é a forma como é computada e pelo fato de não “deixar passar” tais valores.

CONCLUSÃO

Esta foi mais uma revisão de um conteúdo importante, de um grupo de funções que pode trazer uma certa dor de cabeça, quando estamos construindo expressões para análise de dados.

O objetivo era rever todo o conteúdo e trazer novas informações com mais maturidade e conhecimento. Aproveitei e complementei com algumas lacunas que ficaram do último post.

Fiz um deep dive com ALL e ALLEXCEPT por julgar que era importante e que seu domínio acrescentará muito quando estiver utilizando transição de contexto, dependências circulares e a função CALCULATE com as suas variações.

Não se esqueçam de consultar sempre a diferença entre VALUES E DISTINCT  e uma dica importante que eu dou para vocês é: sempre que possível construa suas funções utilizando VALUES.

Se gostou, deixe o seu like e compartilhe com seus amigos que querem aprender a linguagem DAX.

Abraços!