Como lidar com medidas em branco em DAX?

Este post descreve um comportamento contra-intuitivo do BLANK em medidas DAX que afetam o Power BI, Analysis Services e Power Pivot.  Esse comportamento pode causar erros em um relatório usando expressões alternativas do mesmo cálculo.  De fato, essas expressões não são equivalentes quando o BLANK está envolvido.

BLANK não é NULL

A primeira coisa a se dizer é que BLANK não corresponde a NULL em SQL.  O artigo BLANK na documentação do DAX  descreve essa diferença com vários exemplos.  Para este post, basta lembrar que o BLANK não se propaga em nenhuma operação da maneira que NULL faz em SQL.  É importante prestar atenção a esta diferença nas expressões em que um resultado intermediário poderia ser BLANK (em Branco).

Razões com medidas BLANK

Considere a tabela SALES com medidas Amount (Montante) e Discount (desconto) que fornecem valores para três produtos (Bread, Fruit, Salad) e nenhuma transação para o produto Soda.

 

Sem a medida Produtos, o relatório anterior teria mostrado apenas três linhas.  No entanto, tenha em mente que existem quatro produtos.

Considerando o requisito de uma Amount% mensal mostrando a relação entre o valor líquido(Net Amount) [Amount – Discount] e o Montante (Amount), essas duas medidas aparentemente idênticas deveriam fornecer o mesmo resultado:

NetAmount % 1 := 1 - ( [Discount] / [Amount] )
NetAmount % 2 := ( [Amount] - [Discount] ) / [Amount]

No entanto, para Soda, o relatório resultante mostra um resultado de 100% para a primeira medida, enquanto que mostra um resultado em branco para a segunda medida. O último é o resultado correto.

Por que isso acontece? Por que a seguinte equação não é válida para o DAX?

1 – ( A / B ) = ( B – A ) / B

O motivo é que o valor BLANK é automaticamente convertido em 0 em somas e subtrações, enquanto se propaga como BLANK em divisões e multiplicações.  A medida da NetAmount% 1 avalia primeiro a proporção entre duas medidas em branco para o produto Soda.  Isso resulta em um BLANK, mas na seguinte subtração, este BLANK é convertido em 0 e o resultado é 100%. Contudo, em NetAmount % 2 executa a diferença entre Amount e o Discount primeiro.  A diferença entre duas medidas em branco para o produto Soda é 0, mas o denominador na divisão seguinte por Amount é BLANK. Isso propaga o BLANK ao resultado da divisão.

Por estas razões, NetAmount % 2 produz o resultado desejado.

Uma possível solução alternativa para corrigir a medida NetAmount % 1 pode ser a seguinte:

Net Amount % 2 :=
IF (
NOT ISBLANK ( [Amount] ),
( [Amount] - [Discount] ) / [Amount]
)

No entanto, a presença de uma declaração IF cria um cálculo mais complexo que poderia ser mais lento.  E o dito “Quanto mais simples, melhor”, também funciona para o DAX.

Razões exatas com variáveis BLANK

A melhor prática é dividir cálculos longos em etapas menores.  Isso envolve o uso de variáveis para auto-documentação do código e melhorar o desempenho, evitando múltiplas avaliações das mesmas expressões.

Uma possível versão da medida necessária pode ser a seguinte NetAmount % 3:

Net Amount % 3 :=
VAR DiscountPercentage =
DIVIDE ( [Discount], [Amount] )
RETURN
IF (
NOT ISBLANK ( DiscountPercentage ),
1 - DiscountPercentage
)

No entanto, esta versão produz um resultado em branco para os produtos de Salad e Soda, enquanto nós queremos o mesmo resultado que para o NetAmount % 2.  Desta vez, a instrução IF testando a variável DiscountPercentage  desconsidera Salad porque a falta de descontos propaga o valor BLANK para essa variável e a instrução IF incorretamente interpretam BLANK como ausência de transações.

A medida NetAmount % 4 fornece uma boa solução baseada em variáveis.  A vantagem desta abordagem é que cada passo está bem documentado.  Do ponto de vista da performance, a presença da declaração IF envolve um custo que poderia ser evitado, mas essa abordagem pode ser necessária para cálculos mais complexos.

Net Amount % 4 =
VAR Amount = [Amount]
VAR Discount = [Discount]
VAR DiscountPercentage = Discount / Amount
RETURN
IF (
NOT ISBLANK ( Amount ),
1 – DiscountPercentage
)

Neste caso específico, a abordagem mais simples usando variáveis é a medida NetAmount % 5, que se assemelha ao NetAmount % 2, mas garante que todas as medidas (Amount e Discount) são avaliadas apenas uma vez.

Net Amount % 5 =
VAR Amount = [Amount]
VAR Discount = [Discount]
RETURN (Amount - Discount) / Amount

Conclusão

Ao escrever uma expressão DAX onde uma ou mais partes podem resultar em um valor em branco, é importante prestar atenção às diferentes formas em que o BLANK se propaga.  A conversão automática de BLANK em 0 em somas e subtrações pode gerar resultados inesperados.  A declaração IF adiciona proteção adicional, mas se omitida por motivos de desempenho, deve-se ter cuidado na solução adotada.

 

Arquivos para Download

medidas-em-branco (2 downloads)

 

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *