Nível: intermediário
Em mim último artigo Eu apresentei o DAX como uma linguagem de consulta e expliquei por que aprender como consultar seu modelo de dados pode ser útil. Abordei o básico sobre o uso de uma ferramenta como o DAX Studio para consultar um banco de dados e retornar uma tabela de dados como resultado. Neste artigo, vou me aprofundar e apresentar as funções de consulta mais comuns e úteis, incluindo CROSSJOIN, RESUMA y ADDCOLUMNS (há mais recursos que não abordo neste artigo).
Aqui está o link para o modelo de dados PBIX novamente se você quiser ir em frente e fazer os exemplos você mesmo. Estou usando o DAX Studio novamente para me conectar ao Power BI Desktop em execução na minha máquina local. Se você dedicar algum tempo para completar esses exemplos, aumentará significativamente o seu aprendizado e retenção de conhecimento.
Linhagem
Antes de continuar, um fato interessante e importante sobre o Power Pivot (em comparação com ferramentas de banco de dados tradicionais) é o conceito de linhagem (pronuncia-se LIN-E-AGE). Ao criar uma nova tabela virtual em uma consulta ou fórmula no Power Pivot, a nova tabela incluirá uma relação automática de um para muitos com a tabela a partir da qual foi criada. Considere a seguinte tabela simples da última vez.
Você pode ver acima que esta consulta produz uma tabela de coluna única de todas as categorias de produtos exclusivas. Esta nova tabela virtual preserva a linhagem do modelo de dados. Neste caso, a nova mesa virtual vem da tabela Produtos e, portanto, a nova mesa virtual tem linhagem para a mesa de produtos. Você pode “imagina” isso conforme mostrado abaixo com a nova tabela tendo uma relação de 1 muitos com a tabela de produtos.
Observe a imagem acima é apenas uma simulação visual sobre o que está acontecendo. A mesa virtual não é materializada e você não pode realmente vê-la na visão de relacionamento. Mas a mesa virtual existe (virtualmente) e a linhagem da tabela Produtos também existe, é só que você não pode realmente ver. Eu recomendo aprenda a “algo muito importante acontece” que esto suceda en el modelo de datos en su mente pois vai ajudá-lo a entender como a nova tabela virtual interage com o resto do modelo de dados, especialmente no que diz respeito à transição de contexto.
Todas as tabelas virtuais têm uma linhagem com as tabelas de onde provêm.
CRUZAR
CROSSJOIN é uma função que pode criar uma nova tabela a partir de 2 ou mais tabelas de origem. Para este exemplo, Vou entrar em algumas mesas virtuais. A primeira mesa virtual é VALUES (produtos[Categoria]) naquela, claro, retorna uma lista de todas as categorias exclusivas de produtos.
A segunda mesa virtual é uma lista de todos os gêneros de clientes possíveis.
A seguir, Vou usar CROSSJOIN para criar uma nova tabela contendo todas as junções exclusivas de ambas as tabelas.
Na tabela acima existem 4 filas x 2 linhas dando um total de 8 linhas de todos os valores únicos. Continuando con el concepto de “algo muito importante acontece” la forma en que estas nuevas tablas virtuales se relacionan en el modelo de datos, ficaria assim:
Lembre-se de que esta é apenas uma simulação de sua aparência. Essas tabelas no topo não são materializadas e você não pode vê-las no modelo de dados. Pero puede “imaginarlos” con este aspecto y se comportan exactamente de la misma manera que lo harían si fueran tablas físicas.
M x N pode significar uma grande mesa
Você deve ter cuidado com CROSSJOIN como, por definição, a tabela resultante terá mxn linhas de comprimento, onde m é o número de linhas na tabela 1 yn é o número de linhas na tabela 2. Se eu tivesse que CROSSJOIN na mesa do cliente (18,484 filas ) com a tabela de produtos (397 filas) acabaria com mais do que 7 milhões de linhas. Isso por si só não é um problema para o Power Pivot criar uma tabela tão grande na memória, mas pode definitivamente ser um problema se você tentar materializar a mesa. Mais na próxima semana.
RESUMIR
RESUMA es, com muito, minha função de consulta DAX favorita. SUMMARIZE pode fazer coisas semelhantes a CROSSJOIN, porém, CROSSJOIN pode unir tabelas que não têm relacionamentos, enquanto SUMMARIZE só pode unir tabelas relacionadas ao relacionamento muitos para muitos 1.
SUMMARIZE primeiro pega uma tabela e depois uma ou mais colunas (acessado por meio de um relacionamento muitos para muitos 1) que você deseja incluir na nova tabela resumida.
RESUMO (, Tabela[coluna], Tabela 2[coluna],…)
Aqui está um exemplo.
Os resultados da consulta acima são semelhantes aos da consulta CROSSJOIN acima, mas há uma diferença importante. SUMMARIZE retornará apenas linhas que realmente existem nos próprios dados (note que só há 6 filas arriba en comparación con 8 filas en el ejemplo CROSSJOIN).
Considere las tablas relevantes del modelo de datos a continuación.
Aquí está la fórmula RESUMEN escrita anteriormente.
EVALUATE
SUMMARIZE(Vendas, Produtos[Categoria], Clientes[Gênero])
Esta consulta comienza con la tabla Ventas y luego agrega los Productos[Categoria] columna de la tabla Productos y Clientes[Gênero] columna de la tabla Clientes. As 2 columnas especificadas dentro de la fórmula SUMMARIZE provienen de tablas en el lado 1 de las relaciones de muchos a 1; esto está permitido.
O seguinte não é permitido e não funcionará.
AVALIE
RESUMO (Produtos, Vendas[CustomerKey])
Não funciona porque a coluna Vendas[CustomerKey] não pode ser acessado a partir da tabela Produtos por meio de um relacionamento muitos para muitos 1.
Também é possível escrever uma declaração SUMMARIZE sobre qualquer mesa. No exemplo a seguir, a instrução SUMMARIZE retorna uma lista de todas as combinações possíveis de categoria de produto e cor.
Você também pode obter o mesmo resultado com a função ALL (o que seria uma solução mais fácil se você estiver usando apenas uma única tabela)
EVALUATE
ALL(Produtos[Categoria], Clientes[Gênero])
Agregar ventas de resumen a la tabla de resumen
Até agora, las consultas SUMMARIZE anteriores son solo listas de combinaciones válidas. Es hora de hacer algo más interesante y agregar el [Vendas totais] a estas tablas de resumen. Antes de continuar, tenha em conta que las siguientes fórmulas no son las mejores prácticas – hay una forma mejor que cubriré más adelante.
O valor máximo para a conta R Marketing é em maio
EVALUATE SUMMARIZE( Produtos, Produtos[Categoria], Produtos[Cor], "total sales", [Vendas totais] )
Tenga en cuenta específicamente que el parámetro de la tabla en esta fórmula es “Produtos”. Observe também que esta fórmula retorna linhas em branco (mostrado abaixo).
Esta declaração resumida resume corretamente todas as combinações de produtos[Categoria] e produto[Cor] na tabela de produtos e, em seguida, para aqueles produtos onde há vendas, essas vendas são exibidas ao lado da combinação única. Mas em alguns casos, combinação única realmente não tem vendas, daí as linhas em branco.
Use vendas como um parâmetro de tabela
Se eu mudar a fórmula acima e mudar a tabela Produtos para a tabela Vendas, linhas em branco não são mais visíveis (Veja abaixo).
SUMMARIZE sempre encontrará as combinações únicas que realmente existem nos dados selecionados. Porque esta nova fórmula começa na tabela Vendas, apenas combinações de produtos[Categoria] e produto[Cor] onde há vendas reais são devolvidas.
Transição de contexto ou nenhuma transição de contexto?
Aqueles de vocês que estão familiarizados com o conceito de transição de contexto podem estar pensando que a transição de contexto está acontecendo aqui.. Isso é uma coisa válida de se assumir, mas não é isso que está acontecendo aqui. O valor máximo para a conta R Marketing é em maio.
Observe como eu mudei a medição [Vendas totais] con SUM (Vendas[Quantidade extendida]). Com esta nova fórmula acima, não há CALCULAR que force a transição de contexto; porém, Apesar disto, a tabela ainda retorna o mesmo resultado. Isso implica que SUMMARIZE não opera em um contexto de linha. De fato, SUMMARIZE é uma operação do Vertipaq Storage Engine. A parte que produz as combinações válidas de colunas é muito eficiente, porém, calcular o número total de vendas é muito ineficiente. Por esta razão, é melhor usar ADDCOLUMNS para somar os totais de vendas (Veja abaixo).
Colunas
ADDCOLUMNS faz exatamente o que sugere: adicionar novas colunas a uma tabela em uma consulta. A sintaxe geral é a seguinte:
ADDCOLUMNS (, "Nome da coluna", ,…)
Para demonstrar como isso funciona, deixe-me começar com uma fórmula acima que produz a seguinte tabela.
A função SUMMARY retorna uma tabela de 2 colunas com 15 filas: todas as combinações possíveis contendo valores de vendas. Esta tabela pode ser usada como parâmetro de tabela na fórmula ADDCOLUMNS da seguinte forma.
Você pode ver acima que esta nova tabela retorna o Total de Vendas para cada um dos 15 combinações possíveis.
E é possível adicionar quantas colunas novas forem necessárias à tabela de resumo. Veja abaixo.
As diferenças importantes entre ADDCOLUMNS e SUMMARIZE
Agora você viu que é possível adicionar colunas (como o resumo das vendas totais) para uma tabela usando SUMMARIZE e também com ADDCOLUMNS. Mas existem algumas diferenças importantes entre estes 2 aproximações.
ADDCOLUMNS tem um contexto de linha
Ao contrário do que mostrei com SUMMARIZE anteriormente neste artigo, ADDCOLUMNS tem um contexto de linha. Considere a seguinte consulta.
Quando eu mudo a medição [Vendas totais] con SUM (Vendas[Quantidade extendida]) os resultados estão errados. Isso mostra que ADDCOLUMNS opera em um contexto de linha.
Eficiência
Quando dada a escolha, você deve escolher usar ADDCOLUMNS em favor de SUMMARIZE para adicionar essas colunas de dados adicionais. ADDCOLUMNS é muito mais eficiente na forma como adiciona os valores à tabela SUMMARIZE. SUMMARIZE usa uma operação Vertipaq Storage Engine para produzir a tabela base e, em seguida, ADDCOLUMNS aproveita a linhagem e a transição de contexto para adicionar as colunas de valor; Esta abordagem aproveita os recursos especiais do Power Pivot para realizar o trabalho da maneira mais eficiente. Para uma cobertura mais detalhada deste tópico, tem que leia este artigo dos italianos.
Outras funções de consulta DAX
Sei que não abordei todas as funções de consulta DAX nesta série de artigos. Há outros, alguns dos quais estão disponíveis apenas em versões mais recentes do Power Pivot (por exemplo, Power BI Desktop, Excel 2016). Se você estiver interessado em mais informações, você pode fazer alguma pesquisa online. Eu estarei cobrindo 1 show final na próxima semana: a função ROW.
Usos de consultas DAX
Semana que vem vou compartilhar meu artigo final nesta série onde explico algumas maneiras de usar consultas DAX no mundo real. Certifique-se de voltar na próxima semana, ou melhor ainda, subscreva a minha newsletter semanal para receber notificações quando há novos artigos.
