Função ALL e ALLEXCEPT – Power BI

Ao contrário da função Filter, que é uma função útil sempre que queremos restringir o número de linhas em uma tabela.

Às vezes, queremos fazer o oposto; ou seja, queremos estender o número de linhas a serem consideradas para um determinado cálculo.

Nesse caso, o DAX no Power BI oferece um conjunto de funções projetadas para esse fim: ALL, ALLEXCEPT, ALLCROSSFILTERED, ALLNOBLANKROW e ALLSELECTED.

ALL

ALL retorna todas as linhas de uma tabela ou todos os valores de uma ou mais colunas, dependendo dos parâmetros usados.

Por exemplo, a seguinte expressão DAX retorna uma tabela calculada ProductCopy com uma cópia de todas as linhas na tabela Product:

ProductCopy = ALL ('Product')

ALL é extremamente útil sempre que precisamos calcular porcentagens ou proporções, pois ignora os filtros introduzidos automaticamente por um relatório.

Imagine que precisamos de um relatório como o da Figura 1, que mostra na mesma linha o valor das vendas e a porcentagem do valor fornecido em relação ao total geral.

A medida Sales Amount calcula um valor iterando na tabela Sales e realizando a multiplicação de Sales[Quantity] por Sales[Net Price]:

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price])

Calculando a porcentagem

Para calcular a porcentagem, dividimos o valor das vendas pelo total geral.

Portanto, a fórmula deve calcular o total geral de vendas, mesmo quando o relatório está filtrando deliberadamente uma determinada categoria.

Isso pode ser obtido usando a função ALL.

Na verdade, a seguinte medida produz o total de todas as vendas, independentemente do filtro que está sendo aplicado ao relatório:

All Sales Amount :=
SUMX ( 
ALL ( Sales ), 
Sales[Quantity] * Sales[Net Price]
)

Na fórmula substituímos a referência a Sales por ALL (Sales), fazendo bom uso da função ALL. Neste ponto, podemos calcular a porcentagem realizando uma divisão simples:

Sales Pct := DIVIDE ( [Sales Amount], [All Sales Amount] )

A figura 2 mostra o resultado das três medidas juntas.

O parâmetro de ALL não pode ser uma expressão de tabela. Precisa ser um nome de tabela ou uma lista de nomes de coluna. Você já aprendeu o que a ALL faz com uma tabela.

Qual é o resultado se usarmos uma coluna?

Nesse caso, ALL retorna todos os valores distintos da coluna em toda a tabela. A tabela de categorias calculadas é obtida na coluna Categoria da tabela Produto:

Categories = ALL ( 'Product'[Category] )

Veja na figura 3 o resultado da tabela calculada Category.

Podemos especificar várias colunas da mesma tabela nos parâmetros da função ALL.

Nesse caso, ALL retorna todas as combinações existentes de valores nessas colunas. Por exemplo, podemos obter a lista de todas as categorias e subcategorias adicionando a coluna Produto [Subcategoria] à lista de valores, obtendo o resultado mostrado na Figura 4:

Categories = ALL ( 'Product'[Category], 'Product'[Subcategory])

Em todas as suas variações, ALL ignora qualquer filtro existente para produzir um resultado. Podemos usar ALL como um argumento de uma função de iteração, como SUMX e FILTER, ou como um argumento de filtro em uma função CALCULATE.

ALLEXCEPT

Se quisermos incluir a maioria, mas não todas as colunas de uma tabela em uma chamada de função ALL, podemos usar ALLEXCEPT em seu lugar. A sintaxe de ALLEXCEPT requer uma tabela seguida pelas colunas que queremos excluir.

Como resultado, ALLEXCEPT retorna uma tabela com uma lista exclusiva de combinações existentes de valores nas outras colunas da tabela.

ALLEXCEPT é uma forma de escrever uma expressão DAX que incluirá automaticamente no resultado quaisquer colunas adicionais que possam aparecer na tabela no futuro.

Por exemplo, se tivermos uma tabela Product com cinco colunas (ProductKey, Product Name, Brand, Class, Color), as duas expressões a seguir produzem o mesmo resultado:

ALL ( 'Product'[Product Name], 'Product'[Brand], 'Product'[Class] )
ALLEXCEPT ( 'Product', 'Product'[ProductKey], 'Product'[Color] )

No entanto, se posteriormente adicionarmos as duas colunas Produto [Custo Unitário] e Produto [Preço Unitário], o resultado de ALL os ignorará, enquanto ALLEXCEPT retornará o equivalente a:

ALL ( 
'Product'[Product Name], 
'Product'[Brand], 
'Product'[Class], 
'Product'[Unit Cost], 
'Product'[Unit Price]
)

Conclusão

Em outras palavras, com ALL declaramos as colunas que queremos, enquanto com ALLEXCEPT declaramos as colunas que queremos remover do resultado.

ALLEXCEPT é útil principalmente com um parâmetro de CALCULATE em cálculos avançados, e raramente é adotado com fórmulas mais simples.

No DAX para Power BI precisamos entender esta função completamente para nos tornarmos especialistas.