Debug medidas DAX versão 2.0

No artigo “Como fazer debug de uma medida DAX”, apresento como podemos fazer uso da função CONCATANEX para fazer o debug de medidas e verificarmos a informação base para o calculo de uma métrica, nomeadamente as que utilizam fórmula agregadoras como o SUMX, MAXX e SUMMARIZE.

Em novembro do ano passado foram lançadas duas novas formulas de DAX: TOCSV e TOJSON.

Vamos verificar como é que estas duas métricas nos podem ajudar no debug do nosso código.

Vamos então considerar os dados utilizados no nosso modelo inicial:

Assim como a métrica original:

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/2023). Se colocarmos este valor num gráfico de colunas obtemos o seguinte resultado:

Neste caso precisamos de obter um único valor por cada uma das subcategorias e não a soma de todos os valores acima da data fim:

De modo a validarmos a informação, no artigo referido anteriormente fizemos a utilização do CONCATANEX, no entanto vamos verificar que informação obtemos com as novas fórmulas TOCSV e TOJSON.

Antes de apresentarmos os cálculos vamos rever qual o conceito de cada uma das fórmulas e os seus parâmetros:

TOCSV:

Definição: Devolve uma tabela como uma cadeia no formato CSV.

Termo	Definição
Tabela	A tabela a converter em CSV.
MaxRows	(Opcional) O número máximo de linhas para converter. A predefinição é de 10 linhas.
Delimitador	(Opcional) Um delimitador de colunas. A predefinição é vírgula “”.
IncludeHeaders	(Opcional) Especifica um cabeçalho com o nome da coluna como primeira linha. A predefinição é: Verdadeiro.

TOJSON:

Definição: Devolve uma tabela como uma cadeia com o formato JSON.

Termo	Definição
Tabela	A tabela a converter em JSON.
MaxRows	(Opcional) O número máximo de linhas a converter. A predefinição é 10 linhas.

Como podemos verifica qualquer uma das duas funções é bastante simples pedindo apenas como obrigatório a tabela que queremos converter, sendo os restantes parâmetros opcionais, os mesmos podem ser utilizados para refinar o nosso resultado.

Fazendo a alteração da nossa métrica original para incorporar as duas novas sintaxes obtemos os seguintes resultados:

Total Days TOCSV = 
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
    TOCSV( DaysFilterTable, 100 )

======

Total Days TOJSON = 
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
    TOJSON( DaysFilterTable, 100 )

Como podemos verifica na imagem acima temos então o resultado num formato que é facilmente copiado para um ficheiro de texto e que permite copiar a informação e analisá-la rapidamente, uma vez que estamos a falar de formatos standard e que facilmente podem ser convertidos em tabelas, por exemplo em Excel.

No nosso exemplo podemos então retificar a fórmula de cálculo de dias e aplicar o TOCSV e TO JSON e obtemos o seguinte resultado:

New 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] )

============

New Total Days TO CSV = 
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
   TOCSV(  FILTER ( DaysFilterTable, 'Table'[#ID] IN IDSElection ), 100)

============

New Total Days TO JSON = 
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
   TOJSON(  FILTER ( DaysFilterTable, 'Table'[#ID] IN IDSElection ), 100)

Comparando este resultado com o anterior vemos que temos agora o nosso resultado restringido a 3 linhas por categoria enquanto na versão original da nossa métrica na categoria 2 a tabela apresentava 5 linhas.

A utilização destas novas funções DAX permite-nos otimizar o nosso debug num formato mais facilmente compreensível e com uma sintaxe mais simples do que o CONCATANEX.