DAX Language LuckyTemplates Tutorial On Reverse TOPN

Nesta postagem do blog, vamos dar uma olhada em uma pergunta que foi feita no fórum LuckyTemplates e usar uma técnica do LuckyTemplates da linguagem DAX para resolvê-la. Caso queira acompanhar e baixar os arquivos, basta visitar o fórum LuckyTemplates e acessar . Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Para explicar o cenário que vamos abordar, temos uma tabela simples como exemplo. Na primeira coluna, temos o nível Financeiro 1 . Na segunda coluna, temos uma data denominada Concluído. Na última coluna, temos a combinação de nível financeiro e data de conclusão, que chamaremos de Receita .

Em seguida, temos um slicer para a data e o nome do cliente .

O que queremos fazer para um período de tempo específico é identificar as três principais datas que não são zero. E então, nessas três principais datas, queremos identificar o nível financeiro.

Na primeira versão da medida, listei as datas inferiores a 29/04/2021 em que temos alguma receita.

E nessas datas, queremos identificar os níveis financeiros que geraram alguma receita. Vou explicar a você como podemos fazer esse cálculo.

Índice

• Modelagem de dados
• Versão 1
• Preparando o Contexto do Filtro
• Verificando o resultado do cálculo do LuckyTemplates da linguagem DAX
• Outros cálculos do LuckyTemplates da linguagem DAX
• Versão 2
• Usando a Função KEEPFILTERS
• Versão 3
• Conclusão

Modelagem de dados

Antes de escrever o DAX real, vamos dar uma olhada rápida no modelo de dados que contém várias tabelas. Estamos interessados ​​apenas na tabela Date , Data table , Job category table e Customer table .

Observe que a tabela Customer não é tão importante porque ela é usada apenas dentro de um slicer.

A medida de receita está sendo calculada na tabela Dados. A categoria Job contém o nível financeiro que temos na matriz. Em seguida, estamos usando a coluna Date da tabela Date em nosso slicer.

Versão 1

Vamos nos livrar de tudo da matriz e começar do zero. Vou criar uma nova medida e chamá-la de V1 porque esta será a versão um. Vamos dar uma olhada em duas outras variações também.

Preparando o Contexto do Filtro

A primeira coisa que vamos fazer é recuperar todos os valores do nível financeiro do contexto do filtro. Vamos criar uma e nomeá-la como FinancialLevelInFilterContext .

Para recuperar os valores do contexto do filtro, vamos utilizar a da tabela Job category que contém a coluna nível financeiro. Então precisamos criar outra variável e nomeá-la como Result . Em seguida, escreva e feche-o também.

No primeiro argumento, temos uma seleção sobre o data slicer. Nessas datas, queremos identificar as datas que não possuem 0 e possuem pelo menos alguma receita.

Se quisermos acessar todas as datas que estão selecionadas naquele slicer, precisamos utilizar a sobre a tabela Date.

E como temos o nível financeiro na matriz, também precisamos garantir que estamos removendo esse valor específico do contexto do filtro. Podemos usar a sobre a categoria Job e depois escrever Nível Financeiro 1.

Agora que nosso contexto de filtro está preparado, podemos escrever o cálculo interno para CALCULATE. Primeiramente, vamos declarar uma variável, que será uma combinação do nível financeiro e coluna de data como vimos na primeira tabela.

Em seguida, usaremos a e a tabela Data para resumir essas duas tabelas: Job category e Date table.

Isso fornecerá a combinação do nível financeiro e a coluna de data que existe dentro da tabela Dados. Em seguida, vamos criar outra variável onde armazenaremos o valor da linha de receita para a primeira variável que criamos.

O nome dessa variável será FinancialLevelAndDatesWithRevenue . O código desta variável será o sobre a variável anterior. Em seguida, criaremos uma coluna virtual que será Receita para iniciar a transição de contexto. Portanto, para cada linha dessa variável, atribuímos a receita.

Esta tabela contém a receita, que também tem zero. Precisamos filtrar essas linhas com zero. Para fazer isso, podemos criar outra variável e nomeá-la como RemoveZeroes .

Usaremos a na variável anterior, então certifique-se de que a receita não será igual a zero. Pode ser maior que zero ou menor que zero, mas não deve ser estritamente igual a zero.

E então vamos recuperar a data da variável RemoveZeroes . Para fazer isso, podemos criar outra variável e nomeá-la KeepOnlyDates .

Posso usar a para me livrar das datas duplicadas que estão sendo retornadas pela .

Em seguida, vamos identificar as três principais datas em ordem decrescente. Usaremos outra variável e a nomearemos Last3Dates , então usaremos a .

Em seguida, usaremos o nas últimas três datas na coluna de datas e usaremos um delimitador.

Vamos arrastar e soltar nossa medida recém-criada dentro dessa matriz. O resultado que estamos obtendo é 31/03/2021, 07/04/2021 e 02/04/2021. Isso significa que nossa medida está funcionando.

Vamos voltar ao editor e eliminar a parte RETURN CONCATENATEX. Para recuperar essas três últimas datas, o que podemos fazer é verificar quais linhas da variável RemoveZeroes realmente fazem parte dessas três últimas datas.

E para isso, vamos criar outra variável DatesInLast3Dates , e usar a função FILTER em RemoveZeroes . Isso retornará uma tabela que inclui uma categoria de trabalho e uma data.

Vamos resumir a coluna de receita que criamos dentro dessa tabela. Vamos retornar SUMX sobre DatesInLast3Dates e resumir a coluna de receita.

Com esse cálculo, obtemos apenas um único valor para cada linha, porque removemos a categoria Job do contexto do filtro.

Usaremos a variável que criamos no início para verificar se cada linha da variável DatesInLast3Dates está disponível no contexto do filtro. Podemos escrever outra variável e nomeá-la como IsInFilterContext .

Isso filtrará as datas na variável DatesInLast3Dates . Em seguida, retornarei SUMX no contexto IsInFilter e resumirei a coluna de receita.

Depois de pressionar Enter , você pode ver que obtemos o mesmo resultado que tínhamos no começo.

Verificando o resultado do cálculo do LuckyTemplates da linguagem DAX

Para verificar se realmente estamos obtendo o resultado correto, podemos criar uma nova tabela CALCULATE que nos ajudará na depuração, identificação e verificação de que o código que escrevemos até agora está realmente retornando o resultado correto.

Vamos criar uma cópia do código que escrevemos até agora. Vou simplesmente copiar a parte destacada abaixo, voltar para criar uma nova tabela e colar o código destacado. Não vamos nos dar ao trabalho de nomear esta tabela porque ela não nos interessa.

E podemos simplesmente escrever RETURN DatesInLast3Dates . Se eu clicar em Confirm , obtemos uma tabela, mas sabemos que ela ainda não está retornando o resultado correto porque, na verdade, precisamos imitar o comportamento do slicer.

Podemos agrupar esse código dentro de um CALCULATETABLE e recuar tudo. Na última parte, vou escrever que a data deve ser maior ou igual a 15-03-2021 e que o nome do cliente deve ser igual a DHL Supply Chain .

A tabela que criamos dentro dessa medida está retornando praticamente o resultado correto. Podemos usar um CALCULATETABLE para verificar a tabela virtual que você está criando dentro do seu cálculo.

Outros cálculos do LuckyTemplates da linguagem DAX

Agora que sabemos que nosso cálculo está funcionando e entendemos o que realmente está acontecendo nos bastidores, podemos examinar outros métodos para calcular o mesmo problema. Em primeiro lugar, vamos criar uma duplicata da medida que já criamos.

Versão 2

Desta vez, não vamos contar com as funções VALUES e FILTER, então vamos nos livrar delas. Vamos remover o resultado de retorno, bem como a última variável que criamos dentro de CALCULATE. Em seguida, converteremos CALCULATE em CALCULATETABLE.

E ao invés de retornar um valor escalar, desta vez vamos retornar o DatesInLast3Dates . Isso retornará o mesmo CALCULATETABLE que fizemos anteriormente. Vamos usar a função CALCULATE e, no primeiro argumento, vamos calcular a medida de receita e injetar CALCULATETABLE como o contexto do filtro.

Depois de trazê-lo para a matriz, você pode ver que estamos retornando o mesmo valor total geral para cada célula.

Usando a Função KEEPFILTERS

No momento, sabemos que o cálculo por trás da cena está funcionando corretamente, mas como podemos ter certeza de que para cada célula estamos informando apenas o valor daquele nível financeiro?

Com a ajuda de CALCULATETABLE, criamos uma tabela que contém o nível financeiro, a coluna de data e a coluna de receita. Podemos usar a função de filtro de chave para criar uma interseção entre o contexto de filtro que existe fora de CALCULATETABLE e aquele que está sendo criado por CALCULATETABLE.

Ao clicar em Confirmar, podemos ver que estamos reportando o valor correto para cada célula e que tanto a versão 1 quanto a versão 2 estão retornando o valor correto.

Então, como isso funciona? A tabela CALCULATE vai retornar o Nível de Manutenção , depois Aluguel , Serviço Externo e Peças de Reposição . A vai criar uma interseção entre a manutenção e essa tabela.

Rental vai criar o contexto inicial do filtro, então CALCULATETABLE vai retornar todos os valores da categoria Job. Então haverá uma interseção entre o Rental e a tabela retornada pelo CALCULATETABLE. Vamos retornar apenas as tabelas ou linhas para essa parte do aluguel.

E, finalmente, quando injetarmos SUMMARIZE no contexto do filtro, a função CALCULATE calculará a receita apenas para Rental. O mesmo processo acontece para cada linha.

Versão 3

Vejamos outra maneira de calcular o mesmo cálculo usando a linguagem DAX LuckyTemplates. Vamos criar uma cópia da medida da Versão 2 e criar outra medida. Vamos nomear esta versão 3.

A ideia por trás desse cálculo é que, como estamos calculando a receita usando a função CALCULATE, não precisamos usar a parte ADDCOLUMNS porque estamos duplicando a mesma coisa dentro e fora de CALCULATE. Em vez disso, podemos simplesmente escrever que a Receita não deve ser igual a 0.

Depois de clicar em Confirmar, precisamos ter certeza de que o código está funcionando, então vamos arrastar a medida para dentro da matriz.

Você pode ver que o código está realmente retornando o mesmo valor para cada célula.

Se eu tentar alterar o contexto do filtro alterando a data na segmentação de data e selecionando alguns valores do nome do cliente, você verá que todas as três medidas estão retornando o mesmo valor para cada linha.

Conclusão

Neste tutorial, aprendemos como usar uma lógica complexa de voltar no tempo a partir da data final para identificar as três principais datas. Com base nessa técnica do LuckyTemplates da linguagem DAX, conseguimos calcular a receita e mostrar apenas os valores que existem no contexto do filtro. Isso é tudo por enquanto neste tutorial.