Como fazer debug de uma medida DAX

Ao desenvolvermos os nossos cálculos muitas vezes obtemos alguns resultados que não conseguimos entender. Esta situação ocorre em cenários em que temos diversos filtros a influenciarem o contexto, ou na utilização de fórmulas agregadores como o SUMX, MAXX, SUMMARIZE.

Existem diversas formas para podermos detalhar e analisar os dados subjacentes e percebermos qual a origem dos problemas. Apresento abaixo uma das opções para validarmos a correção dos nossos cálculos.

Vamos considerar os dados abaixo:

O que pretendemos calcular é o total de dias das diversas categorias entre as datas selecionadas, sendo que no caso de termos mais do que uma data no intervalo de datas queremos obter o valor mais perto da data de fim (significando que estaria em vigor).

Assim se considerarmos a categoria 2 no período de datas entre 20/02/2021 e 30/10/2021 teremos de obter um total de 147 dias que são os valores identificados:

Pretendemos calcular os valores que estão entre as datas de calendário. Das várias hipóteses que podemos e para efeitos de prova de conceito vamos optar por selecionar todas as linhas que têm data superior a data fim escolhida (30/10/2021).

Poderíamos optar por uma abordagem mais complexa através do filtro da data de início e data de fim, no entanto, devido a multiplicidade de opções que poderiam surgir para determinar se a categoria estaria ativa nesse momento, e não nos daria o erro pretendido para a nossa prova de conceito (mais do que uma linha numa mesma data).

Assim criamos a seguinte medida:

Total Days =
VAR DataFinal =
    MAX ( 'Calendar'[Date] )
VAR DaysFilterTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[#ID],
            'Table'[Cat],
            'Table'[Sub-Cat],
            'Table'[End],
            'Table'[Days]
        ),
        'Table'[End] >= DataFinal
    )
RETURN
    SUMX ( DaysFilterTable, 'Table'[Days] )

Assim estamos a calcular a soma dos dias em que a data final é superior a data escolhida (31/10/2021). Se colocarmos este valor num gráfico de colunas obtemos o seguinte resultado:

Como podemos verificar em vez de obtermos os 147 dias para a categoria 2 temos um total de 249 (a categoria 3 também apresenta um total incorreto).

Para validação destes dados podemos realizar um simples filtro na nossa tabela (como apresentado acima) e verificamos que o cálculo devolve mais dados do que os necessários. Esta opção, no entanto, não é possível devido o total de linhas, multiplicidade de filtros a utilizar ou as relações existentes no nosso modelo.

Assim poderemos usar um pequeno truque que nos permite saber quais os valores que estão a ser considerados. Neste caso sabemos queremos obter um valor por cada subcategoria vamos então fazer uma adaptação do nosso código, para verificar quais os valores que estão na nossa soma.

Total Days =
VAR DataFinal =
    MAX ( 'Calendar'[Date] )
VAR DaysFilterTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[#ID],
            'Table'[Cat],
            'Table'[Sub-Cat],
            'Table'[End],
            'Table'[Days]
        ),
        'Table'[End] >= DataFinal
    )
RETURN
    CONCATENATEX ( DaysFilterTable, 'Table'[Sub-Cat] & "-" & 'Table'[#ID], " | " )

Nesta fórmula vamos concatenar a subcategoria e o ID da tabela calculada (DaysFilterTable) e verificar para cada categoria quais os ID que estão a ser considerados:

Pela tabela resumo verificamos que a categoria 2 e a categoria 3 estão a considerar linhas adicionais. Daqui advêm o nosso erro onde deveríamos ter um valor por categoria /subcategoria temos um número maior.

De modo a resolver esta questão vamos então fazer o filtro da nossa tabela calculada para ir buscar apenas o valor mínimo de cada subcategoria:

Total Days =
VAR DataFinal =
    MAX ( 'Calendar'[Date] )
VAR DaysFilterTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[#ID],
            'Table'[Cat],
            'Table'[Sub-Cat],
            'Table'[End],
            'Table'[Days]
        ),
        'Table'[End] >= DataFinal
    )


// Summarize previous table by Cat and sub-cat and return the minimum ID for that selection giving us the value that was closest to the Date selection
VAR IDSElection =
    SELECTCOLUMNS (
        SUMMARIZE (
            DaysFilterTable,
            'Table'[Cat],
            'Table'[Sub-Cat],
            "@IdentificationID", MIN ( 'Table'[#ID] )
        ),
        "@IDMinimumValues", [@IdentificationID]
    )
RETURN
    CONCATENATEX (
        FILTER ( DaysFilterTable, 'Table'[#ID] IN IDSElection ),
        'Table'[Sub-Cat] & "-" & 'Table'[#ID],
        " | "
    )

Ao criarmos uma tabela intermédia com a sumarização por categoria e subcategoria e o valor mínimo do ID obtemos o ID que tem a menor data, logo obtemos o valor em vigor na data pretendida, essa tabela de ID é de utilizada para filtrar a nossa tabela inicial (DaysFilterTable).

O resultado desta alteração devolve-nos então um valor por cada subcategoria:

Podemos então utilizar o SUMX para efetuar o nosso cálculo e obter o resultado correto.

Total Days =
VAR DataFinal =
    MAX ( 'Calendar'[Date] )
VAR DaysFilterTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[#ID],
            'Table'[Cat],
            'Table'[Sub-Cat],
            'Table'[End],
            'Table'[Days]
        ),
        'Table'[End] >= DataFinal
    )
VAR IDSElection =
    SELECTCOLUMNS (
        SUMMARIZE (
            DaysFilterTable,
            'Table'[Cat],
            'Table'[Sub-Cat],
            "@IdentificationID", MIN ( 'Table'[#ID] )
        ),
        "@IDMinimumValues", [@IdentificationID]
    )
RETURN
    SUMX ( FILTER ( DaysFilterTable, 'Table'[#ID] IN IDSElection ), 'Table'[Days] )

O exemplo apresentado não é pretendido verificar se a fórmula é a mais correta para o cálculo, mas pretende demonstrar como podemos de uma forma “simples” fazer um debug das nossas medidas no caso de não percebermos exatamente que valores estão a ser apresentados.