DAX – Funções Tabulares II

Continuando sobre as funções tabulares em DAX e sua complexidade, hoje vamos ver outras funções que fazem parte desse esquema.

As funções são ALL e ALLEXCEPT e elas agem contra o contexto de filtro criado para um determinado cálculo, medida etc – onde houver um filtro, ela age.

Então, sem mais explicação, vamos ver na prática como elas funcionam!

ALL E EXCEPT

Neste primeiro exemplo criei três colunas calculadas para a tabela ProductSubCategory sendo a primeira calculando a venda total para cada categoria de produto.

A intenção é criar uma coluna que nos mostre a porcentagem que cada categoria representa na venda total, então, vamos precisar de vendas por categoria e vendas totais.

Como minha tabela produto está relacionada com a tabela vendas, foi possível criar este vínculo.

Ao criar a venda por categoria, vamos utilizar a função ALL junto da SUMX para obter o valor total.

Com isso, basta calcular a divisão entre as duas colunas, podendo utilizar a função DIVIDE ou uma expressão normal. No meu caso, utilizei a função.

Se por um acaso possuir prévio conhecimento do objetivo final ao qual está criando a expressão, utilize variáveis com as expressões bases e evite criar muitas colunas calculadas. Neste exemplo, criei apenas uma coluna calculada para saber o total por categoria de produtos.

Se fizer uso de variável na sua expressão, a função ALL não aceita como parâmetro de uso. Somente tabelas físicas são permitidas, mesmo sendo uma função tabular.

A função ALL ignora qualquer que seja o contexto de filtro dentro de uma medida, veja neste exemplo duas situações com a mesma fórmula.

O primeiro exemplo é a fórmula utilizada para criar medida. Como sabemos, medidas possuem um ‘contexto’ mais gráfico.

A fórmula utilizada foi esta.

mrALLCOLORTESTE = 
    VAR ALLT = (FILTER(ALL('Product'),'Product'[Color] = "SILVER"))
    RETURN
        SUMX( ALLT,
            SUMX(RELATEDTABLE(Sales),
                'Sales'[Quantity] * 'Sales'[Net Price])
        )

Esse é o resultado da medida aplicado em um dashboard do PBI. Repare que todos os valores são iguais.

Isso acontece pois como falei, a função ALL ignora completamente o contexto de filtro aplicado pelo PBI quando utilizamos em gráficos.

Agora, perceba quando jogo em uma coluna calculada e aplico essa coluna calculada em um contexto de filtro, como o resultado muda. Para criar a fórmula na coluna calculada, removi a função ALL.

Como podem ver, aplicando a coluna calculada em um contexto de filtro, cada cor teve seu total calculado e exibido, o que não aconteceu com a medida, que exibiu para todas as cores o valor da venda da cor cinza.

NOTA: em próximos posts sobre DAX começarei a abordar os contextos de linha e filtro e entenderemos como funciona e cada um se diferencia.

Pela coluna calculada criar um contexto de linha, acaba gerando um total diferente, agrupando e somando tudo. Essa questão de contexto de filtro e linha será abordado num próximo post onde tratarei exclusivamente disso.

Também é possível retornar várias colunas de uma tabela com o ALL, desde que seja da mesma tabela. All não aceita de tabelas distintas pois o número de linhas em sua maioria é diferente.

Já o ALLEXCEPT pode vir a ser útil caso queira construir uma tabela diminuta de uma maior, contendo apenas as principais colunas, como fiz com a tabela sales.

A vantagem é trabalhar com modelos mais enxutos garantindo maior velocidade para criar qualquer tipo de expressão.

Existem algumas situações que precisamos destacar quanto ao uso do EXCEPTALL:

Se na tabela utilizada como base, houver alguma modificação ou criação de uma coluna calculada, ela irá se refletir aqui.

Reparem que até o ícone da coluna calculada é diferente quando mimetizamos uma tabela com o ALLEXCEPT.

Não é possível adicionar mais colunas utilizando a função all. Isso ocorre porque a função all não retorna todos os valores, logo, acusa erro; ela age como DISTINCT.

Tabelas que não possuem relacionamento com a tabela mãe, são impedidas de serem adicionadas normalmente. Terá de usar a função RELATEDTABLE.

VALUES, DISTINCT E BLANK

No tópico anterior vimos que a função All retorna todos os valores únicos de uma coluna. Em DAX existem outras duas funções que agem de forma similar, retornando uma lista de valores únicos que são: VALUES E DISTINCT.

Primeiro, vamos entender como a função VALUES funciona.

Vamos avaliar o seguinte cenário para entender a diferença entre as duas medidas.

A primeira medida terá essa sintaxe:

E a segunda, essa sintaxe:

Sabendo que medidas são representações gráficas, vamos analisar seus valores quando expostas em um gráfico.

Em azul, a primeira medida, criada com a função ALL e de vermelho, a medida criada com VALUES. A medida criada com ALL retorna a quantidade de cores distintas existentes na tabela Product[Color], independente da distribuição nas categorias de produtos, o que não ocorre com a medida criada com a função VALUES.

A função VALUES acaba retornando todos valores que ela encontra e como estamos aplicando num gráfico, ela acaba filtrando por categoria de produto.

Então, para o produto áudio, existem 10 cores distintas distribuídas entre os produtos, para cameras and camcoders 14, cell phones 8 e etc.

Mas se utilizássemos essa função em uma coluna calculada, veja o resultado.

“E por que as duas funções possuem comportamentos diferentes?? o que ocorre?”

Esse comportamento acontece pois as duas funções consideram linhas vazias (blank). Esse é um comportamento padrão do PBI, garantido pelo seu engine quando temos uma relação 1:N e criamos a medida no lado 1(uma categoria de produto(1) pode ter muitos produtos associados(N)).

O engine faz isso apenas para garantir que não haverá valor perdido entre as relações.

Entenda que quando essa situação ocorre, apenas UMA linha em branco é adicionada e todos as relações do lado N são ligadas a essa linha.

Ainda que a função VALUES seja uma função tabular e por isso, retorne tabela, existe um caso especial onde ela pode retornar um valor scalar, ou seja, uma única linha.

Esse caso é isolado e só é valido quando temos uma tabela com uma única linha e coluna.

Para esse tipo de situação, primeiro criamos uma medida simples contando as marcas distintas.

Após, crie uma coluna calculada com a contagem das marcas de produtos que forem iguais a 1 dentro de um IF.

Ao final, basta criar um relatório com as seguintes medidas:

A medida criou uma simples contagem de marcas que possuíam apenas um registro em determinada categoria. Já a coluna calculada, foi utilizada para retornar o nome das marcas, caso (IF) seu valor dentro do contexto de filtro de categoria fosse 1.

Por isso conseguimos visualizar a quantidade e quais marcas são.

Dax possui outras duas alternativas para o padrão que acabamos de ver acima, que seriam as funções: HASONEVALUE E SELECTEDVALUE

Veja o exemplo com a função HASONEVALUE comparando com o resultado da expressão com IF criado no exemplo anterior. O resultado foi o mesmo, levando em consideração que utilizamos menos código, já que temos uma função própria para tal.

Veja o código da criação da medida abaixo e sua aplicação no gráfico.

HasOneBrand = 
    IF(HASONEVALUE('Product'[Brand]),
       VALUES('Product'[Brand]))

E com a SELECTEDVALUE, a função é ainda mais simples com uma opção de mensagem caso não retorne o valor desejado. No meu caso, adicionei “two or more values”. Veja o resultado! E muito mais fácil de implementar.

mrSelectedProdB = 
    SELECTEDVALUE('Product'[Brand],"Two or more values")

Por fim, existe uma outra função no subgrupo da função tabular ALL que se chama ALLSELECTED e que age de forma diferente no resultado final.

Por exemplo, repare esse gráfico com os filtros selecionados e os valores.

Como explicado anteriormente na função do ALL, quando utilizamos em alguma expressão, ela elimina o contexto de filtro da função retornando sempre o valor total de uma determinada medida. Como neste meu exemplo eu selecionei apenas quatro categorias, a medida mrPctBrand retornou o total das porcentagens agrupadas e somadas no seu final.

Agora, se por um acaso esse tipo de resultado não fosse desejado, se quiséssemos que os 4 selecionados retornassem o total como 100%, temos de utilizar ALLSELECTED.

Veja no gráfico, adicionando a nova medida e comparando os resultados com as mesmas categorias selecionadas.

A função age como um mix de AII com ALLEXCEPT, onde ela exclui os outros valores que não foram selecionados, agrega e soma os valores dos selecionados, tornando aquele o novo ‘grand total‘ no dashboard.

O único problema desta função é a sua complexidade de execução. A recomendação é para que seja utilizada em situações mais simples, como feito agora! Seu comportamento é um tanto instável, podendo em algumas situações causar erros inesperados. Cuidado.

CONCLUSÃO

Este foi um post para encerrar temporariamente o assunto que envolve funções tabulares e seus usos.

Mostrei alguns exemplos com alguns contextos e o modo de ação em cada um deles, principalmente na função ALL, que possui duas formas de ação.

Tenham cuidado quando forem trabalhar com essas funções pois dependendo do tipo de situação, podem retornar erro. Funções tabulares sempre retornam tabelas e isso pode acabar causando confusão pois queremos apenas a coluna!

Tenham como base o uso de variável para facilitar o cálculo e a função RELATEDTABLE, que auxilia muito na criação de expressões.

Para o próximo post irei trabalhar os contextos e sua importância para entender a linguagem DAX; são vitais nessa caminhada.

Link para baixar o banco de dados utilizado nos exemplos, aqui.

Modelo de dados PBIX, aqui.