Tabelas expandidas no LuckyTemplates

Neste tutorial, vamos dar uma olhada em um cenário muito específico que tenho certeza de que todos vocês já encontraram ao trabalhar com DAX – a tabela expandida . Tabelas expandidas podem atrapalhar seu cálculo se você não souber como elas realmente funcionam. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Para este exemplo, estamos usando o banco de dados Contoso, que contém a tabela sales , customer table , date table , products table , products category table e products subcategoria table .

No lado esquerdo, temos a relação um-para-muitos entre as tabelas de produtos, categoria, subcategoria e vendas. Também temos uma relação um-para-muitos entre cliente e vendas, bem como datas e vendas.

Índice

• Exemplo 1
• Exemplo #2
• Vendas Vermelhas #1
• Vendas Vermelhas #2
• Vendas Vermelhas #3
• Verificando Tabelas Expandidas
• Definindo Tabelas Expandidas
• Fatiando a coluna de categoria e usando tabelas expandidas
• Explicando Red Sales 2
• Explicando Red Sales 3
• Usando o DAX Studio para verificar tabelas expandidas
• Conclusão

Exemplo 1

Digamos que queremos criar um relatório a partir dessa coluna de categoria na tabela de categorias e usar uma medida que calcula quantos clientes existem na tabela de clientes. Queremos dividir o número da mesa do cliente pela categoria.

Se eu colocar um filtro na tabela de categorias, esse filtro irá para a tabela de subcategorias, depois alcançará a tabela de produtos e, finalmente, alcançará e filtrará a tabela de vendas. Mas esse filtro não conseguirá filtrar a mesa do cliente, a menos que ativemos a filtragem bidirecional.

Vamos voltar ao relatório para corrigir esse cálculo. Podemos agrupar esse cálculo dentro da função CALCULATE e, em seguida, escrever Sales. Podemos ver que desta vez não estamos repetindo o mesmo número para cada célula do visual.

Para verificar se esse cálculo está correto, podemos trazer a chave do cliente da tabela de vendas para agregar e, em seguida, fazer uma contagem (distinta) sobre ela.

Você pode ver que estamos retornando o mesmo valor para cada linha. Usando a tabela de vendas dentro da função CALCULAR, conseguimos corrigir o cálculo.

Exemplo #2

Vamos para o próximo exemplo. Para este, queremos identificar o valor das vendas dos produtos tintos em 2007, 2008 ou 2009. Dependendo da seleção do fatiador, também quero retroceder um ano.

Por exemplo, se eu selecionar 2009, quero relatar as vendas vermelhas de 2008. Se eu selecionar 2008, também quero relatar as vendas vermelhas de 2007.

Vendas Vermelhas #1

O cálculo de nossas vendas totais é basicamente uma soma dos dados de vendas. No contexto da linha, vamos multiplicar a quantidade pelo preço líquido.

Vamos criar uma nova medida e nomeá-la como Red Sales. Vamos escrever CALCULAR e depois Vendas totais. Vamos a tabela Vendas para mostrar que a cor dos produtos é igual ao vermelho.

Em seguida, escreveremos sobre as datas na coluna de datas.

Quando trazemos esse cálculo em um visual de cartão, ficamos com um branco.

Vendas Vermelhas #2

O que esta acontecendo aqui? Vamos tentar reescrever esse cálculo e ver se conseguimos obter um resultado. Vamos criar uma nova medida e nomeá-la como Red Sales 2 e usar a primeira parte do cálculo para Red Sales 1.

Vamos iniciar outro CALCULATE sobre o primeiro CALCULATE.

Vamos trazer essa medida na matriz e ver os resultados. Se selecionarmos 2008, obteremos 51.947. Se selecionarmos 2009, obtemos 24.343. Por fim, se selecionarmos 2010, obtemos 39.724.

Vendas Vermelhas #3

Existe outra maneira de escrever esse cálculo. Vamos escrever uma nova medida e chamá-la de Red Sales 3, então usar a função CALCULAR.

Vamos calcular as vendas totais e escrever que, para os produtos, a cor é igual ao vermelho. Em seguida, use a função SAMEPERIODLASTYEAR nas datas.

Se arrastarmos a medida nº 3 para o visual do cartão, você verá que esses dois valores de cartão estão retornando o mesmo valor, o que está correto.

Mas há algo errado com nosso primeiro cálculo, pois estamos retornando um valor em branco e não o correto.

Vamos ver o que realmente está acontecendo aqui. Agora que vimos alguns cálculos em tabelas expandidas, vamos entender a teoria por trás deles.

Antes de entender o que é uma tabela expandida, você precisa entender que todas as tabelas que temos aqui são chamadas de tabelas base .

Então, quando essas tabelas se tornam tabelas expandidas? Depois de criar um relacionamento muitos-para-um entre uma tabela e outra, a tabela base se torna a tabela expandida.

Verificando Tabelas Expandidas

Mas como podemos verificar se uma expansão de tabela está realmente acontecendo? Bem, você pode usar a palavra-chave relacionada em qualquer tabela. Se você conseguir acessar a coluna de um lado, saberá que pode ocorrer uma expansão da tabela.

Vamos para a tabela de vendas e criar uma nova coluna calculada.

Digamos que queremos recuperar a cor do produto da tabela de produtos para esta chave de produto específica. Usaremos RELATED, que fornecerá apenas a lista de colunas no IntelliSense que podem ser expandidas a partir da tabela de vendas.

Podemos ver que a tabela customer e a tabela sales têm um relacionamento de muitos para um. Também podemos ver a lista de colunas da tabela de clientes, colunas da tabela de datas e colunas de produtos.

Ao selecionar Produtos[Cor], podemos criar uma nova coluna na tabela de vendas com a ajuda da palavra-chave RELATED. RELATED apenas nos dá acesso às colunas da tabela para as quais a tabela base pode realmente se expandir.

Se mudarmos a natureza dessa relação de muitos para um para muitos para muitos, esse cálculo deixará de funcionar.

Vamos mudar a natureza desse relacionamento para muitos-para-muitos. Podemos ver na parte inferior que temos um símbolo de aviso.

Quando voltamos para a tabela de vendas, vemos a mensagem de erro que diz que a coluna Products[Color] não existe ou não tem relação com nenhuma tabela.

No contexto atual, o aviso que recebemos não é muito amigável. Basicamente, isso significa que a tabela de vendas não pode se expandir para a tabela de produtos porque não podemos acessar apenas um único valor para aquele item de linha específico.

E como estamos usando o relacionamento muitos-para-muitos, a expansão da tabela não acontece e RELATED não funciona.

Vamos voltar à exibição do diagrama e corrigir esse cálculo. Mudaremos a natureza do relacionamento para muitos-para-um e ativaremos esse relacionamento para fazer o cálculo funcionar.

Definindo Tabelas Expandidas

Antes de começarmos a ver os cálculos que já fizemos no relatório, vamos reiterar a definição da tabela expandida.

Se você tiver um modelo de dados com um esquema em estrela , a tabela de fatos se expandirá para todas as tabelas no modelo de dados se houver um relacionamento muitos para um entre as dimensões e a tabela de fatos.

Se você tiver um esquema floco de neve , a tabela de subcategorias de produtos e a tabela de categorias serão expandidas para a tabela base, que neste caso é a tabela de produtos. A tabela de vendas é a tabela base, que se expande para todas as outras tabelas.

Nos bastidores, a tabela de vendas terá todas as colunas em uma única tabela. Observe que a expansão da tabela é apenas um conceito lógico, portanto, não vai expandir e aumentar o tamanho do seu modelo de dados.

A tabela expandida só entra em cena quando você está se referindo a uma tabela (que é a tabela base) e quando você tem uma relação muitos-para-um com outras tabelas.

Fatiando a coluna de categoria e usando tabelas expandidas

Vamos tentar corrigir os cálculos que já fizemos na visualização do relatório. Neste exemplo, estamos dividindo pela coluna de categoria da tabela de categorias de produtos e estamos tentando contar quantos clientes existem.

Então, o que estamos realmente fazendo aqui? Estamos usando a tabela de vendas expandida como referência. Uma vez que o contexto do filtro contenha um valor da tabela de categoria, esse filtro alcançará a tabela de vendas da subcategoria produtos e diretamente para as vendas.

Como vendas é uma tabela expandida e estamos usando essa referência dentro da função CALCULAR, a tabela de vendas também conterá a coluna da tabela de clientes. Quando estamos aplicando um filtro sobre a tabela de vendas, indiretamente, estamos filtrando também a tabela de clientes.

Vamos voltar aos nossos cálculos de vendas vermelhas e tentar entender o que realmente está acontecendo. Começaremos com o cálculo em branco. Se selecionarmos essa medida, você verá que estamos escrevendo um código aninhado. Temos CALCULATE, depois Total Sales, FILTER sobre as vendas e SAMEPERIODLASTYEAR.

Vamos quebrar esse cálculo passo a passo. Primeiro, precisamos identificar o contexto de filtro externo que existe fora do cálculo.

Selecionamos 2008 na segmentação de números do ano civil.

A partir deste contexto de filtro, a tabela de vendas será avaliada. A tabela de vendas conterá apenas a linha para o número do ano civil de 2008 e a cor do produto será vermelha. Temos dois filtros aqui: um que é criado pelo contexto do filtro e outro que está sendo criado pelo filtro .

O SAMEPERIODLASTYEAR está sendo avaliado no contexto do filtro onde o ano é 2008. Ele receberá uma lista de datas em 2008 e deslocará essas datas de 2008 para 2007. A tabela que será retornada por this conterá apenas as datas de 2007.

Após a conclusão dessas duas operações, a função CALCULATE prepara o contexto do filtro e aplica esses dois filtros ao contexto do filtro. Quando aplicamos isso, temos um contexto de filtro da cor do produto igual ao vermelho, e na coluna do ano, temos um filtro sobre 2007 e 2008.

Portanto, para esse modelo de dados, não há uma única transação que exista em dois anos diferentes. Quando CALCULATE tentar mesclar esses dois filtros em uma condição e , dirá que o ano deve ser 2008 e 2007 e que a cor do produto deve ser vermelha.

Quando estamos filtrando a tabela de vendas com o contexto de filtro do ano de 2008, também estamos filtrando indiretamente a tabela de datas. O estável de vendas tem uma relação de muitos para um com o estável de datas.

Vamos criar um novo cálculo para identificar quantas linhas existem na tabela de datas para as datas relacionadas. Vamos escrever CALCULATE, depois da tabela de datas. Então vamos FILTER ALL da tabela de vendas, e vamos dizer que as datas RELACIONADAS no número do ano civil devem ser iguais a 2008.

Dentro de CALCULATE, não referenciamos nenhum filtro na tabela de datas. Estamos simplesmente verificando se as datas no número do ano civil devem ser a partir de 2008. Idealmente, esse filtro não deve ser capaz de filtrar a tabela de datas. Também estamos usando a função , que irá ignorar o contexto do filtro que virá do slicer.

Vamos criar um novo cartão para este cálculo. você pode ver que estamos retornando 348 linhas.

Então, como é possível que, de uma tabela de datas de 2.500 linhas, estejamos retornando apenas 348 linhas? Se formos usar uma tabela expandida, também filtraremos indiretamente a outra tabela, que está conectada por meio de um relacionamento muitos-para-um.

Embora não tenhamos nenhum filtro para o ano atual, ainda estamos limitando o número de linhas visíveis para a tabela de dimensões que temos de um lado.

Quando retornamos a tabela de vendas, também retornamos uma versão filtrada da tabela de datas, tabela de clientes, tabela de produtos, tabela de categorias de produtos e tabela de subcategorias de produtos.

Explicando Red Sales 2

Vamos passar para o próximo cálculo, que é o Red Sales 2. Começaremos com o CALCULATE externo porque se estivermos aninhando essa função em qualquer cenário, o CALCULATE externo deve preparar o contexto do filtro para o CALCULATE interno.

No slicer, selecionamos o ano civil de 2008. Quando a função SAMEPERIODLASTYEAR obtém a data de 2008, ela mudará essas datas em 2007 e será o contexto FILTER para o cálculo interno. Este CÁLCULO interno avaliará o ano de 2007.

A função FILTER irá filtrar a tabela de vendas, e a tabela de vendas será limitada apenas pela linha onde o ano é 2007. Uma vez que temos as linhas de 2007, vamos verificar se os produtos relacionados são iguais a vermelho.

Ao contrário do primeiro cálculo, não estamos retornando duas camadas diferentes. Essas duas camadas diferentes não estão sendo aplicadas no contexto FILTER, portanto, a tabela retornada pela função FILTER conterá todas as linhas da tabela sales.

Uma vez aplicado este filtro, vamos obter o valor das vendas do ano de 2007, bem como os produtos que são iguais ao vermelho, enquanto no primeiro cálculo, estávamos retornando o ano de 2008 e 2007.

Desta vez, preparamos o contexto do filtro para a tabela de vendas usando a função SAMEPERIODLASTYEAR e aninhando a função CALCULATE dentro de outra CALCULATE.

Explicando Red Sales 3

Para o terceiro exemplo, temos uma função CALCULATE muito simples com duas instruções: uma é a cor do produto é igual a vermelho e, em seguida, SAMEPERIODLASTYEAR em datas.

A função SAMEPERIODLASTYEAR será avaliada em um contexto de filtro, que conterá apenas as datas de 2007. A cor dos produtos aplicará um contexto de filtro de vermelho sobre a tabela de produtos, que por sua vez filtrará a tabela de vendas e conterá apenas as linhas para os produtos vermelhos.

Quando esses dois valores são aplicados no contexto do filtro, eles vão filtrar a tabela de vendas, mas não a tabela de vendas expandida. você pode ver que em nenhum lugar do código fizemos referência à tabela de vendas expandida.

Este é o cenário ideal que você deve usar se estiver tentando referenciar a tabela expandida. Ao usar a tabela expandida, os cálculos podem se tornar muito complicados e, às vezes, você não consegue identificar por que seus cálculos estão retornando um resultado incorreto.

Se você não entender o conceito de tabelas expandidas, poderá desenvolver esse código específico como uma medida de modelo e, em seguida, implantá-lo na produção. Outros usuários podem começar a criar outras medidas sobre essa medida e podem não conseguir descobrir por que os cálculos não estão funcionando porque simplesmente não possuem esse nível de compreensão sobre a linguagem DAX.

Se você estiver trabalhando com DAX, tente sempre colocar o filtro em uma única coluna, pois quando você usa uma única coluna, o conceito de tabelas expandidas não é aplicado. Por exemplo, quando você usa PRODUCTS [Color] = “Red” , o filtro atinge a tabela de vendas, mas esse filtro não pode alcançar a tabela do cliente porque não estamos usando a tabela de vendas expandida.

Ao usar uma única coluna, não nos referimos ao conceito de tabelas expandidas. Estamos simplesmente aplicando um filtro básico sobre a tabela de vendas, que não se propaga para as demais tabelas.

Usando o DAX Studio para verificar tabelas expandidas

Antes de encerrar, vamos usar o DAX Studio para verificar nosso código para o Red Sales 3. Vamos para a guia View, clique em Performance Analyzer e inicie a gravação. Atualizaremos esse visual e selecionaremos um cartão em branco.

Das ferramentas externas, vamos lançar o estúdio DAX

Podemos ver que na tela de resultados na parte inferior, obtemos um espaço em branco.

Ativaremos o Server Timings para entender as consultas que estão sendo geradas nos bastidores. Iremos para a guia Horários do servidor e a expandiremos para ver tudo.

Estamos realizando um JOIN na tabela de vendas no lado esquerdo. Também estamos executando um JOIN na chave do produto da tabela de produtos.

Na cláusula WHERE, diz que as datas no número do ano civil devem ser iguais a 2008 e que a cor do produto deve ser vermelha.

Em seguida, temos outra condição que diz Dates[Date], seguida por um intervalo de datas. Então, quais são essas datas? O ideal é que essas datas sejam de 2008, mas são de 2007 quando mudamos a formatação desses números no Excel.

É por isso que estamos retornando um valor em branco. Estamos dizendo que o ano civil deve ser 2008 e aplicando uma condição AND de que as datas devem ser 2007. Este 2007 é devido à função SAMEPERIODLASTYEAR que usamos no cálculo.

Vamos alterar a medida e a referência para Red Sales 2. Assim que executarmos o código, você pode ver que estamos obtendo as datas da tabela de datas, então estamos filtrando a coluna de datas para o número do ano civil de 2008.

Desta vez, podemos retornar um resultado porque não estamos aplicando um filtro na cláusula WHERE e estamos dizendo que as datas no número do ano civil devem ser iguais a 2008.

Onde encontrar a armadilha DAX oculta
Contando clientes ao longo do tempo usando DISTINCTCOUNT no LuckyTemplates
Análise de novos clientes usando DAX avançado

Conclusão

Felizmente, este tutorial foi útil para entender o que é uma tabela expandida e como ela pode atrapalhar seus cálculos. Tabelas expandidas não são realmente intuitivas e nem fáceis de entender.

Para mais informações sobre tabelas expandidas, não se esqueça de se inscrever no canal de TV LuckyTemplates. Temos uma grande quantidade de conteúdo sendo publicado o tempo todo, meu e de vários criadores de conteúdo, todos dedicados a melhorar a maneira como você usa o LuckyTemplates e a Power Platform.