PowerPivot – DAX – Entendendo o contexto de execução
Olá a todos!
Hoje continuo com o tema DAX.
Introdução ao Contexto de Execução
Antes de entrar nas especificidades de cada função, conforme anunciei no meu último post que o faria, é importante frisar que o resultado obtido é sempre baseado no contexto atual. O contexto pode vir da tabela que você está trabalhando (novas colunas ao modelo) ou ao conjunto tratado pela measure.
E o que pode afetar/criar esse contexto?
Basicamente o contexto vem dos filtros aplicados (filtros nas funções ou slicers). Isso afeta o resultado esperado, por exemplo ao utilizar um slicer (segmentador de dados) temos uma redução de contexto.
Quando estamos trabalhando com valores relativos, por exemplo, considere o seguinte conjunto de dados e a seguinte DAX:
=SUM(Tabela[Categoria])
Temos um resultado semelhante a:
Neste momento estamos trabalhando com o conjunto total dos dados, mas ao marcar um slicer, por exemplo “Rock”, temos o filtro sobre o conjunto usado:
Ok, Thiago até aí ok, mas em que cenários que devo me atentar quanto a isso? Por exemplo, e se você possuir uma expressão DAX para calcular uma média, onde o denominador seja o somatório total das vendas ?
Algo como:
Vendas / Todas as vendas independente de categoria ?
Você poderia pensar em algo como =SUM(Tabela[Categoria])/SUM(Tabela[Categoria]), e manter a divisao por categorias, mas a conta não faria sentido, pois tanto o numerador quanto o denominador resultariam em um valor flutuante de acordo com o contexto.
Como resolver então?
Existem três funções que podem ajudar que são: CALCULATE(), ALL() e ALLExcept().
O CALCULATE serve para manipular o contexto, e o ALL e ALLExcept servem para retornar regiões dos dados de forma invariável ao contexto dos slicers (ALL) e podendo ter excessões (ALLExcept).
Na prática ficaria algo como:
=SUM(Tabela[Valor])/CALCULATE(SUM(Tabela[Valor]),ALL(Tabela))
Assim, mesmo aplicando um slicer, o resultado da medida (measure) para aquela categoria é baseado no valor total(denominador) e não mais no contexto dos slicers, veja:
Por hoje é só pessoal! :)
Este artigo é parte de uma série de artigos sobre PowerPivot e DAX. Confira os outros artigos sobre PowerPivot no link: http://zavaschi.com/index.php/tag/powerpivot/
Abraços!
Thiago Zavaschi
PowerPivot – DAX – Time Intelligent Functions – Golden Rules
Olá!
Estou desenvolvendo um trabalho de BI para um cliente baseado no Powerpivot. Esta solução vai fazer uso intensivo de datas.
Existem algumas “regras de ouro” (“Golden Rules”) quando se está trabalhando com datas no PowerPivot.
São elas (fonte: Kasper de Jonge, para variar, hehe, o cara é fera!):
- Nunca usar a coluna datetime da tabela fato nas funções de tempo.
- Sempre criar uma tabela Tempo (dimensão de tempo) separada.
- Crie relacionamentos entre as tabelas fato e a tabela de tempo.
- Tenha certeza de que os relacionamentos são baseados na coluna datetime (e não em outra chave artificial).
- A coluna datetime na tabela tempo deve estar na granularidade de dia (sem frações dentro do mesmo dia).
Nos próximos posts veremos melhor estes processos.
Este artigo é parte de uma série de artigos sobre PowerPivot e DAX. Confira os outros artigos sobre PowerPivot no link: http://zavaschi.com/index.php/tag/powerpivot/
Abraços,
Thiago Zavaschi
PowerPivot – Data Analysis Expressions DAX – Intro
Então pessoal. :)
Creio que muitos que acompanham as evoluções do PowerPivot já devem ter se deparado com o seguinte pensamento: falta material sobre DAX em português e as referências externas (em inglês ao menos) são sempre de blogs de especialistas (Rob Collie, Kasper de Jonge, Vidas Matelis).
A minha proposta é seguir na mesma linha (já que meu principal canal de difusão de informação é este blog mesmo) e fornecer um pouco de conteúdo sobre DAX, mas em português. Já fiz alguns posts de introdução ao PowerPivot, então creio que valha a pena explorar num nível técnico mais profundo. Claro que se sugirem dúvidas podem perguntar :).
DAX é a abreviação para Data Analysis Expressions. É uma “evolução” da liguagem de fórmulas do Excel e só funcionará par a manipulação da base do PowerPivot dentro do seu workbook.
As DAX são importantes pois: devido a semelhança com a (já conhecida) linguagem de fórmulas do Excel e também por possibilitarem uma flexibilidade na análise dos dados. A capacidade de escrever as DAX e elas serem sensíveis aos slicers do Excel 2010 gera um ganho muito grande de capacidade de análise.
Agora vamos por a mão na massa. Neste artigo apresento uma introdução dos dois tipos de expressões DAX que podem ser criadas. Nos próximos artigs vou assumir que os concents apresentados aqui já estão assimilados.
Temos dois lugares onde podemos utilizar as DAX:
1) Através de novas colunas associadas a uma determinada tabela importada no PowerPivot, conhecidas como “calculated columns”. O resultado desta expressão DAX é armazenado juntamente aos seus dados. O uso deste tipo de DAX (em geral) não deve acarretar problemas de performance.
Há algumas experiências (do pessoal que citei no início do post principalmente) que relatam que a taxa de compressão adquirida pelo VertiPaq atravém de colunas importadas é maior que sobre as colunas calculadas. Não fiz um teste com uma massa grande ainda, mas o que eles querem dizer é que: nos casos em que o cálculo pode vir já feito no momento da importação a compressão (sobre o tamanho final do workbook) possui maiores ganhos. A seguir um exemplo da criação de uma coluna utilizando DAX ou trazendo da fonte de dados diretamente. Há outras utilizadades para colunas computadas que veremos ao longo do artigo e em artigos futuros.
Por exemplo:
Usar a expressão DAX como coluna computada: =Tabela[coluna1] * Tabela[coluna2].
É equivalente a importar (do SQL Server por exemplo) com uma coluna computada na query:
SELECT *, coluna1 * coluna2 FROM Tabela
2) Através de DAX Measures (medidas) que são calculadas tendo como base uma tabela ou uma coluna. Este cálculo será executado para cada célula que o contiver. Então atenção quando utilizá-las!
Uma DAX Measure é basicamente uma fórmula DAX (estilo a usada para a criação de uma coluna) associada a um nome (e a uma tabela).
Um aspecto positivo das DAX Measures é que elas operam sobre o conjunto atual da PivotTable. A cada intereçaão dos slicers temos o recálculo dos valores das measures e com isso temos a atualização dos valores mostrados nos gráficos e tabelas.
Por exemplo:
Vou criar uma DAX measure para o exemplo mostrado na figura anterior que fará a soma dos elementos da coluna nomeada “Valor”.
Ficar complicado (por ter muitos elementos) eu usar a coluna Valor como fonte do slicer (ver figura abaixo). Então o que fazer nestes casos?
Conforme vimos no tópico anterior, é possível criar colunas através de expressões DAX. uma saida então é criar uma nova coluna que agrupe os valores. (isso não é um case, os exemplos são apenas para fins didáticos, por isso são simples).
Vou criar uma coluna que categorize os elementos da coluna valor quando são menores que 10, entre 10 e 20, e maiores que 20. Em exemplos futuros abordaremos melhor esta estratégia de categorização.
Notas sobre a sintaxe da DAX:
* Estou usando o operador IF, que apesar de simples é extremamente importante para se obter os resultados desejados.
* O operador de concatenação de strings é o &.
* Para me referir a uma tabela eu simplesmente digo o nome da mesma, para me referenciar a uma coluna utiliza-se: NomeTabela[NomeColuna].
* Operadores MAX e MIN (perceba que não são os novos MAXX e MINX, estes veremos em artigos futuros) calculam os valeres máximos e mínimos das colunas passadas como parâmetro a eles).
* Aninhei um segundo IF no parâmetro “else” do primeiro.
A expressão resultante é a seguinte (também pode ser observada na figura abaixo):
=IF(Tabela[Valor] < 10, MIN(Tabela[Valor]) & " |- 10", IF(Tabela[Valor] <= 20, "10 |- 20", "20 |- " & MAX(Tabela[Valor])))
Usar essa nova coluna (GrupoValor) como um slicer tornou a coisa bem mais interessante, não? :)
Agora seleciono a PivotTable e vou em “New Measure” lá na ribbon. E crio a seguinte expressão DAX (perceba que também aparece na PowerPivot Field List com um pequeno ícone ao lado):
=SUM(Tabela[Valor])
Adicione esta measure como valor na PivotTable e adicione a coluna GrupoValor como um slicer. Pronto! Selecione os slicers e veja que o valor total é alterado de acordo com o que foi marcado! :)
Aguardem novos posts sobre DAX em um futuro (espero que) não muito distante!
Este artigo é parte de uma série de artigos sobre PowerPivot e DAX. Confira os outros artigos sobre PowerPivot no link: http://zavaschi.com/index.php/tag/powerpivot/
Abraços,
Thiago Zavaschi