Nível: intermediário
Eu escrevi alguns artigos contendo referências incorporadas a consultas DAX e funções de tabela DAX no passado. Hoje estou escrevendo um artigo que apresenta a linguagem de consulta DAX desde o início para que você possa aprender como usá-la e como ela o ajudará a se tornar uma superestrela do Power BI.
O que é uma linguagem de consulta?
Uma linguagem de consulta é uma linguagem de programação que permite enviar uma solicitação a um banco de dados e, em seguida, receber dados na forma de uma tabela. Se você tiver apenas algumas centenas de linhas de dados, isso não é muito útil. E, por outro lado, tem milhões de linhas de dados, torna-se muito útil.
A diferença mais importante entre DAX quando usado em medidas e colunas calculadas e DAX como uma linguagem de consulta é que a linguagem de consulta sempre retorna uma tabela. A tabela resultante consistirá em uma ou mais colunas, nenhuma ou mais linhas e dados em nenhuma ou mais das células da tabela.
Por que eu preciso de uma linguagem de consulta?
Quando você cria um modelo de dados no Power BI ou Power Pivot para Excel, você está basicamente carregando dados tabulares em um banco de dados de relatórios dedicado para o propósito expresso de análise e / ou relatórios. Depois de carregado, tu (o autor do relatório) você normalmente gasta horas de esforço construindo o modelo de dados para que possa ser usado para agregar valor ao seu negócio / trabalho. Finalmente, uma vez que tudo isso esteja feito, a maneira mais comum de usar seu novo modelo de dados é criar relatórios e tabelas dinâmicas que apresentam dados a um usuário de uma maneira fácil de entender.
A seguir, mostra um exemplo de um relatório interativo do Power BI executado a partir de um modelo de dados do Power BI.
E aqui está uma tabela dinâmica em execução em um modelo de dados Excel Power Pivot.
Ambas as visualizações acima são resumos dos dados subjacentes e em ambos os casos na realidade, não consigo ver nenhum dos dados subjacentes em si, apenas o resumo. As ferramentas de visualização acima são excelentes e extremamente úteis. porém, eles geralmente não são as melhores ferramentas se você deseja ver os próprios dados subjacentes ou se deseja extrair conjuntos de dados muito grandes por qualquer motivo.
Depois de ter investido todo esse esforço na construção de seu modelo de dados, vai querer usá-lo ao máximo. O Power Pivot e o Power BI podem lidar com muitos milhões de linhas de dados (ao contrário do Excel tradicional) e, portanto, a “forma antiga” apenas olhar para a mesa subjacente não é realmente prático neste mundo novo e feliz..
Mostre-me os dados subjacentes!
Se você pegar a tabela dinâmica que mostrei acima, a maioria dos usuários do Excel saberia que eles podem clicar duas vezes em qualquer célula de valor (vamos dizer o 31,6% representa as vendas de bicicletas de montanha para pessoas entre 30 e 40 anos de idade). Quando você clica duas vezes em uma mesa dinâmica tradicional como esta, algo mágico acontece: uma cópia dos dados subjacentes é extraída da fonte e aparece magicamente em uma nova planilha no Excel. Assim que terminar de olhar para a cópia dos dados, você pode deletar a nova planilha e você estará de volta ao ponto de onde começou: a vida é boa.
Quando você clica duas vezes em uma mesa dinâmica tradicional como esta, você está efetivamente enviando uma consulta ao banco de dados subjacente e a resposta à consulta é uma tabela de dados.
Mas funciona com o Power Pivot?
Em definitivo, sim e não dependendo do que você procura. A experiência é diferente se você tiver um modelo de dados Excel Power Pivot ou estiver conectado a uma fonte de dados SSAS externa. Em minha experiência, os resultados são, na melhor das hipóteses, inconsistentes e, no pior dos casos, eles podem não entregar o que você espera. Mas a boa notícia é que, com DAX como linguagem de consulta, você pode facilmente escrever qualquer consulta que desejar e extrair exatamente o subconjunto de dados de que precisa. Você não precisa mais depender de clicar duas vezes em sua tabela dinâmica para interrogar seus dados.
É hora de aprender DAX como uma linguagem de consulta
Nós vamos, chega de conversa, é hora de levar o DAX a sério como uma linguagem de consulta. Você investiu tempo e esforço na criação de seu modelo de dados no Power BI ou Power Pivot e agora, acertadamente, você deseja extrair um subconjunto de seus dados para que possa dar uma olhada neles. Comecemos.
Primeiro selecione a ferramenta apropriada
Existem 3 abordagens gerais que você pode adotar para extrair uma tabela de dados de seu fabuloso modelo de dados.
- Use uma ferramenta dedicada como DAX Studio ou SQL Server Management Studio. Escrevi um artigo que descreve como instalar e usar o DAX Studio aqui.
- Escreva um “nova tabela” e Power BI Desktop. Eu escrevi um artigo sobre como usar esse recurso aqui.
- Use um “truque de mesa” de Excel que aprendi com Marco Russo para extrair uma tabela diretamente no Excel. Abordarei esse truque posteriormente nesta série de artigos..
Vou usar o DAX Studio neste artigo do blog, mas você pode usar o Power BI "Nova Tabela" se quiser ir em frente e não tiver (ou você quer) DAX Studio. De fato, se você quer aprender essas coisas, então eu ainda devo usar qualquer abordagem. Em minha experiência, você não pode aprender novos conceitos simplesmente lendo. Porém, observe que o uso de Power BI e Excel como ferramenta de consulta deve ser considerado como uma extração temporária dos dados. Depois de verificar os dados que deseja, Eu normalmente derrubaria a tabela para que você não acabasse armazenando dados redundantes (você sempre pode extraí-los novamente se precisar).
Vamos começar com a declaração de avaliação
Todas as consultas DAX devem começar com a instrução EVALUATE. Porém, há uma exceção. Quando você usa o botão “Nova tabela” de Power BI para escrever uma consulta, apenas omita esta instrução EVALUATE. Este é certamente outro exemplo de “Açúcar de sintaxe” onde os desenvolvedores protegem o usuário de algumas das sintaxe mais confusas. Se você estiver acompanhando e testando minhas consultas abaixo no Power BI, apenas omita a instrução EVALUATE ao escrever as consultas. O que mais, se você quiser mover sua consulta DAX Studio para uma tabela no Power BI, você pode remover a instrução EVALUATE naquele momento.
Conexão do DAX Studio ao banco de dados
Quando executo o DAX Studio, Sou solicitado a conectar ao meu banco de dados. Neste caso, Eu tenho uma instância do Power BI Desktop com um modelo de dados Adventure Works em execução no meu PC e o DAX Studio me dá a opção de me conectar a ele. Você também pode se conectar a modelos de dados do Excel Power Pivot e servidores SSAS tabulares.
Para uma descrição mais completa da interface do usuário DAX Studio, ler meu outro post que mencionei anteriormente. Pelo contrário, vamos entrar nele.
Devolva uma tabela existente
a consulta dax mais fácil que você pode digitar é simplesmente retornar uma cópia completa de uma tabela existente.
EVALUATE
Customers
Quando eu executar esta consulta no DAX Studio (que mostra como 1 a seguir) e, em seguida, pressionar F5 para executar, toda a tabela é devolvida (que mostra como 2). Um total de 18,484 linhas de dados (Várias tabelas de dados no Power Pivot 3).
Isso em si não é muito útil, pois você provavelmente já tem esta tabela em algum sistema de origem..
Lista de valores únicos
em dax há muitas funções que retornam tabelas em vez de valores escalar. Eu cobri este tópico em profundidade na semana passada para que você possa lê-lo aqui, se quiser..
Deixe-me agora olhar para o recurso ALL.
EVALUATE
ALL(Produtos[Categoria])
Quando eu executar esta tabela função como uma consulta, Eu realmente posso “Assistir” os resultados da consulta. Neste caso, a consulta retorna uma tabela de coluna única que consiste em 4 filas, todas as categorias de produtos exclusivos.
A seguir, vamos ver VALORES.
EVALUATE
VALUES(Produtos[Categoria])
Neste caso, a função tabela de valores retorna exatamente o mesmo resultado.
Agora vamos dar uma olhada no DISTINCT.
Evaluate
DISTINCT(Produtos[Categoria])
E a mesma coisa de novo aqui.
todas as três funções de tabela retornam a mesma tabela neste caso. Na realidade, todo mundo faz coisas diferentes em diferentes cenários de filtragem. Esta é uma das melhores coisas sobre consultas DAX e isso é que você pode “inspecionar” o que está acontecendo com essas funções de tabela em diferentes cenários. Mais sobre isso depois.
Uma tabela de duas colunas
A função ALL pode tomar 1 ou mais colunas como entrada (ou uma mesa). No exemplo a seguir, Usei TUDO para materializar uma tabela de todas as combinações de categoria de produto e subcategoria de produto. Existem 37 combinações únicas no total.
Uma mesa filtrada
A maioria das pessoas que conhecem o DAX saberá sobre o recurso FILTER. a função filtro retorna uma cópia filtrada de uma tabela. por exemplo, ver exemplo abaixo:
EVALUATE
FILTER(Produtos, [Vendas totais] > 200000)
Esta é uma consulta muito mais interessante porque retorna uma tabela contendo todos os produtos que venderam mais do que $ 200,000 em vendas o tempo todo. Existem 49 desses produtos.
Modifique o contexto do filtro
Quando você escreve uma medida DAX ou uma coluna calculada, só existe uma maneira de alterar o contexto do filtro e é usando a função CALCULAR. CALCULATE retorna um valor escalar após a aplicação de filtros. CALCULATETABLE é irmão de CALCULATE. Como você provavelmente pode adivinhar, A CALCULATETABLE retorna uma tabela depois de aplicar filtros.
esta próxima consulta retorna exatamente o mesmo resultado da última consulta.
EVALUATE
CALCULATETABLE(Produtos, FILTRO(Produtos, [Vendas totais] > 200000))
a principal diferença com o uso de calculável em comparação com apenas usando filtro é que o cálculo pode retornar uma tabela diferente para a função do filtro.
EVALUATE
CALCULATETABLE(Vendas, FILTRO ( Produtos, [Vendas totais] > 200000))
a consulta anterior usa a mesma função de filtro de antes, mas em vez de devolver uma tabela contendo os produtos que venderam mais do que $ 200,000, retorna todas as transações de vendas reais que contribuíram para a qualificação desses produtos (veja os resultados abaixo).
Combine funções de mesa
Agora que você vê como algumas das funções de tabela padrão funcionam em uma consulta, vamos ver como combiná-los em uma única consulta.
EVALUATE
CALCULATETABLE(
VALORES(Produtos[Subcategoria]),
FILTRO(Produtos, [Vendas totais] > 50000)
)
Esta consulta primeiro aplica um filtro a todos os produtos que venderam mais de $ 50,000 (linha 4 a seguir) e, em seguida, retorna uma lista de subcategorias de produtos exclusivas que contêm esses produtos.
Uma coisa interessante sobre os resultados da consulta anterior é que o “Capacetes” estão na lista (a única subcategoria que não é para bicicletas). Para ver quais são os produtos exatos onde foram vendidos, é simplesmente uma questão de escrever outra consulta como segue.
EVALUATE
CALCULATETABLE(
VALORES(Produtos[Nome do Produto]),
FILTRO(Produtos, [Vendas totais] > 50000),
Produtos[Subcategoria]="Capacetes"
)
Observe como consegui reutilizar a estrutura de consulta original para retornar a lista de nomes de produtos com apenas uma pequena alteração na consulta.
Haverá mais na próxima semana
Este é um bom tópico, muito grande para uma única postagem de blog. Esta semana, apresentei o tópico de consultas DAX, Eu mostrei como as consultas podem ser escritas para retornar tabelas e como as funções da tabela podem ser combinadas para interrogar dados. Na próxima semana, apresentarei algumas novas funções especiais de consulta que expandem ainda mais os recursos.
