Nível: avançado
Eu estava ajudando alguém no Fórum do Power Pivot na semana passada com uma pergunta complicada de coluna calculada. O que mais, você deve saber que eu sempre aconselho os iniciantes a ficarem longe de colunas calculadas. Na maioria das vezes você não precisa de colunas calculadas porque na maioria das circunstâncias uma medida é uma escolha melhor para usar (leia mais sobre isso aqui). Nesta ocasião, porém, a fórmula necessária provavelmente será complexa, desempenho de tempo de execução de tal fórmula como uma medida pode ser lento, além do fato de que o número de possíveis valores únicos da Fórmula DAX é pequeno. Quando essas condições são atendidas, uma coluna calculada pode ser uma boa solução.
Meu processo é mais importante do que minha solução
Quero dizer, antes de começar isso esta postagem do blog é sobre o processo Eu passei por uma solução de trabalho. Acho que o processo é muito mais interessante e muito mais útil do que a fórmula final. O que mais, quando você olha para a fórmula final, você pode pensar que nem mesmo entende o que a fórmula faz, muito menos sabe como escrevê-la. Mas aqui está o ponto de viragem: quando eu olho para a fórmula final, Eu acho o mesmo. É por isso que o processo de escrever esta fórmula é tão importante.
Aqui está a fórmula final.
Se você quiser aprender a escrever tal fórmula, continue lendo.
=
CALCULATE(
MAX(Alterações[new_value]),
FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])),
FILTRO(Alterações, Alterações[change_type] = "Tipo"),
FILTRO(
Alterações,
Alterações[data de início]
= CALCULAR(
MAX(Alterações[data de início]),
FILTRO(
CALCULÁVEL(
Alterações,
FILTRO(Alterações, Alterações[member_id] = MAIS CEDO(Alterações[member_id])),
FILTRO(Alterações, Alterações[change_type] = "Tipo")
),
Alterações[data de início] < PRIMEIROS(Alterações[data de início])
)
)
)
)
O problema explicado
Há uma tabela de dados (ver exemplo abaixo). A tabela contém informações sobre mudanças no status de membro do clube. Sempre, um membro tem
- Um estado (ativo ou inativo)
- Um cara (cheio, parcial, limitado)
Tanto o “Estado” como ele “Gentil” são indicados por um código. Os códigos de status e tipo podem mudar com o tempo. Quando estes mudam, um log de alterações é criado mostrando o valor_antigo e o valor_novo. Uma linha na tabela é igual a uma mudança.
Os pontos principais são:
- O log de mudanças (linha) pode ser para uma mudança no Estado ou uma mudança no escreve como indicado no Change_Type coluna.
- Há uma coluna Member_ID que mostra a qual membro a linha de alteração pertence.
- A data da mudança é start_date.
objetivo
O objetivo é saber o que o “status atual” do código de tipo para cada membro. no momento em que o novo changelog foi adicionado. Em outras palavras, no momento em que este changelog foi criado, Este cliente era um membro pleno, parcial o limitado?
Portanto, o problema é que eu preciso encontrar o último “código de tipo” definido antes da data da transação atual. Um exemplo deixará claro.
Exemplo
Aqui está uma cópia filtrada da tabela para member_id 3100. As transações são classificadas na data_de_início do pedido (qual é a data da transação). Esta tabela contém minha fórmula de coluna calculada no trabalho final “Tipo atual”
- As primeiras 2 transações (filas) aconteceu em 28/1/1998 (data de início). Neste momento não havia entrada pré-existente para “Cara” e, Como tal, a coluna calculada “Tipo atual” retorna em branco (Assistir 1 Acima).
- A transação na fila 3 aconteceu em 22/3/2013 (data de início). Neste momento, a “Cara” para este cliente que era “12” porque este foi o último código estabelecido como new_value antes do 22/3/2013. Neste caso, a “Cara” o 28/1/1998 como é mostrado em 2 Acima. Como resultado, minha coluna calculada retorna 12 para o registro 3 (Assistir 2 Acima).
- O registro 3 realmente mudou o valor de "Tipo" de 12 uma 14, então os seguintes registros (4 uma 7) deve retornar o valor 14. E eles fazem o que você pode ver em 3 Acima.
Um design diferente?
Antes de compartilhar com vocês como criei esta coluna calculada, é importante notar que isso provavelmente seria mais fácil de resolver se houvesse 2 mesas separadas, um que contém as transações de “Cara” e outra tabela contendo as transações de “Estado”. Pondo isso de lado, este é um grande desafio, pois é, e trabalhar em problemas DAX desafiadores o ajudará a ficar melhor e mais forte: um verdadeiro DAX Ninja.
Como resolvi o problema: passo a passo
O princípio
Eu tive uma visão clara em minha mente do que eu tinha que fazer antes de começar. Ao resolver qualquer problema DAX, você deve pensar sobre “filtrar primeiro, avaliar em segundo lugar”. Em outras palavras, filtrar a tabela que eles me fornecem para que ela contenha apenas o que eu preciso, então pegue os valores que procuro.
Com isso em mente, para cada registro, precisava:
- Verifique a qual membro o único registro pertencia
- Verifique a data desta mudança de registro
- Filtre a tabela inteira para:
- este membro
- também para todos os registros que foram change_type = “modelo”
- também para todos os registros anteriores à data de registro atual
- Mais tarde, uma vez que eu tive a tabela filtrada acima, Tive que encontrar o último registro até start_date e, em seguida, pegar o número da coluna new_value
Paso 1. Comece com qualquer coisa
Francamente, Eu só queria escrever algo para me ajudar a começar. Então comecei com algo simples. Perguntei-me: “quantas linhas cada cliente tem nesta tabela??”
Eu escrevi esta coluna calculada.
=
CALCULATE(
CONTRATAÇÕES(Alterações),
FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id]))
)
A linha 4 É a chave. A linha 4 dados…
“Vá e coloque um filtro na tabela Alterações onde o member_id é igual a member_id desta linha”. A função PREVIOUS é necessária porque uma coluna calculada tem um contexto de linha, assim como a função FILTER.
A sintaxe VAR
Se você tem Excel 2016 o Power BI Desktop, você pode usar a sintaxe VAR. Acho que a sintaxe VAR é mais fácil de escrever e ler. Aqui está a mesma fórmula com sintaxe VAR.
= VAR ThisMember = Changes[member_id] RETURN CALCULATE( CONTRATAÇÕES(Alterações), FILTRO(Alterações, Alterações[member_id] = EsteMember) )
Espero que você concorde que esta versão VAR é mais fácil de ler e entender. Primeiro, define um valor para a variável na linha 2. Mais tarde, você pode usar essa variável dentro de suas fórmulas sem ter que se preocupar com o contexto da linha interna e externa.
Nós vamos, Estou funcionando e resolvi o primeiro problema. Eu escrevi uma fórmula que detecta corretamente o member_id atual e filtra a tabela para esse membro (como você pode ver abaixo na coluna WIP).
Claro, Eu precisei usar COUNTROWS já que não é possível colocar uma tabela em uma coluna. Em seu lugar, Eu usei COUNTROWS como um hack para que eu pudesse “Assistir” qual o tamanho da mesa para cada membro. Eu uso COUNTROWS regularmente quando estou construindo fórmulas complexas, porque é uma boa maneira de “Assistir” a mesa virtual que estou construindo. Outro ponto importante aqui são os dados de teste. Quando escrevi esta fórmula de teste pela primeira vez, realmente tinha 7 linhas na tabela de amostra para cada cliente. Isso tornava muito difícil verificar se a fórmula funcionava. Então, antes de fazer qualquer outra coisa, Voltei e mudei meus dados de teste para que cada membro tivesse um número diferente de linhas. então eu poderia “Assistir” que a fórmula estava funcionando. Bons dados de teste são essenciais para a escrita eficiente de fórmulas.
Paso 2. Quantas linhas de “Gentil” para este membro?
Trabalhando no meu plano mental de alto nível, Decidi tentar filtrar a tabela para contar quantas linhas havia para cada cliente onde o registro era change_type = "type". Aqui está minha nova fórmula.
=
CALCULATE(
CONTRATAÇÕES(Alterações),
FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])),
FILTRO(Alterações, Alterações[change_type] = "Tipo")
)
Como você pode ver, Eu voltei para a sintaxe original (sin VAR). Acabei de adicionar um novo filtro em change_type = “modelo”. Foi fácil verificar se esta fórmula funcionou olhando os resultados.
Paso 3. Extraia algo mais útil
Até agora, contei quantas linhas existem nessas tabelas de teste. Então, agora que eu tinha a tabela correta para usar como meu filtro, Decidi tentar extrair a última data de início desta tabela. Isso foi tão fácil quanto mudar a linha 3 como você pode ver abaixo.
=
CALCULATE(
MAX(Alterações[data de início]),
FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])),
FILTRO(Alterações, Alterações[change_type] = "Tipo")
)
E a nova fórmula agora extrai corretamente a última data de início desta tabela. Lembre-se que a tabela contém todos os change_type = “modelo” para este member_id, então esta data de início é a data da última transação de “Gentil” Fundar.
Paso 4. Converta o 2 filtros em uma única tabela
A fórmula acima tem 2 funções de filtro. Eu realmente preciso que seja uma única mesa para poder usá-la.. Há algumas maneiras de fazer isso, mas eu decidi transformá-lo em uma tabela como segue. Por favor, note que eu ainda estou usando COUNTROWS para ser capaz de validar que ele ainda está funcionando.
=
COUNTROWS(
CALCULÁVEL(
Alterações,
FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])),
FILTRO(Alterações,Alterações[change_type] = "Tipo")
)
)
As linhas 3 uma 7 agora eles são um “Tabela” que eu posso usar como eu vou junto. Em seu lugar, poderia ter usado uma única função FILTER com vários filtros usando &&.
Paso 5. Obtenha a última data antes desta data de transação
Esta tabela contém todos os tipos de transações. Mas Eu não quero. todo tipo de transações, Buscar a última transação que ocorreu antes de esta transação atual. Em outras palavras, Preciso filtrar esta tabela para excluir qualquer tipo de transações que ocorreu em ou após a data de Está transação. Eu decidi voltar para a sintaxe VAR aqui para facilitar a escrita desta fórmula.
= VAR thisStartDate = Changes[data de início] RETURN CALCULATE( MAX(Alterações[data de início]), FILTRO( CALCULÁVEL( Alterações, FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])), FILTRO(Alterações, Alterações[change_type] = "Tipo") ), Alterações[data de início] < esteStartDate ) )
Note que as linhas 7-11 são a tabela que eu produzi na etapa 4. Enrolei isso dentro de outro FILTRO para excluir todos os registros na data de registro atual ou após essa data, salvando apenas os registros que ocorreram antes da data atual do registro de alterações. Portanto, as linhas 6 uma 13 eles retornam uma tabela contendo todos os registros de 'tipo’ o que aconteceu antes desse registro. Então eu uso a instrução CALCULATE para retornar a data do último tipo de transação. Como você verá na tabela resultante abaixo, a coluna wip agora retorna espaço branco para qualquer registro que não tenha um registro de 'tipo'’ por um período anterior no tempo.
Paso 6. Extrair o valor do registro de “Gentil” anterior
Agora que eu sei a data do registro do 'tipo'’ Direito, Posso proceder para extrair o valor desse registro da coluna new_value. Aqui está minha fórmula.
= VAR thisStartDate = Changes[data de início] VAR dateToUse = CALCULATE( MAX(Alterações[data de início]), FILTRO( CALCULÁVEL( Alterações, FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])), FILTRO(Alterações, Alterações[change_type] = "Tipo") ), Alterações[data de início] < esteStartDate) ) RETURN CALCULATE( MAX(Alterações[new_value]), FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])), FILTRO(Alterações, Alterações[change_type] = "Tipo"), Alterações[data de início] = dataParause )
Observe como eu peguei o resultado da fórmula na etapa 5 e eu transformei em outro VAR (filas 3 uma 14). Mais tarde, Eu escrevi uma nova fórmula (filas 16 uma 21 Acima) para extrair o valor que preciso depois de filtrar o member_id (linha 18), el change_type (linha 19) e a data correta da transação (linha 20).
Uma última complicação
Eu tive um último problema que encontrei. El OP e http://powerpivotforum.com.au estava usando Excel 2010, então eu não poderia fornecer a fórmula acima (Excel 2010 não suporta sintaxe VAR). Então eu tive que remover a sintaxe VAR. Primeiro removi o primeiro VAR (linha 2 da etapa 6 anterior) da seguinte maneira. Observe como eu tive que usar o BEFORE na linha 11 para gerenciar o contexto da linha.
= VAR dateToUse = CALCULATE( MAX(Alterações[data de início]), FILTRO( CALCULÁVEL( Alterações, FILTRO(Alterações, Alterações[member_id] = ANTERIOR( Alterações[member_id])), FILTRO(Alterações, Alterações[change_type] = "Tipo") ), Alterações[data de início] < MAIS CEDO(Alterações[data de início]) ) ) RETURN CALCULATE( MAX(Alterações[new_value]), FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])), FILTRO(Alterações, Alterações[change_type] = "Tipo"), FILTRO(Alterações, Alterações[data de início] = dataParause) )
Finalmente removi o segundo VAR da etapa. 6 da seguinte maneira. Eu peguei as linhas 3-13 da fórmula anterior e data substituídaParauser até a linha 19 com esta fórmula.
=
CALCULATE(
MAX(Alterações[new_value]),
FILTRO(Alterações, Alterações[member_id] = ANTERIOR(Alterações[member_id])),
FILTRO(Alterações, Alterações[change_type] = "Tipo"),
FILTRO(
Alterações,
Alterações[data de início]
= CALCULAR(
MAX(Alterações[data de início]),
FILTRO(
CALCULÁVEL(
Alterações,
FILTRO(Alterações, Alterações[member_id] = MAIS CEDO(Alterações[member_id])),
FILTRO(Alterações, Alterações[change_type] = "Tipo")
),
Alterações[data de início] < PRIMEIROS(Alterações[data de início])
)
)
)
)
Depois de fazer essa mudança, note que desta vez nas linhas 14 e 17 Eu tive que modificar a fórmula para gerenciar corretamente o contexto da linha aninhada, mas desta vez eu tive que me referir a MAIS CEDO em vez de MAIS CEDO. Isso é porque há 3 conjuntos de contexto de linha aninhado. A primeira é a coluna calculada, o segundo é o FILTRO na linha 6 e o terceiro é o FILTRO na linha 11.
Uma palavra final
O ponto principal desta postagem é que a fórmula final acima é difícil de ler e entender. Você pode pensar que esta fórmula é escrita começando do topo e trabalhando para baixo, Mas isso é longe da verdade. Fórmulas DAX complexas raramente (se alguma vez) são escritos de cima para baixo. O truque para escrever fórmulas DAX complexas é quebrar o problema em partes e resolver cada peça do quebra-cabeça passo a passo..
Aqui está o sample_workbook e aqui estão todas as fórmulas que escrevi.
Você tem uma solução melhor?
Eu sei que existem alguns Ninjas DAX talentosos que leem meu blog (Jess, Owen para nomear 2). Eu sempre amo aprender, então se você tem uma fórmula / melhor solução ou abordagem alternativa interessante do que esta, Eu adoraria ver isso. Poste nos comentários.
