Prévia do material em texto
1 2 Bem-vindo! Para quem não me conhece eu sou o Marcelo Leite, instrutor e consultor na área de BI. Trabalho a mais de 8 anos na área da educação, formei centenas de alunos na área e tenho como objetivo difundir o conhecimento da melhor ferramenta de BI do mercado para aqueles que buscam o aprendizado. Acredito que o conhecimento deve ser compartilhado com todos aqueles que o procuram, por essa razão criei também um canal no YouTube aonde forneço dicas e tutoriais gratuitos sobre a ferramenta número um em Business Intelligence. Você pode acessar esse canal clicando no link - http://www.youtube.com/c/PowerBInaprática Se você não conhece o DAX, ou tem um conhecimento prévio dentro da linguagem esse e- book vai te auxiliar a criar medidas e funções com a linguagem dentro do Power BI. Aqui listei as principais funções e algumas explicações sobre elas para bom entendimento e aplicação dentro da ferramenta. “Hoje o profissional que sabe trabalhar com Power BI tem grande destaque no mercado de trabalho, no futuro, o profissional que não souber trabalhar com Power BI será substituído por outro que saiba” Marcelo Leite c.o - Power BI na prática http://www.youtube.com/c/PowerBInapr%C3%A1tica 3 Sumário Bem-vindo! .................................................................................................................................... 2 O que é DAX?................................................................................................................................. 4 Por que DAX é tão importante? .................................................................................................... 4 Sintaxe ....................................................................................................................................... 4 Observação ................................................................................................................................ 5 Tarefa: criar uma fórmula de medida ....................................................................................... 6 Funções ..................................................................................................................................... 7 Contexto .................................................................................................................................... 8 Contexto de linha ...................................................................................................................... 8 Contexto de filtro ...................................................................................................................... 8 Resumo .......................................................................................................................................... 9 Modos de conexão com Power BI ............................................................................................... 11 Importância da Modelagem de Dados: ....................................................................................... 11 Principais funções DAX ................................................................................................................ 12 Funções de Tabela ................................................................................................................... 12 FILTER & IF ................................................................................................................................... 16 Introdução a CALCULATE............................................................................................................. 18 Transição de Contexto ................................................................................................................ 25 VALUES, HASONEVALUE e SELECTEDVALUE ............................................................................... 27 Funções de inteligência e tempo ................................................................................................ 28 Opção de "Marcar Tabela de Data" no Power BI ........................................................................ 33 Ranking com RANKX e TOPN ....................................................................................................... 38 4 O que é DAX? O DAX é uma coleção de funções, operadores e constantes que podem ser usados em uma fórmula, ou expressão, para calcular e retornar um ou mais valores. Resumindo, o DAX ajuda você a criar novas informações de dados já presentes em seu modelo. Por que DAX é tão importante? É muito fácil criar um novo arquivo do Power BI Desktop e importar alguns dados nele. Você pode até mesmo criar relatórios que mostrem informações valiosas sem usar nenhuma fórmula DAX. Mas e se você precisar analisar o percentual de crescimento em diferentes categorias de produto e para intervalos de datas diferentes? Ou você precisa calcular o crescimento ano a ano comparado às tendências do mercado? As fórmulas DAX oferecem essa e outras funcionalidades importantes também. Aprender a criar fórmulas DAX eficientes ajudará você a tirar o máximo proveito de seus dados. Quando obtém as informações de que precisa, você pode começar a resolver problemas comerciais reais, que afetam o seu resultado final. Esse é o potencial do Power BI, e o DAX ajudará você a aproveitá-lo. Sintaxe Antes de criar suas próprias fórmulas, vamos dar uma olhada na sintaxe das fórmulas DAX. A sintaxe inclui os vários elementos que compõem uma fórmula, ou mais resumidamente, o modo como a fórmula é escrita. Por exemplo, vamos examinar uma medida de uma fórmula DAX simples. Esta fórmula inclui os seguintes elementos de sintaxe: A. O nome da medida Total Sales. B. O operador de sinal de igual (=) indica o início da fórmula. Quando calculada, ela retornará um resultado. C. A função SUM do DAX soma todos os números na coluna Sales[SalesAmount]. Você aprenderá mais sobre as funções mais tarde. D. Os parênteses () envolvem uma expressão que contém um ou mais argumentos. Todas as funções exigem pelo menos um argumento. Um argumento transmite um valor para uma função. E. A tabela referenciada Sales. 5 F. A coluna referenciada [SalesAmount] na tabela Sales. Com este argumento, a função SUM sabe em que coluna deve agregar uma SUM. Ao tentar entender uma fórmula DAX, geralmente é útil decompor os elementos em uma linguagem que você usa e fala todos os dias. Por exemplo, você pode ler esta fórmula como: Para a medida chamada Total Sales, calcule (=) a SUM dos valores na coluna [SalesAmount], na tabela Sales. Quando adicionada a um relatório, essa medida calcula e retorna valores somando as quantidades de vendas para cada um dos outros campos que são incluídos, por exemplo, “Cell Phones in the USA”. Você deve estar pensando: “Por acaso essa medida não faz a mesma coisa que adicionar o campo SalesAmount ao meu relatório?” Bem, sim. Porém, há um bom motivo para criar nossa própria medida que soma os valores do campo SalesAmount: podemos usar isso como um argumento em outras fórmulas. Isso pode parecer um pouco confuso agora, mas à medida que suas habilidades com fórmulas DAX aumentarem, saber disso tornará suas fórmulas e seu modelo mais eficientes. Na verdade, você verá mais tarde a medida Total Sales aparecendo como um argumento em outras fórmulas. Vamos dar uma olhada em mais alguns pontos sobre essa fórmula. Em especial, vale lembrar que introduzimos uma função, SUM. Funções são fórmulas gravadas previamente, que tornam mais fácil fazer cálculos complexos e manipulações com números, datas, hora, texto e muito mais. Você aprenderá mais sobre as funções mais tarde. Você também pode ver que a coluna [SalesAmount] era precedida pela tabela Sales, à qual a coluna pertence. Isso é conhecido como um nome de coluna totalmente qualificado, que inclui o nome da coluna precedido pelo nomeda tabela. Colunas referenciadas na mesma tabela não exigem que o nome da tabela seja incluído na fórmula. Isso pode tornar fórmulas longas, que fazem referência a várias colunas, mais curtas e fáceis de ler. No entanto, é uma prática recomendada incluir o nome da tabela em suas fórmulas de medida, mesmo quando se tratar da mesma tabela. Observação Se um nome de tabela contiver espaços, palavras-chave reservadas ou caracteres não permitidos, coloque o nome da tabela entre aspas simples. Além disso, você precisará colocar os nomes de tabela entre aspas se o nome contiver quaisquer caracteres fora do conjunto de caracteres alfanuméricos ANSI, independentemente de sua localidade dar ou não suporte ao conjunto de caracteres. É importante que suas fórmulas tenham a sintaxe correta. Na maioria dos casos, se a sintaxe não estiver correta, um erro de sintaxe será retornado. Em outros casos, a sintaxe pode estar correta, mas os valores retornados podem não ser o que você esperava. O editor do DAX no Power BI Desktop inclui um recurso de sugestões, usado para criar fórmulas sintaticamente corretas, ajudando você a selecionar os elementos corretos. Vamos criar uma fórmula simples. Essa tarefa ajudará você a entender melhor a sintaxe da fórmula e como o recurso de sugestões na barra de fórmulas pode ajudá-lo. https://msdn.microsoft.com/library/ee634387.aspx 6 Tarefa: criar uma fórmula de medida Para concluir esta tarefa, você precisará abrir o arquivo Exemplo de Vendas da Contoso para o Power BI Desktop. Na visualização de Relatório, na lista Campos, clique com o botão direito do mouse na tabela Sales e clique em Nova Medida. Na barra de fórmulas, substitua Measure digitando um novo nome de medida, Previous Quarter Sales. Após o sinal de igual, digite as primeiras letras CAL e, em seguida, clique duas vezes na função que você deseja usar. Nesta fórmula, você deseja usar a função CALCULATE. Você usará a função CALCULATE para filtrar os valores que desejamos somar por um argumento que transmitimos à função CALCULATE. É isso que chamamos de aninhar funções. A função CALCULATE tem pelo menos dois argumentos. O primeiro é a expressão a ser avaliada e o segundo é um filtro. Após o parêntese de abertura ( para a função CALCULATE, digite SUM seguido por outro parêntese de abertura (. Agora, precisamos passar um argumento para a função SUM. Comece a digitar Sal e selecione Sales [SalesAmount], seguido por um parêntese de fechamento ). Esse é o primeiro argumento de expressão para a função CALCULATE. Digite uma vírgula (,) seguida por um espaço para especificar o primeiro filtro e, em seguida, digite PREVIOUSQUARTER. Esse será nosso filtro. Você usará a função de time intelligence PREVIOUSQUARTER para filtrar os resultados SUM pelo trimestre anterior. Depois da abertura de parênteses (, para a função PREVIOUSQUARTER, digite Calendar[DateKey]. A função PREVIOUSQUARTER tem um argumento, uma coluna contendo um intervalo contíguo de datas. Em nosso caso, essa é a coluna DateKey na tabela de Calendário. Verifique se ambos os argumentos passados para as funções PREVIOUSQUARTER e CALCULATE estão fechados digitando dois parênteses de fechamento )). Sua fórmula agora deve ter essa aparência: Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey])) Clique na marca de seleção na barra de fórmulas ou pressione Enter para validar a fórmula e adicioná-la ao modelo. Você conseguiu! Você acabou de criar uma medida usando DAX, e não estamos falando de uma medida fácil. O que essa fórmula fará é calcular o total de vendas do trimestre anterior, dependendo dos filtros aplicados em um relatório. Por exemplo, se colocamos SalesAmount e nossa nova medida Previous Quarter Sales em um gráfico e adicionamos Year e QuarterOfYear como Segmentações de Dados, obteremos algo semelhante ao exemplo abaixo: 7 Você acabou de conhecer vários aspectos importantes das fórmulas DAX. Em primeiro lugar, esta fórmula incluiu duas funções. É importante observar que PREVIOUSQUARTER, uma função de inteligência de dados temporais, está aninhada como um argumento transmitido para CALCULATE, uma função de filtro.Fórmulas DAX podem conter até 64 funções aninhadas. É improvável que uma fórmula chegue a conter tantas funções aninhadas. Na verdade, uma fórmula como essa seria muito difícil de criar e depurar; além disso, ela provavelmente não seria muito rápida. Nesta fórmula, você também usou filtros. Filtros restringem o que será calculado. Nesse caso, você selecionou um filtro como um argumento, que é, na verdade, o resultado de outra função. Você aprenderá mais sobre filtros posteriormente. Por fim, você usou a função CALCULATE. Essa é uma das funções mais poderosas em DAX. Conforme você criar modelos e fórmulas mais complexas, provavelmente utilizará essa função muitas vezes. Discutir a função CALCULATE está fora do escopo deste artigo, mas fique atento a ela conforme seu conhecimento sobre o DAX aumentar. Funções Funções são fórmulas predefinidas que realizam cálculos usando valores específicos, chamados argumentos, em uma determinada ordem ou estrutura. Argumentos podem ser outras funções, outra fórmula, expressão, referências de coluna, números, texto, valores lógicos como TRUE ou FALSE, ou constantes. O DAX inclui as seguintes categorias de funções: Data e Hora, Inteligência de Dados Temporais, Informações, Lógica, Matemática, Estatística, Texto, Pai/Filho e Outras. Se já estiver familiarizado com as funções em fórmulas do Excel, muitas das funções no DAX podem parecer semelhantes para você. No entanto, as funções DAX são exclusivas nos seguintes aspectos: Uma função DAX sempre referencia uma coluna ou uma tabela completa. Se desejar usar apenas valores específicos de uma tabela ou coluna, é possível adicionar filtros à fórmula. https://msdn.microsoft.com/library/ee634385.aspx https://msdn.microsoft.com/library/ee634825.aspx https://msdn.microsoft.com/library/ee634786.aspx https://msdn.microsoft.com/library/ee634763.aspx https://msdn.microsoft.com/library/ee634763.aspx https://msdn.microsoft.com/library/ee634552.aspx https://msdn.microsoft.com/library/ee634365.aspx https://msdn.microsoft.com/library/ee634241.aspx https://msdn.microsoft.com/library/ee634822.aspx https://msdn.microsoft.com/library/ee634938.aspx https://msdn.microsoft.com/library/mt150102.aspx https://msdn.microsoft.com/library/mt150101.aspx 8 Se precisar personalizar cálculos linha por linha, o DAX fornece funções que permitem usar o valor da linha atual ou um valor relacionado como um tipo de argumento, para realizar cálculos que variam de acordo com o contexto. Você aprenderá mais sobre contexto posteriormente. O DAX inclui várias funções que retornam uma tabela em vez de um valor. A tabela não é exibida, mas é usada para fornecer informações de entrada a outras funções. Por exemplo, é possível recuperar uma tabela e, em seguida, contar os valores distintos contidos nela, ou calcular somas dinâmicas em diferentes colunas ou tabelas filtradas. O DAX inclui uma variedade de funções de inteligência de dados temporais. Estas funções permitem definir ou selecionar intervalos de datas e executar cálculos dinâmicos, baseados nesses intervalos.Por exemplo, é possível comparar somas em períodos paralelos. O Excel tem uma função muito popular, VLOOKUP. As funções DAX não usam uma célula ou intervalo de células como referência, como a VLOOKUP faz no Excel. As funções DAX usam uma coluna ou tabela como referência. Lembre-se: no Power BI Desktop, você está trabalhando com um modelo de dados relacionais. Procurar por valores em outra tabela é realmente muito fácil e, na maioria dos casos, você não precisa criar nenhuma fórmula. Como você pode ver, as funções no DAX podem ajudá-lo a criar fórmulas muito poderosas. Nós abordamos apenas as noções básicas das funções. Na medida em que suas habilidadescom DAX aumentarem, você criará fórmulas usando muitas funções diferentes. Um dos melhores lugares para obter detalhes sobre cada uma das funções DAX é a Referência de funções DAX. Contexto Contexto é um dos conceitos do DAX mais importantes para se compreender. Há dois tipos de contexto em DAX: o contexto de linha e o contexto de filtro. Primeiro, vamos dar uma olhada no contexto de linha. Contexto de linha É mais fácil pensar no contexto de linha como a linha atual. Ele se aplica sempre que uma fórmula tem uma função que utiliza filtros para identificar uma única linha em uma tabela. A função aplicará inerentemente um contexto de linha a cada linha da tabela que essa função está filtrando. Esse tipo de contexto de linha geralmente se aplica a medidas. Contexto de filtro O contexto do filtro é um pouco mais difícil de entender do que o contexto de linha. É mais fácil pensar no contexto de filtro como um ou mais filtros aplicados em um cálculo que determina um resultado ou valor. O contexto de filtro não existe no lugar do contexto de linha; em vez disso, eles são aplicados em conjunto. Por exemplo, para restringir ainda mais os valores a serem incluídos em um cálculo, você pode aplicar um contexto de filtro que não só especifica o contexto de linha, mas também especifica apenas um valor específico (filtro) nesse contexto de linha. https://msdn.microsoft.com/query-bi/dax/data-analysis-expressions-dax-reference 9 O contexto de filtro é visto facilmente em seus relatórios. Por exemplo, ao adicionar TotalCost a uma visualização e, em seguida, Year e Region, você está definindo um contexto de filtro que seleciona um subconjunto de dados com base em um determinado ano e região. Por que o contexto de filtro é tão importante no DAX? Visto que, embora o contexto de filtro possa ser aplicado mais facilmente pela adição de campos a uma visualização, ele também pode ser aplicado em uma fórmula DAX pela definição de um filtro com funções como ALL, RELATED, FILTER, CALCULATE, por relações e por outras medidas e colunas. Por exemplo, vamos dar uma olhada na seguinte fórmula em uma medida chamada Store Sales: Para entender melhor essa fórmula podemos decompô-la, de modo muito similar ao que ocorre em outras fórmulas. Esta fórmula inclui os seguintes elementos de sintaxe: A. O nome da medida Store Sales. B. O operador de sinal de igual (=) indica o início da fórmula. C. A função CALCULATE avalia uma expressão, como um argumento, em um contexto que é modificado pelos filtros especificados. D. Os parênteses () envolvem uma expressão que contém um ou mais argumentos. E. Uma medida [Total Sales] na mesma tabela como uma expressão. A medida Total Sales tem a fórmula: =SUM(Sales[SalesAmount]). F. Uma vírgula (,) separa o primeiro argumento da expressão do argumento do filtro. G. A coluna referenciada totalmente qualificada, Channel[ChannelName]. Esse é o nosso contexto de linha. Cada linha nesta coluna especifica um canal: Store, Online, etc. H. O valor específico, Store, como um filtro. Esse é o nosso contexto de filtro. Esta fórmula garante que somente valores de vendas definidos pela medida Total Sales sejam calculados, apenas para linhas na coluna Channel[ChannelName] e usando o valor "Store" como um filtro. Como você pode imaginar, a capacidade de definir o contexto de filtro em uma fórmula apresenta funcionalidades incríveis e poderosas. Ser capaz de fazer referência a um determinado valor em uma tabela relacionada é apenas um exemplo. Não se preocupe se você não entender totalmente o contexto, logo de imediato. Ao criar suas próprias fórmulas, você entenderá melhor o contexto e a razão pela qual ele é tão importante no DAX. Resumo 10 Agora que você tem uma noção básica dos conceitos mais importantes do DAX, você pode começar a criar fórmulas DAX para medidas por conta própria. DAX pode ser realmente um pouco difícil de aprender, mas há muitas fontes de aprendizado disponíveis para você. Depois de ler este artigo e experimentar algumas das suas próprias fórmulas, você pode aprender mais sobre outros conceitos e fórmulas de DAX que podem ajudá-lo a resolver seus próprios problemas empresariais. Há muitos recursos do DAX disponíveis para você: o mais importante é a Referência ao DAX (Expressões de Análise de Dados). DAX já existe há anos em outras ferramentas de BI da Microsoft, como modelos de tabela do Analysis Services e Power Pivot, portanto, há muitas informações disponíveis. Você encontrará mais informações em white papers, livros e blogs tanto da Microsoft quanto de profissionais de BI de vanguarda. O Wiki do Centro de Recursos do DAX no TechNet também é um ótimo lugar para começar. https://msdn.microsoft.com/library/gg413422.aspx http://social.technet.microsoft.com/wiki/contents/articles/dax-resource-center.aspx 11 Modos de conexão com Power BI Para iniciarmos o trabalho e aprendizagem em DAX usando o Power Pivot, vamos usar o DW de uma empresa chamada Adventure Works. As camadas que vamos trabalhar se chama camada semântica, aonde vamos criar todas as regras de negócio. Antes de entendermos os modos de conexão com Power BI vamos verificar primeiro qual a importância da modelagem de dados que iremos fazer no DW da Adventure Works. Link de download da DW aqui É importante que você configure o servidor SQL para fazer suas análises e use SQLManagementStudio para recuperar a DataBase da Adventure Works no seu servidor. Vou deixar o link desses instaladores para você abaixo: Link dos instaladores Lembrando que para quem possui o curso completo do Power BI na prática, no Módulo III do curso ensinamos a fazer tanto a instalação do servidor quanto a recuperação da data-base para uso no BI. Importância da Modelagem de Dados: - Chamada de camada semântica, ela é a representação dos dados de uma empresa através de termos de negócio conhecidos. - É nessa camada que criamos os relacionamentos, medidas, adicionamos novas colunas e/ou tabelas auxiliares, etc. https://1drv.ms/u/s!Alb2xvFWB7sNhsZnAvxq6RMGGoU7TQ?e=nmDfgR https://1drv.ms/u/s!Alb2xvFWB7sNhsZkbqs1QlE_j3gSzw?e=knlvws 12 Principais funções DAX Funções de Tabela Geralmente são aplicadas como filtros e/ou como tabelas virtuais no meio de fórmulas. Dificilmente são utilizadas para se gerar novas tabelas físicas em modelos bem estruturados, a não ser para testes e para estudos de como funcionam. - COUNTROWS - FILTER - ALL, ALLSELECTED, ALLEXCEPT - VALUES - DISTINCT - CROSSJOIN - GENERATE - SUMMARIZE - ADDCOLUMNS - SUMMARIZECOLUMNS Vamos criar algumas tabelas calculadas para verificar os valores de retorno para as formulas listadas. No menu “modelagem”, Clique na opção nova tabela para seguir os passos abaixo. Quando a barra de fórmulas for habilitada vamos testar algumas das principais funções DAX para estudarmos o que cada uma dessas funções retorna para nós. Lembrando mais uma vez, dificilmente iremos usar essas formulas para retornar tabelas, exceto para fins de estudo. 13 A função ALL aceita mais de um argumento, podendo trazer quantas colunas quisermos de uma tabela. 14 A função ALLEXCEP faz o contrário, exclui a coluna informada na expressão mantendo todas as outras informações. Outra forma de se obter valores distintos é usando a função VALUES. A função VALUES retorna valores similares a função ALL. A diferença só será perceptível quando usarmos essas funções entre nossas formulas e equações DAX. Quando usado em uma outra expressão a função ALL remove todo o contexto de filtro, já a função VALUES respeita o contexto de filtro atual. 15 Obs: Quando falo sobre contexto de filtro, me refiro a avaliação que está sendo aplicada no visual, com os filtros de segmentação ativos ou não. A função CROSSJOIN cria um plano cartesiano entre duas tabelas,aqui vem um exemplo de como usar as funções agrupadas. Para entregar duas tabelas para a função CROSSJOIN vamos usar a função VALUES para criar essa tabela virtual a partir de duas colunas. Poderíamos ter usado a função ALL para entregar a mesma tabela, porém sem respeitar o contexto de filtro entre as cores de produto. A próxima função é a função FILTER, que além de retornar uma tabela, permite que filtremos o resultado desse valor. 16 Perceba que se usarmos a função ALL junto com a FILTER, iremos obter apenas o valor distinto da cor. Essa combinação de FILTER + ALL é bem poderosa para algumas analises avançadas. FILTER & IF Para verificarmos a diferença entre o uso das duas funções DAX, vamos criar uma medida para calcularmos as vendas de produtos que tem o valor unitário acima de R$1.000,00 Para criar essa formula precisamos buscar o valor unitário da dimensão do produto usando a função RELATED. Obs: A função RELATED é usada quando queremos avalia uma coluna de uma outra consulta dentro do contexto de uma consulta atual. A função condicional IF cria uma análise logica no primeiro argumento e executa uma expressão caso esse argumento seja verdadeiro no segundo argumento. No caso acima estamos perguntando se o valor unitário do produto é maior do que mil reais, caso verdadeiro, o valor de venda será calculado. O argumento BLANK() especifica que caso o teste logico seja falso, a expressão não retorna nenhum valor. 17 O problema de usar a função IF dentro de um iterador como o SUMX e que ela faz com que a medida seja menos performática, pois o motor mais lento do BI é usado para fazer a leitura desse cálculo. Vamos verificar como podemos executar a mesma analise usando a função FILTER. Para executar a analise usando a função FILTER criamos um filtro de tabela antes de executar a expressão da SUMX, como no exemplo abaixo: Estamos entregando pro iterador uma tabela reduzida para fazer o calculo do total de vendas, isso facilita o processamento do cálculo me dando performasse para o relatório. Se você quiser, pode jogar toda a função FILTER em uma nova tabela calculada para analisar essa tabela resultante. Na função FILTER eu posso usar mais de uma condição de filtro usando o operador && para encadear outra condição. Nessa analise a medida me fornecerá apenas os produtos com preços acima de mil reais e que são da cor vermelha. Podemos criar outros argumentos usando o operador “pipe pipe” = ||, que cria a pergunta “ou”. 18 A operação está dentro de parênteses para que a medida verifique se existe uma cor ou outra antes de agrupar com os valores acima de mil reais. A mesma operação pode ser criada usando o operador “IN” que reduz a quantidade de linhas escritas no código. Esse operador retorna exatamente os mesmos valores do operador ||. A vantagem é poder listar quantos valores quisermos, separando por ponto e virgula dentro das chaves do operador. Introdução a CALCULATE - A função CALCULATE é a principal função DAX. - Única função capaz de modificar o contexto de avaliação Sintaxe: - Os filtros do segundo argumento em diante retornam ou filtram tabelas 19 CALCULATE ( SUM(fVendas[Vendas]), dCalendario[Ano] = 2016 ) A equação acima é traduzida internamente para: CALCULATE ( SUM(fVendas[Vendas]), FILTER( ALL (dCalendario[Ano]), dCalendario[Ano] = 2016 ) ) Na função CALCULATE, Primeiro é feita a avaliação dos filtros internos e depois a avaliação da expressão para os filtros aplicados. Se quisermos aplicar mais de uma condição para os nossos filtros, podemos usar os operadores “e” e “ou” dentro dos filtros. No exemplo da equação abaixo, a filtragem não funcionária pois o sistema está referenciando duas colunas e ela não sabe qual contexto deve substituir 20 Então se quisermos obter um resultado entre duas colunas diferentes, podemos usar uma das opções abaixo, separando a avaliação em dois filtros distintos. 21 O resultado dessas três formulas seria o mesmo. Vamos criar uma medida usando a CALCULATE filtrando apenas as vendas dos produtos de cor vermelha, modificando o contexto da minha avaliação. Nessa função não precisamos mais usar a RELATED, pois a função CALCULATE tem um contexto de filtro e não de linha como a SUMX. Se usarmos uma função FILTER com o ALL aplicado na entrega da tabela, iremos quebrar o contexto de avaliação da expressão, vamos testar isso na fórmula abaixo: 22 Os valores irão se repetir durante toda a matriz, mesmo nos campos que não são da cor vermelha. Se você remover o ALL do filtro e entregar apenas a tabela produto, a avaliação será feita apenas na linha do produto vermelho. Podemos combinar a ALL para criar uma medida que calcule a porcentagem de vendas de cada cor em especifico. Para isso precisamos de uma medida auxiliar que guarde o total fixo de venda das cores, vamos fazer isso nos próximos cálculos: Nessa função, estamos ignorando a filtragem dos valores para as cores do produto. Na próxima iremos fazer a divisão para obter o resultado desejado. 23 Para evitar erros nas nossas divisões, a melhor pratica seria usar a função DIVIDE para efetuar operações de divisão, essa função nós possibilita criar um terceiro argumento para resultados alternativos. O resultado será o mesmo o que melhora com o uso da função DIVIDED e a possibilidade de tratar erros nas divisões operadas com 0 ou números negativos que exibem erros na aplicação da medida nos contextos. Vamos criar uma hierarquia entre a categoria dos produtos e as cores na nossa matriz para verificar o contexto de algumas avaliações. Para criar a hierarquia arraste o campo “categoria de produto” para cima do campo “cor de produto”. 24 Perceba que se usarmos novamente na filtragem a função ALL, os valores irão ignorar tanto a categoria quando a cor do produto, apresentando apena o total em todos os campos. Então podemos dizer que a função ALL quando aplicada em um contexto de filtro, ignora a seleção da filtragem do visual. 25 OBS: A função ALL no exemplo descrito acima é usada para toda dimensão produto na sintaxe: ALL(Produto) – Sem referenciar a coluna. Podemos combinar a filtragem da própria CAULCULATE para alterar junto com a expressão o contexto da filtragem. Dessa forma, podemos informar com o ALL que a função deve ignorar os filtros de todas as cores modificando o contexto para a categoria “Bikes” Com essa medida podemos fazer com que a medida ignore todos os filtros de cor e aplique o total acumulado penas levando em consideração os produtos da categoria “Bikes”. Se aplicarmos essa medida na nossa matriz iremos perceber que o total muda em relação ao contexto. Tente criar uma medida para as outras categorias e observar a diferença dos totais e das aplicações em cada uma das cores. Transição de Contexto Dentro da função CAULCULATE podemos também modificar a transição do contexto da avaliação. Quando aplicada a uma formula cujo contexto é de linha, a função CALCULATE muda para um contexto de filtro equivalente Imagine que estamos no contexto da dimensão produto, e gostaríamos de trazer para cada linha daquele contexto o valor do produto individual. Normalmente a formula ficaria assim: 26 Usando o SUM, teremos o valor total de vendas repetido em cada linha de produto. Usando a CALCULATE conseguimos modificar o contexto da avaliação respeitando o filtro linha a linha da dimensão produto. 27 VALUES, HASONEVALUE e SELECTEDVALUE Essas funções quando usadas sozinhas retornam um escalar de uma coluna ou tabela. Elas podem ser usadas dentro da CALCULATE para avaliar contextos diferentes ou criar condições logicas para executar expressões.Quando usada em uma medida, podemos verificar se o contexto avaliado retorna um valor distinto dentro de uma seleção. Com a formula abaixo, podemos através de um segmentador de dados avaliar o valor selecionado. Quando aplicamos a medida criada em um cartão, podemos verificar que o visual apresenta um erro. Para eliminar o erro, podemos fazer uma checagem utilizando a função HASONEVALUE para verificar se existe ou não uma seleção ativa. Se aplicarmos um segmentador de dados como o da imagem, o valor será apresentado e se não houver seleção o texto aplicado no ELSE será exibido no cartão. Para não usarmos uma função IF, que torna nosso relatório menos performático, podemos usar a função SELECTEDVALUE para puxar o campo da seleção e o resultado alternativo caso essa seleção não exista. 28 Funções de inteligência e tempo São funções relacionadas a agregação ao longo do tempo - Acumulado ao ano - Acumulado ao trimestre - Acumulado ao mês - Mesmo período no último ano - Mesmo período no último trimestre - Mesmo período no último mês Existem diversas funções prontas para facilitar as agregações ao longo do tempo como a TOTALYTD Sintaxe: 29 Todas as funções podem ser usadas utilizando a CALCULATE como no exemplo baixo Sintaxe de mesmo período último ano (muito útil para calcular porcentagem de crescimento ao longo do tempo) É possível também utilizar a função FILTER para acumular ao longo do período de tempo desejado: 30 Também é possível utilizar a função FILTER em conjunto com a ALLSELECTD para considerar apenas o período de tempo do slicer: 31 Vamos criar uma matriz na nossa área de visuais com a hierarquia ano/mês para avaliarmos alguns contextos de data com funções DAX. Nesse ponto é interessante inserir uma nova tabela vazia para comportar as nossas medidas temporais. Criando essa medida podemos verificar o acumulado ano a ano em um gráfico de linhas como o abaixo: 32 Vamos tentar criar um comparativo de vendas mês a mês. Para criar essa medida precisamos calcular inicialmente os valores do mês passado em relação a nossa seleção atual para podermos efetuar a divisão comparativa. Usando o DATEADD escolhemos um campo e um período para retomar na avaliação da medida. Se colocarmos essa medida na nossa matriz, iremos verificar que os valores são referentes sempre ao do mês anterior ao da seleção total de vendas. Para criar a mesma medida retornando os valores do ano passado, podemos usar uma função pronta do DAX chamada SEMEPERIODLASTYEAR. Essa função retorna automaticamente para o contexto os valores do ano passado em relação a seleção atual. Com nossas medidas auxiliares criadas, podemos calcular a diferença ano a ano e mês a mês usando o DIVIDED 33 Para as nossas medidas funcionarem corretamente com analise temporal, é importante que o campo que estamos utilizando para a criação e segmentação das mesmas tenham um relacionamento com a tabela de fato de vendas. Mesmo que o relacionamento esteja inativo as formulas irão funcionar. Opção de "Marcar Tabela de Data" no Power BI Na versão de fevereiro/2018 a opção de marcar um campo como data existe no Power BI. Você pode marcar uma tabela de data de duas formas: pela aba "Modelação", ou clicando com o botão da direita em cima da tabela na aba "CAMPOS". Seguem imagens dos locais: 34 Obs: para ativar o botão da opção 1 você deve antes deixar selecionada a tabela desejada. Após marcar a tabela de data, a coluna escolhida ficará com um ícone novo ao seu lado, indicando que aquela é a coluna principal de data do seu modelo, conforme abaixo: 35 Detalhe: ao fazer isso, você perde a hierarquia que havia nesta coluna e era criada automaticamente pelo Power BI. Você precisa criar sua própria hierarquia utilizando as colunas Ano, Mês e Dia, ou quaisquer outras que desejar. Com isso, não é mais necessário criar um relacionamento fake na coluna Data[Data] para marcar esta tabela como uma tabela de data. 36 Esta operação de marcar uma tabela como data faz com que possamos escrever medidas de Inteligência de Tempo na forma abaixo e visualizar resultados corretos nos níveis de Mês e Ano: Total Vendas YTD = CALCULATE( [Total Vendas]; DATESYTD(Data[Data]) ) Você consegue entender o que o Power BI faz por trás para conseguir mostrar o resultado correto nos níveis de Mês e Ano? Ao marcarmos a tabela Data como nossa tabela de data do Power BI, a fórmula DAX acima é traduzida internamente para: Total Vendas YTD com ALL = CALCULATE( [Total Vendas]; DATESYTD(Data[Data]); ALL(Data) ) É o terceiro argumento, responsável por remover filtros na tabela Data, que faz com que possamos visualizar resultados corretos para quaisquer colunas da tabela de Data. De forma semelhante, quando calculamos o Total Acumulado, utilizamos apenas a coluna Data[Data] no filtro da CALCULATE e obtivemos resultados corretos nos níveis de Mês e Ano: Total Vendas ACC = VAR DataAtual = MAX(Data[Data]) RETURN CALCULATE( [Total Vendas]; FILTER( ALL(Data[Data]); Data[Data] <= DataAtual ) 37 ) Como então é possível estarmos visualizando corretamente o acumulado nos meses e anos se aplicamos a remoção do filtro apenas na coluna Data[Data]? A resposta é a mesma: internamente é feita a inclusão de ALL(Data) na medida para ignorar quaisquer filtros nas demais colunas da tabela de Data: Total Vendas ACC com ALL = VAR DataAtual = MAX(Data[Data]) RETURN CALCULATE( [Total Vendas]; FILTER( ALL(Data[Data]); Data[Data] <= DataAtual ); ALL(Data) ) Esse é um tópico avançado, mas que gera bastante dúvidas em usuários do Power BI que utilizam ou já utilizaram o Analysis Services. Se você sempre utiliza a coluna de data como chave principal, você provavelmente nunca se deparou com esse "detalhe". E não há problema algum você utilizar a data como chave principal na tabela de data. Se essa já é sua metodologia, continue com ela. 38 Ranking com RANKX e TOPN A medida RANKX é muito útil para ranquear uma determinada categoria de acordo com uma expressão. Sintaxe: Já a função TOPN retorna uma tabela com as TOP N linhas de uma tabela avaliada em uma determinada expressão. Sintaxe: 39 Vamos avaliar os clientes que fizeram mais compras pelo site da empresa usando essas funções de ranqueamento. Primeiro vamos criar uma matriz com os nomes dos nossos clientes e aplicar o total de vendas para termos essa avaliação como base. 40 Na função RANKX precisamos entregar a coluna do nome do cliente sem filtro usando a função ALL. Dessa forma conseguimos propagar a avaliação pelo nome de todos os clientes de uma vez e não linha a linha. O único problema dessa avaliação e que o total no final da matriz e ranqueado como 1. Isso acontece porque não existe nenhum contexto de clientes para ser avaliado no total final da expressão total vendas. Para solucionar esse problema basta criar uma condicional validando os valores de nomes antes de ranquear a expressão. Usando a função HASONEVALUE validamos a condicional apenas quando existe algum contexto de clientes para avaliar, excluindo assim o ranqueamento do total. Com essa medida ranqueando todos os nossos clientes podemos criar medidas auxiliares para avaliar totais e margens de venda por top 5 ou qualquer outro valor fixo que desejarmos. Para criar essas medidas utilizamos a função CALCULATE filtrando a seleção em relação aos 5 primeiros clientes. 41 Dessa forma, tanto o total quando a filtragem só acontece para os 5 primeiros clientes ranqueados pela minha medida anterior.Bem-vindo! O que é DAX? Por que DAX é tão importante? Sintaxe Observação Tarefa: criar uma fórmula de medida Funções Contexto Contexto de linha Contexto de filtro Resumo Modos de conexão com Power BI Importância da Modelagem de Dados: Principais funções DAX Funções de Tabela FILTER & IF Introdução a CALCULATE Transição de Contexto VALUES, HASONEVALUE e SELECTEDVALUE Funções de inteligência e tempo Opção de "Marcar Tabela de Data" no Power BI Ranking com RANKX e TOPN