Olá pessoal!

No snippet de hoje gostaria de mostrar algumas diferenças entre as funções DISTINCT, VALUES. e ALL.

Ambas são funções que retornam valores distintos de uma dada coluna e sua única diferença é no tratamento de linhas em branco (blank) dentro do PBI.

Conceituando:

  • ALL – todos os valores distintos de uma dada coluna
  • VALUES – todos os valores distintos visíveis de uma determinada coluna

Então, vamos criar duas medidas utilizando-as!

As medidas serão criadas na tabela ProductCategory e retornando as cores distintas que cada categoria de produto tem, aplicado  em um contexto de filtro.

mrCntValuesColor =
-- EXPRESSÃO COM VALUES
VAR CNTR =
    COUNTROWS (
        VALUES ( 'Product'[Color] )
    )
RETURN
    CNTR
mrCountALLColor =
-- EXPRESSÃO COM ALL
VAR CNT =
    COUNTROWS (
        ALL ( 'Product'[Color] )
    )
RETURN
    CNT

Como são medidas e precisam de um contexto de filtro, veja o gráfico de matriz em que foram inseridas.

Na medida com a função ALL temos duas situações:

  1. A primeira é que ela por si, remove o contexto de filtro e;
  2. Ela retorna todos os valores distintos da tabela cor, como possui 16 cores, esse é o seu resultado.

Veja que criei uma coluna calculada apenas para comprovar o resultado da medida com ALL.

No entanto, a função VALUES só distingue os valores quando aplicada em um filter context, se eu utilizasse ela em uma coluna calculada qualquer, o comportamento seria semelhante ao da função ALL

Para mostrar a situação, repare nas duas colunas abaixo com as seguintes funções.

ccValWFilter =
VAR CLOR = "TRANSPARENT"
VAR CNTFILTER =
    FILTER (
        VALUES ( 'Product'[Color] ),
        'Product'[Color] = CLOR
    )
RETURN
    COUNTROWS ( CNTFILTER )
ccRlTableColor =
VAR COLOR = "BLACK"
VAR FILT =
    FILTER (
        RELATEDTABLE ( 'Product' ),
        'Product'[Color] = COLOR
    )
RETURN
    COUNTROWS ( FILT )

Veja que para ambas, eu abri um contexto de filtro, mas a coluna que possui a função VALUES não é capaz de trabalhar com um contexto de filtro em colunas calculadas.

Como a função retorna um valor único para cada linha, uma vez que está nos trazendo o resultado em forma de coluna, temos o resultado abaixo em vermelho.

E a função  ALL em um row context irá pegar a cor que foi passada no filtro, contar a quantidade de linhas em que ela é atribuída e retornar seu valor, veja.

ccCountColor =
VAR CNTCOLOR =
    FILTER (
        'Product',
        'Product'[Color] = "BLACK"
    )
RETURN
    COUNTROWS ( CNTCOLOR )

Execute a função acima na tabela Product e verá que o valor é o mesmo!!

E a função  ALL em um row context irá pegar a cor que foi passada no filtro, contar a quantidade de linhas em que ela é atribuída e retornar seu valor, veja.

Por mais que pareça que o parágrafo tenha sido repetido, aqui houve uma mudança de cor dentro das variáveis quando executei a função. Fiquem tranquilos!

Agora que temos o comportamento entre as funções ALL e VALUES estabelecidos  e suas diferenças fundamentais, vamos para o tema central do post que é entender a diferença e o tratamento entre VALUES  e DISTINCT. 

VALUES E DISTINCT

No início do post vimos que values quando em um filter context consegue destacar cada valor distinto retornando o seu total na situação inserida.  A função distinct age da mesma forma, mas o que as diferencia é a forma como tratam um valor em blank.

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 PBI ao atualizar este modelo, criará uma linha com valor blank no lado N (tabela Sales).

Veja no gráfico abaixo, com a medida criada com a função DISTINCT, que os valores são exatamente iguais quando comparados com a função VALUES.

Após o teste inicial, 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” que deletamos do banco de dados que originou o modelo.

Como dito no início deste tópico, a função VALUES e DISTINCT se diferenciam justamente pela maneira que tratam o valores em branco, sendo a função a medida construída com a função VALUES computa e retorna dentro do contexto.

Podemos ver que até quando criamos uma medida com o total filtrado por cor, o resultado é computado, ainda que não apareça a qual cor 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 PBI 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 PBI não consegue computar esse valor órfão e retornar.

E fiz um teste eliminando mais um cor do banco na tabela Product e o que o PBI fez foi agrupar os valores em branco da tabela Sales em um único blank row.

E caso se deparem com um modelo que esteja com algum tipo de inconformidade, tipo essa, o ideal é utilizar a values pela coluna da chave, como feito na medida abaixo, utilizando a coluna ProductKey.

A justificativa é a forma como a função calcula e pelo fato de não “deixar passar” tais valores, podendo causar erro no cálculo.

E por fim, se utilizarmos apenas tabelas com values e distinct, saiba que a primeira retorna valores duplicados e linhas em branco e a segunda não, apenas valores distintos daquela tabela.

CONCLUSÃO

Este foi um post que tentei destrinchar ao máximo o comportamento das três funções que possuem bastante similaridade em suas ações.

Lembrem-se que a função distinct irá desconsiderar valores em branco dentro de uma tabela e a função values não; além de ter duplicatas no resultado.

Se desconfia que o modelo de dados está de alguma forma inconsistente, utilize a função values em suas expressões para averiguar, dando preferencia para colunas chave.

E quando se tratar da função ALL, tenha bem estabelecido que por desconsiderar o filter context, o seu resultado será a distinção de valores como um todo; como aconteceu com a coluna ProductColor que possui 16 cores no total.

Espero que gostem, Deus os abençoe!

Saúde!