DAX significa Data Aanálise Expressões. DAX é uma linguagem de fórmula e é 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. DAX é a linguagem de fórmula associada ao Modelo de Dados do Excel Power Pivot.

Não é uma linguagem de programação, mas é uma linguagem de fórmula que permite aos usuários definir cálculos personalizados em colunas e campos calculados (também conhecidos como medidas). O DAX ajuda a criar novas informações a partir dos dados que já estão presentes em seu modelo de dados. As fórmulas DAX permitem que você execute modelagem de dados, análise de dados e use os resultados para relatórios e tomada de decisões.

O DAX inclui algumas das funções usadas nas fórmulas do Excel, mas com funcionalidade modificada e funções adicionais projetadas para trabalhar com dados relacionais e realizar agregação dinâmica.

Importância do DAX

A base do DAX é o Modelo de Dados, que é o banco de dados Power Pivot no Excel. O Modelo de Dados consiste em tabelas entre as quais as relações podem ser definidas de forma a combinar os dados de diferentes fontes. As conexões de dados com o Modelo de Dados podem ser atualizadas conforme e quando os dados de origem mudam. O modelo de dados usa o mecanismo de análise em memória Power Pivot xVelocity (VertiPaq) que torna as operações de dados o mais rápidas possível, além de acomodar vários milhares de linhas de dados. Para mais informações sobre o Modelo de Dados, consulte o tutorial - Power Pivot.

O DAX, em conjunto com o Data Model, permite vários recursos avançados no Excel - Power Pivot, Power PivotTables, Power PivotCharts e Power View. Você pode usar o DAX para resolver vários cálculos básicos e problemas de análise de dados.

DAX também é útil no Power BI para criar um novo arquivo do Power BI Desktop e importar alguns dados para ele. Além disso, as fórmulas DAX fornecem recursos como a análise da porcentagem de crescimento nas categorias de produtos e para diferentes intervalos de datas, calculando o crescimento ano a ano em comparação com as tendências do mercado e muitos outros também.

Aprender como criar fórmulas DAX eficazes o ajudará a obter o máximo de seus dados. Quando você obtém as informações de que precisa, pode começar a resolver problemas reais de negócios que afetam seus resultados financeiros. Este é o poder do Power BI e o DAX o ajudará a chegar lá.

Pré-requisitos para este tutorial

Este tutorial é uma extensão do tutorial do Excel Power Pivot, onde você aprendeu sobre o recurso Power Pivot, Modelo de Dados, Relacionamentos, Power PivotTables, Power Pivot Charts, etc. Seria uma boa ideia revisar este tutorial antes de se aprofundar no DAX, pois este tutorial é mais sobre a linguagem DAX em que você escreve fórmulas para a análise de dados no modelo de dados e relata esses resultados.

Este tutorial também apresenta funções DAX que são semelhantes às funções do Excel, mas com algumas variações. Uma comparação de funções do Excel e funções DAX é fornecida para ajudá-lo a distinguir ambos. Da mesma forma, as fórmulas do Excel e as fórmulas DAX são comparadas e as semelhanças e diferenças são discutidas. Uma boa compreensão dessas diferenças ajudaria você a escrever fórmulas DAX eficazes de forma eficiente.

O conhecimento das Funções e Fórmulas do Excel não é necessário para este tutorial, pois DAX é inteiramente para o Modelo de Dados na janela do Power Pivot. Você entrará em uma planilha do Excel apenas para ver as tabelas do Power Pivot, os gráficos do Power Pivot e as visualizações do Power View baseadas no modelo de dados. Porém, se você é um profissional do Excel com bons conhecimentos em Funções e Fórmulas do Excel, é melhor anotar o que foi mencionado na seção anterior e os detalhes fornecidos no decorrer deste tutorial.

Colunas Calculadas

Colunas calculadas são as colunas que você pode adicionar a uma tabela no Modelo de Dados, por meio de uma fórmula DAX. Você já aprendeu sobre eles no tutorial do Excel Power Pivot, mas aprenderá em detalhes no capítulo - Colunas calculadas como DAX trata de colunas calculadas, campos calculados e funções DAX.

Campos / medidas calculados

Você não pode alterar os valores nas tabelas no Modelo de Dados editando. No entanto, você pode adicionar campos calculados a uma tabela que pode ser usada nas Power PivotTables. Os campos calculados são definidos dando um nome e definindo uma fórmula DAX. Para obter detalhes, consulte o capítulo - Campos calculados .

Os campos calculados foram nomeados como medidas nas versões do Excel anteriores ao Excel 2013. Eles foram renomeados de volta para medidas no Excel 2016. Neste tutorial, iremos referenciá-los como campos calculados. Mas, observe que os termos - campos calculados e medidas - são sinônimos e se referem aos mesmos em todos os aspectos.

Você pode editar um campo calculado após ele ser definido e armazenado. Você pode alterar a fórmula DAX usada na definição ou pode renomear o campo calculado. Você aprenderá sobre isso no capítulo - Editando um campo calculado . Você pode excluir um campo calculado. Consulte o capítulo - Excluindo um campo calculado .

Fórmulas DAX

As fórmulas DAX formam o coração da linguagem DAX. Você pode criar campos calculados e colunas calculadas definindo-os com fórmulas DAX. Você pode escrever fórmulas DAX para as operações de análise de dados. As fórmulas DAX não se referem às células individuais ou intervalo de células na tabela, mas referem-se às tabelas e colunas no Modelo de Dados. Uma coluna em uma tabela no Modelo de Dados deve conter o mesmo tipo de dados.

As fórmulas DAX contêm tabelas, colunas, colunas calculadas, campos calculados, operadores DAX e funções DAX. Consulte o capítulo - Fórmulas DAX para aprender em detalhes.

Sintaxe DAX

Como acontece com qualquer linguagem, DAX, a linguagem de fórmula também tem uma sintaxe. Suas fórmulas DAX devem seguir a sintaxe DAX, do contrário, você obterá erros em tempo de design ou em tempo de execução ou receberá resultados incorretos.

Você aprenderá o seguinte no capítulo - Sintaxe DAX -

• Requisitos de nomenclatura DAX para tabelas, colunas
• Operadores DAX
• Valores especiais DAX
• Tipos de dados DAX
• Conversões de tipo de dados implícitas DAX

Operadores DAX

DAX é uma linguagem de fórmulas e, portanto, usa os operadores para definir as fórmulas. DAX tem os seguintes tipos de operadores -

• Operadores aritméticos DAX
• Operadores de comparação DAX
• Operador de concatenação de texto DAX
• Operadores lógicos DAX

A ordem de precedência do operador DAX também é definida e varia da ordem de precedência do operador do Excel. Consulte o capítulo - Operadores DAX .

Parâmetros padrão DAX

A sintaxe da função DAX tem certos requisitos de parâmetros. Isso ocorre porque os argumentos da função DAX podem ser tabelas ou colunas ou campos calculados ou outras funções DAX. Consulte o capítulo - Parâmetros padrão DAX .

Funções DAX

O Excel 2013 possui 246 funções DAX que você pode usar em fórmulas DAX. Você aprenderá sobre essas funções no nível da categoria no capítulo -DAX Functions. No entanto, para obter detalhes sobre a sintaxe de cada função DAX, parâmetros, uso e valores de retorno, você deve consultar nosso tutorial sobre - Funções DAX . Os nomes das seções usados ​​para a descrição de cada função DAX são fornecidos no capítulo - Compreendendo as funções DAX .

Como as funções DAX são necessárias para escrever as fórmulas DAX e os resultados das funções DAX usadas dependem do contexto em que são usadas, talvez seja necessário ir e voltar entre esses dois tutoriais para obter uma compreensão do DAX que usará no Data Modelagem com DAX e Power BI.

Funções especiais DAX

O DAX tem algumas funções que tornam o DAX poderoso. Essas funções DAX vêm nas categorias - funções de inteligência de tempo DAX e funções de filtro DAX e requerem uma menção especial. Você aprenderá sobre as funções de inteligência de tempo DAX no capítulo - Compreendendo a inteligência de tempo DAX . Você aprenderá sobre o uso das funções do filtro DAX no capítulo - Funções do filtro DAX .

Contexto de avaliação DAX

Os resultados de uma fórmula DAX podem variar com base no contexto usado para avaliação. O DAX tem dois tipos de contexto de avaliação - Contexto de linha e Contexto de filtro. Consulte o capítulo - Contexto de avaliação DAX .

Fórmulas DAX

DAX é uma linguagem de fórmula e você deve obter o máximo dela ao escrever as fórmulas DAX. Consulte o capítulo - Fórmulas DAX para aprender sobre a sintaxe da fórmula e como criá-las de maneira fácil e correta.

Os resultados das fórmulas DAX mudam sempre que os dados são atualizados e sempre que as fórmulas DAX são recalculadas. Você tem que entender a diferença entre atualização de dados e recálculo. Consulte o capítulo - Atualizar os resultados das fórmulas DAX .

Os dados do Modelo de Dados são esperados e estão sujeitos a alterações de tempos em tempos. Isso ocorre porque os dados são usados ​​para atividades de análise de dados que requerem dados atualizados em qualquer ponto do tempo. Para entender as diferentes maneiras de atualizar dados, consulte o capítulo - Atualizando Dados no Modelo de Dados .

Você entenderá os diferentes tipos de recálculo de fórmulas DAX no capítulo - Recalcular fórmulas DAX .

Os recálculos da fórmula DAX devem considerar as dependências de dados e seguir uma ordem específica. Caso contrário, você pode obter erros ou resultados incorretos. Consulte o capítulo - Resolução de problemas de recálculo da fórmula DAX para obter detalhes.

Você obterá uma visão geral de alguns dos erros comuns de fórmula DAX e aprenderá como corrigi-los, no capítulo - Erros de fórmula DAX .

Cenários DAX

Se você começar a aprender um novo idioma, a melhor maneira de se familiarizar com o idioma é entendendo onde usar o quê. Da mesma forma, sendo DAX uma linguagem de fórmula destinada à análise de dados, você precisa entender os vários cenários em que ela pode ser usada.

Consulte os capítulos a seguir para obter detalhes sobre isso.

• Cenários DAX
• Cenários - realizando cálculos complexos
• Cenários - Trabalhando com Texto e Datas
• Cenários - valores condicionais e teste de erros
• Cenários - usando inteligência de tempo
• Cenários - Classificação e comparação de valores

UMA calculated columné uma coluna que você adiciona a uma tabela existente no modelo de dados de sua pasta de trabalho por meio de uma fórmula DAX que define os valores da coluna. Em vez de importar os valores da coluna, você cria a coluna calculada.

Você pode usar a coluna calculada em um relatório de Tabela Dinâmica, Gráfico Dinâmico, Tabela Dinâmica do Power, Gráfico Dinâmico do Power ou Power View como qualquer outra coluna da tabela.

Noções básicas sobre colunas calculadas

A fórmula DAX usada para criar uma coluna calculada é como uma fórmula do Excel. No entanto, na fórmula DAX, você não pode criar fórmulas diferentes para linhas diferentes em uma tabela. A fórmula DAX é aplicada automaticamente a toda a coluna.

Por exemplo, você pode criar uma coluna calculada para extrair o ano da coluna existente - Data, com a fórmula DAX -

= YEAR ([Date])

YEAR é uma função DAX e Date é uma coluna existente na tabela. Como visto, o nome da tabela está entre colchetes. Você aprenderá mais sobre isso no capítulo - Sintaxe DAX .

Quando você adiciona uma coluna a uma tabela com esta fórmula DAX, os valores da coluna são calculados assim que você cria a fórmula. Uma nova coluna com o cabeçalho CalculatedColumn1 preenchido com valores de ano será criada.

Os valores da coluna são recalculados conforme necessário, como quando os dados subjacentes são atualizados. Você pode criar colunas calculadas com base em colunas existentes, campos calculados (medidas) e outras colunas calculadas.

Criação de uma coluna calculada

Considere o modelo de dados com os resultados das Olimpíadas, conforme mostrado na captura de tela a seguir.

• Clique na visualização de dados.
• Clique na guia Resultados.

Você verá a tabela de resultados.

Como visto na imagem acima, a coluna mais à direita tem o cabeçalho - Adicionar coluna.

• Clique na guia Design na Faixa de Opções.
• Clique em Adicionar no grupo Colunas.

O ponteiro aparecerá na barra de fórmulas. Isso significa que você está adicionando uma coluna com uma fórmula DAX.

• Digite = ANO ([Data]) na barra de fórmulas.

Como pode ser visto na imagem acima, a coluna mais à direita com o cabeçalho - Adicionar coluna está destacada.

• Pressione Enter.

Demorará um pouco (alguns segundos) para que os cálculos sejam feitos. Por favor, espere.

A nova coluna calculada será inserida à esquerda da coluna Adicionar coluna mais à direita.

Conforme mostrado na captura de tela acima, a coluna calculada recém-inserida é destacada. Os valores em toda a coluna aparecem de acordo com a fórmula DAX usada. O cabeçalho da coluna é CalculatedColumn1.

Renomeando a coluna calculada

Para renomear a coluna calculada com um nome significativo, faça o seguinte -

• Clique duas vezes no cabeçalho da coluna. O nome da coluna será destacado.

• Selecione o nome da coluna.
• Digite Ano (o novo nome).

Como visto na imagem acima, o nome da coluna calculada foi alterado.

Você também pode renomear uma coluna calculada clicando com o botão direito do mouse na coluna e clicando em Renomear na lista suspensa.

Apenas certifique-se de que o novo nome não entre em conflito com um nome existente na tabela.

Verificando o tipo de dados da coluna calculada

Você pode verificar o tipo de dados da coluna calculada da seguinte forma -

• Clique na guia Página inicial na faixa de opções.
• Clique no tipo de dados.

Como você pode ver na imagem acima, a lista suspensa contém os tipos de dados possíveis para as colunas. Neste exemplo, o tipo de dados padrão (Auto), ou seja, o número inteiro é selecionado.

Erros em colunas calculadas

Podem ocorrer erros nas colunas calculadas pelos seguintes motivos -

• Alterando ou excluindo relacionamentos entre as tabelas. Isso ocorre porque as fórmulas que usam colunas nessas tabelas se tornarão inválidas.

• A fórmula contém uma dependência circular ou autorreferenciada.

Problemas de desempenho

Conforme visto anteriormente no exemplo dos resultados das Olimpíadas, a tabela Resultados tem cerca de 35.000 linhas de dados. Portanto, quando você criou uma coluna com uma fórmula DAX, ela calculou todos os mais de 35.000 valores na coluna de uma vez, o que demorou um pouco. O Modelo de Dados e as tabelas devem lidar com milhões de linhas de dados. Portanto, pode afetar o desempenho quando a fórmula DAX tem muitas referências. Você pode evitar os problemas de desempenho fazendo o seguinte -

• Se sua fórmula DAX contiver muitas dependências complexas, crie-a em etapas salvando os resultados em novas colunas calculadas, em vez de criar uma única fórmula grande de uma vez. Isso permite que você valide os resultados e avalie o desempenho.

• As colunas calculadas precisam ser recalculadas quando ocorrem modificações de dados. Você pode definir o modo de recálculo para manual, economizando recálculos frequentes. No entanto, se algum valor na coluna calculada estiver incorreto, a coluna ficará esmaecida, até que você atualize e recalcule os dados.

UMA calculated fieldem uma tabela em um modelo de dados é o campo obtido por uma fórmula DAX. Em versões anteriores do Power Pivot, o campo calculado era denominado como uma medida. No Excel 2013, ele foi renomeado como um campo calculado. No entanto, ele foi renomeado para medir no Excel 2016. Se você consultar qualquer documentação, poderá observar uma combinação desses dois termos. Observe que os termos campo calculado e medida são sinônimos. Neste tutorial, usamos o termo campo calculado.

Compreendendo os campos calculados

Um campo calculado é uma fórmula criada especificamente para uso em uma Tabela Dinâmica (ou Gráfico Dinâmico).

Você pode criar um campo calculado com base nas funções de agregação padrão, como COUNT ou SUM, ou definindo sua própria fórmula DAX.

A seguir está a diferença entre o campo calculado e a coluna calculada -

• Um campo calculado pode ser usado apenas na área VALUES de uma Tabela Dinâmica.

• Uma coluna calculada com os resultados calculados também pode ser usada nas áreas ROWS, COLUMNS e FILTERS.

Salvando Campo Calculado

O campo calculado será salvo com sua tabela de origem no Modelo de Dados. Ele aparece na lista Power PivotTable ou Power PivotChart Fields como um campo na tabela.

Usando Campo Calculado

Para usar um campo calculado, você deve selecioná-lo na lista Campos do Power PivotTable. O campo calculado será adicionado à área VALORES e a fórmula usada para o campo calculado será avaliada. Um resultado é criado para cada combinação de campos de linha e coluna.

Campo Calculado - Um Exemplo

Considere o seguinte modelo de dados para os dados das Olimpíadas -

Como visto na captura de tela acima, a tabela Resultados tem um campo Medalha que contém os valores - Ouro, Prata ou Bronze para cada uma das linhas contendo a combinação Esporte - Evento - País - Data. Suponha que você queira contagem de medalhas para cada país, então você pode criar um campo calculado Contagem de medalhas com a seguinte fórmula DAX -

Medal Count := COUNTA([Medal])

Criando um campo calculado em uma tabela

Para criar o campo calculado Contagem de medalhas na tabela Resultados, faça o seguinte -

• Clique na célula na área de cálculo abaixo da coluna Medalha na tabela Resultados. A célula será destacada.

• Digite Contagem de medalhas: = CONT.valores ([Medalha]) na barra de fórmulas.

Pressione Enter.

Conforme visto na captura de tela acima, o campo calculado aparece na célula selecionada, mostrando o valor como 34.094. Este número é o número total de linhas na tabela Resultados. Portanto, não faz muito sentido à primeira vista. Conforme discutido anteriormente, o uso real de um campo calculado pode ser visto apenas adicionando-o a uma Tabela Dinâmica do Power ou a um Gráfico Dinâmico do Power.

Usando o campo calculado em uma tabela dinâmica Power

Para usar o campo calculado para contar o número de medalhas para cada país, faça o seguinte -

• Clique na Tabela Dinâmica na Faixa de Opções na janela do Power Pivot.
• Clique na Tabela Dinâmica na lista suspensa.

A caixa de diálogo Criar Tabela Dinâmica é exibida.

• Clique na planilha existente.
• Selecione onde você deseja colocar a Tabela Dinâmica.

Uma tabela dinâmica vazia será criada.

• Clique na tabela Resultados na lista Campos da tabela dinâmica.
• Clique nos campos - País e contagem de medalhas.

Como você pode observar, a contagem de medalhas é adicionada à área VALORES e o país é adicionado à área LINHAS. A Tabela Dinâmica é criada com os valores do campo País aparecendo nas linhas. E para cada linha, o valor da contagem de medalhas é calculado e exibido. Assim, o campo calculado avalia a fórmula DAX utilizada e exibe os valores.

• Adicione o campo Esporte da tabela de Resultados à área ROWS.

Como você pode ver na imagem acima, a contagem de medalhas é calculada para cada país - em termos de esporte e um subtotal para o próprio país.

É assim que o DAX complementa os recursos de energia.

Tipos de campos calculados

Existem dois tipos de campos calculados - implícitos e explícitos.

• A implicit calculated field é criado no painel de lista Campos da Tabela Dinâmica do Power.

• A explicit calculated field é criado na tabela da janela do Power Pivot ou a partir da Faixa de Opções do PowerPivot na janela do Excel.

Criação de um campo calculado implícito

Um campo calculado implícito pode ser criado de duas maneiras, ambas no painel Campos da Tabela Dinâmica do Power.

Criação de um campo calculado implícito na lista de campos da tabela dinâmica

Você pode criar o campo Contagem de medalha a partir do campo Medalha na lista Campos da tabela dinâmica da seguinte maneira -

• Desmarque o campo Contagem de medalhas.
• Clique com o botão direito no campo Medalha.
• Clique em Adicionar aos valores na lista suspensa.

Contagem de medalha aparece na área Valores. A coluna Contagem de medalha será adicionada à tabela dinâmica.

Criação de um campo calculado implícito na área VALUES

Você pode criar um campo calculado implícito -% da linha principal na área de Valores para expressar a contagem de medalhas de cada esporte que um país ganhou como uma porcentagem do número total de medalhas ganhas por aquele país.

• Clique na seta para baixo na caixa Contagem de medalhas na área VALORES.
• Clique em Configurações do campo de valor na lista suspensa.

A caixa de diálogo Configurações do campo de valor é exibida.

• Digite% medalhas na caixa Nome personalizado.
• Clique na guia Mostrar valores como.
• Clique na caixa em Mostrar valores como.
• Clique em% do total da linha pai.

• Clique no botão Formato do número.

A caixa de diálogo Formatar células é exibida.

• Clique em Porcentagem.
• Digite 0 em casas decimais.
• Clique OK.
• Clique em OK na caixa de diálogo Configurações do campo de valor.
• Selecione Não mostrar subtotais.

Você criou outro campo calculado implícito% Medalhas e como você pode observar, para cada país, a porcentagem de medalhas em termos de esporte é exibida.

Desvantagens de um campo calculado implícito

Os campos calculados implícitos são fáceis de criar. Na verdade, você os tem criado até mesmo em tabelas dinâmicas e gráficos dinâmicos do Excel. Mas, eles têm as seguintes desvantagens -

• They are volatile. Isso significa que, se você desmarcar o campo usado para o campo calculado, ele será removido. Se você quiser exibi-lo novamente, terá que criá-lo novamente.

• Their scope is limitedà Tabela Dinâmica ou Gráfico Dinâmico em que são criados. Se você criar outra Tabela Dinâmica em outra planilha, será necessário criar o campo calculado novamente.

Por outro lado, os campos calculados explícitos serão salvos com a tabela e estarão disponíveis sempre que você selecionar essa tabela.

Criação de um campo calculado explícito

Você pode criar um campo calculado explícito de duas maneiras -

• Na área de cálculo em uma tabela no modelo de dados. Você já aprendeu isso na seção - Criando um campo calculado em uma tabela.

• Da faixa de opções do PowerPivot na tabela do Excel. Você aprenderá esta maneira de criar um campo calculado explícito na próxima seção.

Criando um campo calculado explícito a partir da faixa de opções do PowerPivot

Para criar um campo calculado explícito a partir da faixa de opções do PowerPivot, faça o seguinte -

• Clique na guia POWERPIVOT na faixa de opções em sua pasta de trabalho.
• Clique nos Campos calculados na área Cálculos.
• Clique em Novo campo calculado na lista suspensa.

A caixa de diálogo Campo calculado é exibida.

• Preencha as informações necessárias conforme mostrado na imagem a seguir.

• Clique no botão Verificar fórmula.
• Clique em OK apenas se não houver erros na fórmula.

Como você pode observar, você pode definir a categoria e o formato do campo calculado nesta caixa de diálogo. Além disso, você pode usar o recurso IntelliSense para entender o uso das funções e usar o recurso AutoCompletar para completar facilmente os nomes das funções, tabelas e colunas. Para obter detalhes sobre o recurso IntelliSense, consulte o capítulo - Fórmulas DAX .

Esta é uma forma recomendada de criar campos calculados explícitos.

Você pode editar um campo calculado para modificá-lo. Mas, antes de editar um campo calculado, você deve saber onde ele está armazenado. Isso significa, em qual tabela o campo calculado é armazenado. Isso vale para campos calculados implícitos e explícitos. Um campo calculado pode ser associado a apenas uma tabela em um Modelo de Dados.

Encontrando Campos Calculados

Para encontrar os campos calculados no Modelo de Dados, faça o seguinte -

• Clique na guia Avançado na faixa de opções na janela do Power Pivot.
• Clique em Mostrar campos calculados implícitos.

• Clique em Exibir Diagrama.

Como você pode ver na captura de tela acima, Mostrar campos calculados implícitos está destacado na faixa de opções. Se não estiver destacado, clique nele novamente.

Você também pode observar que existem 4 caixas de seleção - Colunas, Campos Calculados, Hierarquias e KPIs. Por padrão, todos os 4 são selecionados.

• Desmarque as caixas - Colunas, Hierarquias e KPIs.

Isso deixará apenas a caixa Campos calculados marcada.

Conforme visto na captura de tela acima, apenas a tabela Resultados tem campos exibidos. As outras duas tabelas estão em branco. Isso mostra que apenas a tabela de resultados tem campos calculados. Você também pode observar que os campos calculados implícitos têm um ícone

exibido, enquanto o campo calculado explícito - Contagem de medalhas não tem esse ícone.

Visualizando Campos Calculados na Tabela

Você pode visualizar os campos calculados na Tabela da seguinte forma -

• Clique no campo calculado.
• Clique com o botão direito e selecione Ir para na lista suspensa.

A tabela aparecerá na Visualização de Dados.

Conforme visto na imagem acima, os campos calculados aparecem na área de cálculo da tabela.

Alterando um campo calculado na tabela

Você pode alterar a fórmula usada para um campo calculado na tabela.

• Clique no campo calculado na tabela na visualização de dados do Modelo de Dados.
• Selecione a fórmula na barra de fórmulas - à direita de: =.

A fórmula ficará destacada.

• Digite a nova fórmula.
• Pressione Enter.

Você aprenderá mais sobre as fórmulas DAX nos capítulos subsequentes.

Renomeando um campo calculado no modelo de dados

Você pode alterar o nome de um campo calculado na Tabela de Dados na Visualização de Dados ou Visualização de Diagrama.

Renomeando um Campo Calculado na Visualização de Dados

• Clique no campo calculado na tabela na visualização de dados do Modelo de Dados.
• Selecione o nome do campo calculado na barra de fórmulas - à esquerda de: =.

O nome do campo calculado será destacado.

• Digite o novo nome do campo calculado.
• Pressione Enter.

Você aprenderá mais sobre a sintaxe DAX nos capítulos subsequentes.

Renomeando um Campo Calculado na Visualização do Diagrama

• Clique com o botão direito do mouse no nome do campo calculado na tabela na visualização do diagrama.
• Clique em Renomear na lista suspensa.

O nome entrará no modo de edição. Digite o novo nome do campo calculado.

Visualizando campos calculados na janela do Excel

Você pode visualizar os campos calculados na janela do Excel da seguinte forma -

• Clique na guia POWERPIVOT na faixa de opções.
• Clique em Campos calculados no grupo Cálculos.
• Clique em Gerenciar campos calculados na lista suspensa.

A caixa de diálogo Gerenciar campos calculados é exibida. Os nomes dos campos calculados explícitos no Modelo de Dados aparecem na caixa de diálogo.

Alterando um campo calculado em Gerenciar campos calculados

Você pode alterar um campo calculado na caixa de diálogo Gerenciar campos calculados.

• Clique em Contagem de medalhas.
• Clique no botão Editar.

A caixa de diálogo Campo calculado é exibida.

• Selecione a fórmula à direita de = na caixa de fórmula.

• Digite a nova Fórmula.
• Clique OK.
• Clique em Fechar na caixa de diálogo Gerenciar campos calculados.

Renomeando um Campo Calculado em Gerenciar Campos Calculados

Você pode renomear um campo calculado na caixa de diálogo Gerenciar campos calculados.

• Clique em Contagem de medalhas.
• Clique no botão Editar.

A caixa de diálogo Campo calculado é exibida.

• Selecione o nome na caixa de nome do campo calculado.

• Digite o novo nome do campo calculado.
• Clique OK.
• Clique em Fechar na caixa de diálogo Gerenciar campos calculados.

Movendo um campo calculado no modelo de dados

Você pode mover um campo calculado dentro da área de cálculo da tabela em que foi criado. Mas não pode ser movido para outra mesa.

• Clique com o botão direito no campo calculado.
• Clique em Cortar.
• Mova o ponteiro para um local diferente na área de cálculo da mesma tabela.
• Clique em Colar.

Note - Realmente não importa onde o campo calculado está dentro da área de cálculo da tabela porque as referências de dados na fórmula DAX do campo calculado estão pelos nomes das colunas e são declaradas explicitamente.

Você pode excluir campos calculados explícitos e implícitos. Existem várias maneiras de fazer isso, que você aprenderá neste capítulo.

No entanto, você precisa se lembrar dos seguintes pontos antes de excluir um campo calculado -

• Um campo calculado explícito pode ser usado em mais de uma tabela dinâmica e / ou gráfico dinâmico. Portanto, você precisa se certificar de que a exclusão do campo calculado explícito não afeta nenhum dos relatórios já gerados.

• Um campo calculado explícito pode ser usado nos cálculos de outros campos calculados explícitos. Portanto, você precisa se certificar de que o campo calculado explícito não seja usado em nenhum dos cálculos de outros campos calculados explícitos.

• Um campo calculado implícito é limitado à Tabela Dinâmica ou Gráfico Dinâmico onde é usado. Portanto, antes de excluir um campo calculado implícito, basta certificar-se de que ele pode ser excluído da tabela dinâmica ou gráfico dinâmico correspondente.

• Criar um campo calculado implícito é mais simples do que criar um campo calculado explícito. Portanto, é necessário mais cuidado antes de excluir um campo calculado explícito.

• Você não pode criar um campo calculado explícito, se o nome entrar em conflito com o nome de um campo calculado implícito. Portanto, pode ser necessário excluir esse campo calculado implícito antes de criar o campo calculado explícito.

Exclusão de um campo calculado explícito no modelo de dados

Você pode excluir um campo calculado explícito na visualização de dados ou na visualização do diagrama no Modelo de Dados.

Exclusão de um campo calculado explícito na exibição de dados

• Localize o campo calculado na área de cálculos da Visualização de Dados.
• Clique com o botão direito no campo calculado.
• Clique em Excluir na lista suspensa.

A mensagem aparece para confirmação de exclusão.

Clique em Excluir do modelo. O campo calculado explícito será excluído.

Exclusão de um campo calculado explícito na visualização do diagrama

• Localize o campo calculado na tabela de dados na Exibição de Diagrama.
• Clique com o botão direito no nome do campo calculado.
• Clique em Excluir na lista suspensa.

A mensagem aparece para confirmação de exclusão.

Clique em Excluir do modelo. O campo calculado explícito será excluído e não será visto na lista de campos da tabela de dados.

Excluindo um campo calculado explícito na janela do Excel

Você pode excluir um campo calculado explícito da janela do Excel da seguinte maneira -

• Clique na guia POWERPIVOT na faixa de opções.
• Clique em Campo Calculado no grupo Cálculos.
• Clique em Gerenciar campos calculados na lista suspensa.

A caixa de diálogo Gerenciar campos calculados é exibida.

• Clique no nome do campo calculado explícito.
• Clique no botão Excluir.

A mensagem de confirmação para exclusão é exibida.

• Clique em Sim. A mensagem informativa de que o modelo de dados foi alterado é exibida na parte superior.
• Clique no botão Fechar na caixa de diálogo.

O campo calculado explícito será excluído e não será visto na lista Campos de Tabela Dinâmica / Gráfico Dinâmico na pasta de trabalho.

Exclusão de um campo calculado implícito

Você pode excluir um campo calculado implícito na visualização de dados ou na visualização do diagrama no Modelo de Dados.

Exclusão de um campo calculado implícito na visualização de dados

• Localize o campo calculado na área de cálculos da Visualização de Dados.
• Clique com o botão direito no campo calculado.
• Clique em Excluir na lista suspensa.

A mensagem aparece para confirmação de exclusão.

• Clique em Excluir do modelo. O campo calculado implícito será excluído.

Exclusão de um campo calculado implícito na visualização do diagrama

• Localize o campo calculado na tabela de dados na Exibição de Diagrama.
• Clique com o botão direito no nome do campo calculado.
• Clique em Excluir na lista suspensa.

A mensagem aparece para confirmação de exclusão.

Clique em Excluir do modelo. O campo calculado implícito será excluído e não será visto na lista de campos da tabela de dados.

Conforme discutido anteriormente, DAX é uma linguagem de fórmula composta de operadores, valores, funções e fórmulas. Neste capítulo, você aprenderá sobre a sintaxe DAX.

DAX Syntax pode ser categorizado como -

• Sintaxe para fórmulas DAX
• Requisitos de nomenclatura DAX
• Valores especiais DAX
• Funções DAX
• Operadores DAX
• Tipos de dados DAX

Antes de continuar aprendendo a sintaxe DAX, você precisa entender a diferença entre as fórmulas do Excel e as fórmulas DAX.

Diferenças entre fórmulas do Excel e fórmulas DAX

As fórmulas DAX são semelhantes às fórmulas do Excel e você pode digitá-las na barra de fórmulas. No entanto, existem algumas diferenças vitais entre os dois.

Fórmula Excel	Fórmula DAX
As fórmulas do Excel são digitadas na barra de fórmulas da janela do Excel.	As fórmulas DAX são digitadas na barra de fórmulas da janela do Power Pivot.
Nas fórmulas do Excel, você pode fazer referência a células ou matrizes individuais para dados.	Em fórmulas DAX, você pode fazer referência apenas a tabelas completas ou colunas de dados, ou seja, as referências podem ser apenas a tabelas e campos nas tabelas. No entanto, se for necessário realizar um cálculo apenas em uma parte dos dados da coluna, você poderá fazê-lo com as funções DAX que filtram e fornecem os valores de dados exclusivos necessários para o cálculo.
As fórmulas do Excel oferecem suporte a certos tipos de dados.	O DAX fornece mais tipos de dados do que o Excel. Portanto, as fórmulas DAX também podem usar os tipos de dados adicionais.
O Excel não oferece suporte a nenhuma conversão de dados implícita.	O DAX executa conversões implícitas de tipo de dados durante os cálculos.

DAX é uma linguagem de fórmula composta por funções, operadores e valores que podem ser usados ​​em uma fórmula ou expressão para calcular e retornar um ou mais valores.

Você pode usar DAX operatorspara comparar valores, realizar cálculos aritméticos e concatenar strings. Neste capítulo, você aprenderá sobre os operadores DAX e como usá-los.

Tipos de operadores DAX

DAX oferece suporte aos seguintes tipos de operadores -

• Operadores aritméticos DAX
• Operadores de comparação DAX
• Operador de concatenação de texto DAX
• Operadores lógicos DAX

Ordem de precedência do operador DAX

Você pode ter uma fórmula DAX com muitos operadores DAX combinando vários valores ou expressões. Nesse caso, o resultado final dependerá da ordem em que as operações são realizadas. O DAX fornece a ordem de precedência padrão do operador e também maneiras de substituir a ordem de precedência padrão.

A precedência do operador padrão DAX está listada na tabela a seguir.

Ordem de precedência	Operador (es)	Operação
1	^	Exponenciação
2	-	Placa
3	* e /	Multiplicação e divisão
4	!	NÃO
5	+ e -	Adição e subtração
6	E	Concatenação
7	=, <,>, <=,> = e <>	Igual a, Menor que, Maior que, Menor que ou igual a, Maior que ou igual a e Diferente de

Sintaxe de expressão DAX

Você precisa primeiro entender a sintaxe da expressão DAX e como a avaliação da expressão é feita com os operandos e operadores.

• Todas as expressões sempre começam com um sinal de igual (=). O sinal de igual indica que os caracteres seguintes constituem uma expressão.

• À direita do sinal de igual, você terá os operandos conectados pelos operadores DAX. Por exemplo, = 5 + 4> 5.

  = 5 * 6 - 3.

• As expressões são sempre lidas da esquerda para a direita e os cálculos são feitos nessa sequência, com base na precedência do operador DAX fornecida na seção anterior.

• Se os operadores DAX tiverem valores de precedência iguais, eles serão avaliados da esquerda para a direita. Por exemplo, = 5 * 6/10. Ambos * e / têm a mesma ordem precedente. Portanto, a expressão é avaliada como 30/10 = 3.

• Se os operadores DAX na expressão tiverem valores de precedência diferentes, eles serão avaliados na ordem de precedência da esquerda para a direita.

  • = 5 + 4> 7. A precedência padrão é + primeiro e> próximo. Portanto, a expressão é calculada da esquerda para a direita. - 5 + 4 é calculado primeiro resultando em 9 e então 9> 5 é avaliado resultando em VERDADEIRO.

  • = 5 * 6 - 3. A precedência padrão é * primeiro e - próximo. Portanto, a expressão é calculada da esquerda para a direita. - 5 * 6 é calculado primeiro resultando em 30 e depois 30 - 3 é calculado resultando em 27.

  • = 2 * 5 - 6 * 3. A precedência padrão é * primeiro, * próximo e depois -. Portanto, a expressão é avaliada como 10 - 18 e depois como -8. Observe que não é 10 - 6 resultando em 4 e então 4 * 3 que é 12.

Uso de parênteses para controlar a ordem de cálculo DAX

Você pode alterar a ordem de precedência do operador padrão DAX usando parênteses, agrupando os operandos e os operadores para controlar a sequência de cálculo.

Por exemplo, = 5 * 6 - 3 é avaliado como 27 com a ordem de precedência do operador padrão DAX. Se você usar parênteses para agrupar os operandos e operadores como = 5 * (6 - 3), então 6 - 3 é calculado primeiro, resultando em 3 e, em seguida, 5 * 3 é calculado, resultando em 15.

= 2 * 5 - 6 * 3 avalia para -8 com a ordem de precedência do operador padrão DAX. Se você usar parênteses para agrupar os operandos e operadores como = 2 * (5 - 6) * 3, então 5 - 6 é calculado primeiro resultando em -1 e, em seguida, 2 * (-1) * 3 é calculado, resultando em -6 .

Como você pode ver, com os mesmos operandos e operadores, resultados diferentes são possíveis pela maneira como você os agrupa. Portanto, ao usar os operadores DAX nas fórmulas DAX, você deve prestar atenção em como deve ser a sequência de computação.

Diferenças entre Excel e DAX

Embora o DAX tenha semelhanças com fórmulas do Excel, existem algumas diferenças significativas entre os dois.

• O DAX é mais poderoso do que o Excel por causa de seu mecanismo de computação residente na memória subjacente.

• DAX oferece suporte a mais tipos de dados do que Excel.

• O DAX fornece recursos avançados adicionais de um banco de dados relacional, modelo de dados, incluindo suporte mais rico para tipos de data e hora.

Em alguns casos, os resultados dos cálculos ou o comportamento das funções no DAX podem não ser iguais aos do Excel. Isso se deve às diferenças no seguinte -

• Fundição de tipo de dados
• Tipos de dados

Diferença no tipo de fundição de dados

No DAX, quando você tem uma expressão = operador valor1 valor2, os dois operandos valor1 e valor2 devem ser do mesmo tipo de dados. Se os tipos de dados forem diferentes, o DAX os converterá primeiro em um tipo de dados comum implicitamente. Consulte o capítulo - Sintaxe DAX para obter detalhes.

Por exemplo, você deve comparar dois operandos de diferentes tipos de dados, digamos um número resultante de uma fórmula, como = [Amount] * 0,08 e um inteiro. O primeiro número pode ser um número decimal com muitas casas decimais, enquanto o segundo número é um inteiro. Então o DAX lida com isso da seguinte maneira -

• Primeiro, o DAX converterá ambos os operandos em números reais usando o maior formato numérico que pode armazenar os dois tipos de números.

• Em seguida, o DAX comparará os dois números reais.

Em contraste, o Excel tenta comparar valores de diferentes tipos de dados sem primeiro forçá-los a um tipo de dados comum. Por esse motivo, você pode encontrar resultados diferentes no DAX e no Excel para a mesma expressão de comparação.

Diferença em tipos de dados

A ordem de precedência do operador no DAX e no Excel é a mesma. No entanto, a porcentagem do operador (%) e os intervalos de dados que o Excel suporta não são suportados pelo DAX. Além disso, o DAX suporta tabela como tipo de dados, o que não é o caso no Excel.

Além disso, nas fórmulas do Excel, você pode se referir a uma única célula, uma matriz ou um intervalo de células. Em fórmulas DAX, você não pode se referir a nenhum deles. As referências da fórmula DAX aos dados devem ser por tabelas, colunas, campos calculados e colunas calculadas.

Se você copiar fórmulas do Excel e colá-las no DAX, verifique se a fórmula DAX está correta, pois a sintaxe DAX é diferente da sintaxe da fórmula do Excel. Além disso, mesmo se uma função tiver o mesmo nome em DAX e Excel, seus parâmetros podem ser diferentes e o resultado da função também pode ser diferente.

Você aprenderá mais sobre tudo isso nos capítulos subsequentes.

Excel DAX - Parâmetros padrão

DAX tem standard parameternomes para facilitar o uso e a compreensão das funções DAX. Além disso, você pode usar certos prefixos para os nomes dos parâmetros. Se o prefixo for claro o suficiente, você pode usar o próprio prefixo como o nome do parâmetro.

Nomes de parâmetros padrão

A seguir estão os nomes dos parâmetros padrão DAX -

Sr. Não.	Nome e descrição do parâmetro
1	expression Qualquer expressão DAX que retorna um único valor escalar, onde a expressão deve ser avaliada várias vezes (para cada linha / contexto).
2	value Qualquer expressão DAX que retorna um único valor escalar em que a expressão deve ser avaliada exatamente uma vez antes de todas as outras operações.
3	table Qualquer expressão DAX que retorna uma tabela de dados.
4	tableName O nome de uma tabela existente usando a sintaxe DAX padrão. Não pode ser uma expressão.
5	columnName O nome de uma coluna existente usando a sintaxe DAX padrão, geralmente totalmente qualificada. Não pode ser uma expressão.
6	name Uma constante de string que será usada para fornecer o nome de um novo objeto.
7	order Uma enumeração usada para determinar a ordem de classificação.
8	ties Uma enumeração usada para determinar o tratamento de valores de empate.
9	type Uma enumeração usada para determinar o tipo de dados para PathItem e PathItemReverse.

Prefixando nomes de parâmetros

Você pode qualificar um nome de parâmetro com um prefixo -

• O prefixo deve ser descritivo de como o argumento é usado.

• O prefixo deve ser de forma que a leitura ambígua do parâmetro seja evitada.

Por exemplo,

• Result_ColumnName - Refere-se a uma coluna existente usada para obter os valores de resultado na função DAX LOOKUPVALUE ().

• Search_ColumnName - Refere-se a uma coluna existente usada para pesquisar um valor na função DAX LOOKUPVALUE ().

Usando apenas o prefixo como parâmetro

Você pode omitir o nome do parâmetro e usar apenas o prefixo, se o prefixo for claro o suficiente para descrever o parâmetro. Omitir o nome do parâmetro e usar apenas o prefixo às vezes pode ajudar a evitar a confusão na leitura.

Por exemplo, Considere DATE (Year_value, Month_value, Day_value). Você pode omitir o nome do parâmetro - valor, que é repetido três vezes e escrevê-lo como DATA (ano, mês, dia). Como você pode observar, usando apenas os prefixos, a função fica mais legível.

No entanto, às vezes o nome do parâmetro e o prefixo precisam estar presentes para maior clareza.

Por exemplo, Considere Year_columnName. O nome do parâmetro é ColumnName e o prefixo é Year. Ambos são necessários para fazer o usuário entender que o parâmetro requer uma referência à coluna de anos existente.

Excel DAX - Funções

A maioria dos DAX functionstêm os mesmos nomes e funcionalidades das funções do Excel. No entanto, as funções DAX foram modificadas para usar tipos de dados DAX e trabalhar com tabelas e colunas.

O DAX tem algumas funções adicionais que você não encontrará no Excel. Essas funções DAX são fornecidas para fins específicos, como pesquisas baseadas em relacionamentos associados aos aspectos do banco de dados relacional do Modelo de Dados, a capacidade de iterar em uma tabela para realizar cálculos recursivos, para realizar agregação dinâmica e para cálculos que utilizam inteligência de tempo.

Neste capítulo, você aprenderá sobre as funções suportadas na linguagem DAX. Para obter mais informações sobre o uso dessas funções DAX, consulte o tutorial - Funções DAX nesta biblioteca de tutoriais.

O que é uma função DAX?

Uma função DAX é uma função incorporada fornecida na linguagem DAX para permitir que você execute várias ações nos dados nas tabelas em seu Modelo de Dados. Conforme discutido anteriormente, o DAX é usado para fins de análise de dados e inteligência de negócios que requerem suporte para extrair, assimilar e derivar percepções dos dados. As funções DAX baseadas no modelo de dados fornecem esses utilitários que tornam seu trabalho mais simples, uma vez que você domina a linguagem DAX e o uso das funções DAX.

Funções do Excel vs. funções DAX

Existem certas semelhanças entre as funções do Excel que você conhece e as funções DAX. No entanto, também existem algumas diferenças. Você precisa obter uma clareza sobre eles, para evitar cometer erros no uso de funções DAX e ao escrever fórmulas DAX que incluem funções DAX.

Semelhanças entre funções do Excel e funções DAX

• Muitas funções DAX têm o mesmo nome e o mesmo comportamento geral das funções do Excel.

• O DAX tem funções de pesquisa semelhantes às funções de pesquisa de vetor e matriz do Excel.

Diferenças entre funções do Excel e funções DAX

• As funções DAX foram modificadas para receber diferentes tipos de entradas e algumas das funções DAX podem retornar um tipo de dados diferente. Portanto, você precisa entender o uso dessas funções separadamente, embora tenham o mesmo nome. Neste tutorial, você encontrará todas as funções DAX prefixadas com DAX para evitar confusão com as funções do Excel.

• Você não pode usar funções DAX em uma fórmula do Excel ou usar fórmulas / funções do Excel em DAX, sem as modificações necessárias.

• As funções do Excel usam uma referência de célula ou um intervalo de células como referência. As funções DAX nunca tomam uma referência de célula ou um intervalo de células como referência, mas, em vez disso, tomam uma coluna ou tabela como referência.

• As funções de data e hora do Excel retornam um número inteiro que representa uma data como um número de série. As funções de data e hora DAX retornam um tipo de dados datetime que está no DAX, mas não no Excel.

• O Excel não tem funções que retornam uma tabela, mas algumas funções podem funcionar com matrizes. Muitas das funções DAX podem facilmente fazer referência a tabelas e colunas completas para realizar cálculos e retornar uma tabela ou coluna de valores. Essa capacidade de DAX adiciona energia ao Power Pivot, Power View e Power BI, onde DAX é usado.

• As funções de pesquisa DAX exigem que um relacionamento seja estabelecido entre as tabelas.

• O Excel oferece suporte a tipos de dados variantes em uma coluna de dados, ou seja, você pode ter dados de diferentes tipos de dados em uma coluna. Enquanto o DAX espera que os dados em uma coluna de uma tabela sejam sempre do mesmo tipo de dados. Se os dados não forem do mesmo tipo, DAX altera a coluna inteira para o tipo de dados que melhor acomoda todos os valores na coluna. No entanto, se os dados forem importados e esse problema surgir, o DAX pode sinalizar um erro.

Para saber mais sobre os tipos de dados DAX e conversão de tipo de dados, consulte o capítulo - Referência de sintaxe DAX.

Tipos de funções DAX

DAX oferece suporte aos seguintes tipos de funções.

• Funções com valor de tabela DAX
  • Funções de filtro DAX
  • Funções de agregação DAX
  • Funções DAX Time Intelligence
• Funções de data e hora DAX
• Funções de informação DAX
• Funções lógicas DAX
• Funções DAX Math e Trig
• Outras funções DAX
• Funções DAX pai e filho
• Funções estatísticas DAX
• Funções de texto DAX

Nesta seção, você aprenderá sobre as funções DAX no nível da categoria de funções. Para obter detalhes sobre a sintaxe da função DAX e o que a função DAX retorna e faz - consulte o tutorial Funções DAX nesta biblioteca de tutoriais.

As funções de inteligência de tempo DAX e funções de filtro DAX são poderosas e requerem uma menção especial. Consulte os capítulos - Compreendendo as funções DAX Time Intelligence e DAX Filter para obter detalhes.

Funções com valor de tabela DAX

Muitas funções DAX usam tabelas como tabelas de entrada ou saída, ou ambas. Essas funções DAX são chamadas de funções com valor de tabela DAX. Como uma tabela pode ter uma única coluna, as funções com valor de tabela DAX também usam colunas únicas como entradas. Você tem os seguintes tipos de funções com valor de tabela DAX -

• Funções de agregação DAX
• Funções de filtro DAX
• Funções de inteligência de tempo DAX

Compreender as funções com valor de tabela DAX ajuda você a escrever fórmulas DAX de maneira eficaz.

Funções de agregação DAX

As funções de agregação DAX agregam qualquer expressão nas linhas de uma tabela e são úteis em cálculos.

A seguir estão algumas funções de agregação DAX -

A seguir está a lista de funções de agregação DAX.

• ADDCOLUMNS (<tabela>, <nome>, <expressão>, [<nome>, <expressão>] ...)

• AVERAGE (<coluna>)

• AVERAGEA (<coluna>)

• AVERAGEX (<table>, <expression>)

• COUNT (<coluna>)

• CONT.valores (<coluna>)

• CONT.valor (<tabela>, <expressão>)

• CONTAR EM BRANCO (<coluna>)

• CONTAS (<tabela>)

• COUNTX (<table>, <expression>)

• CROSSJOIN (<tabela1>, <tabela2>, [<tabela3>] ...)

• DISTINCTCOUNT (<coluna>)

• GERAR (<tabela1>, <tabela2>)

• GENERATEALL (<tabela1>, <tabela2>)

• MAX (<coluna>)

• MAXA (<coluna>)

• MAXX (<tabela>, <expressão>)

• MIN (<coluna>)

• MINA (<coluna>)

• MINX (<table>, <expression>)

• PRODUTO (<coluna>)

• PRODUCTX (<table>, <expression>)

• ROW (<nome>, <expressão>, [<nome>, <expressão>] ...)

• SELECTCOLUMNS (<table>, <name>, <scalar_expression>,

• [<nome>, <expressão_escalar>] ...)

• SUM (<coluna>)

• RESUMA (<table>, <groupBy_columnName>, [<groupBy_columnName>]…, [<name>, <expression>]…)

• SUMX (<tabela>, <expressão>)

• TOPN (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

Funções de filtro DAX

As funções de filtro DAX retornam uma coluna, uma tabela ou valores relacionados à linha atual. Você pode usar funções de filtro DAX para retornar tipos de dados específicos, pesquisar valores em tabelas relacionadas e filtrar por valores relacionados. As funções DAX Lookup funcionam usando tabelas e relacionamentos entre elas. As funções de filtro DAX permitem que você manipule o contexto de dados para criar cálculos dinâmicos.

A seguir estão algumas funções do filtro DAX -

A seguir está a lista de funções do filtro DAX.

• ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>] ..., <table>, <groupingColumn>, [<groupingColumn>] ... [filterTable] ...)

• TODOS ({<table> | <coluna>, [<coluna>], [<coluna>]…})

• ALLEXCEPT (<table>, <column>, [<column>]…)

• ALLNOBLANKROW(<table>|<column>)

• ALLSELECTED ([<tableName> | <columnName>])

• CALCULAR (<expressão>, <filtro1>, <filtro2> ...)

• CALCULATETABLE (<expression>, <filter1>, <filter2>…)

• CROSSFILTER (<columnName1>, <columnName2>, <direction>)

• DISTINCT (<coluna>)

• ANTES (<coluna>, <número>)

• EARLIEST(<column>)

• FILTER(<table>,<filter>)

• FILTERS(<columnName>)

• HASONEFILTER(<columnName>)

• HASONEVALUE(<columnName>)

• ISCROSSFILTERED (<columnName>)

• ISFILTERED (<columnName>)

• KEEPFILTERS (<expressão>)

• RELATED(<column>)

• RELATEDTABLE(<tableName>)

• SUBSTITUTEWITHINDEX (<table>, <indexColumnName>, <indexColumnsTable>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…])

• USERELATIONSHIP(<columnName1>,<columnName2>)

• VALUES(<TableNameOrColumnName>)

Funções DAX Time Intelligence

As funções DAX Time Intelligence retornam uma tabela de datas ou usam uma tabela de datas para calcular uma agregação. Essas funções DAX ajudam a criar cálculos que suportam as necessidades de análise de Business Intelligence, permitindo que você manipule dados usando períodos de tempo, incluindo dias, meses, trimestres e anos.

A seguir estão algumas funções do DAX Time Intelligence -

A seguir, está a lista de funções DAX Time Intelligence.

• CLOSINGBALANCEMONTH (<expression>, <dates> [, <filter>])

• CLOSINGBALANCEQUARTER (<expressão>, <datas>, [<filtro>])

• CLOSINGBALANCEYEAR (<expression>, <dates>, [<filter>], [<year_end_date>])

• DATEADD (<dates>, <number_of_intervals>, <interval>)

• DATESBETWEEN (<dates>, <start_date>, <end_date>)

• DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>)

• DATESMTD (<dates>)

• DATESQTD (<datas>)

• DATESYTD (<dates>, [<year_end_date>])

• ENDOFMONTH (<datas>)

• ENDOFQUARTER (<datas>)

• ENDOFYEAR (<dates>, [<year_end_date_end>])

• FIRSTDATE (<dates>)

• FIRSTNONBLANK (<coluna>, <expressão>)

• ÚLTIMA DATA (<datas>)

• LASTNONBLANK (<coluna>, <expressão>)

• NEXTDAY (<dates>)

• PRÓXIMO MÊS (<datas>)

• NEXTQUARTER (<datas>)

• NEXTYEAR (<dates>, [<year_end_date>])

• OPENINGBALANCEMONTH (<expression>, <dates>, [<filter>])

• OPENINGBALANCEQUARTER (<expressão>, <datas>, [<filtro>])

• OPENINGBALANCEYEAR (<expression>, <dates>, [<filter>], [<year_end_date>])

• PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>)

• PREVIOUSDAY(<dates>)

• PREVIOUSMONTH(<dates>)

• PREVIOUSQUARTER(<dates>)

• ANTERIOR (<dates>, [<year_end_date_end>])

• SAMEPERIODLASTYEAR(<dates>)

• STARTOFMONTH(<dates>)

• STARTOFQUARTER(<dates>)

• STARTOFYEAR(<dates>)

• TOTALMTD (<expressão>, <datas>, [<filtro>])

• TOTALQTD (<expressão>, <datas>, [<filtro>])

• TOTALYTD (<expressão>, <datas>, [<filtro>], [<data_end_do_ano>])

Funções de data e hora DAX

As funções de data e hora DAX são semelhantes às funções de data e hora do Excel. No entanto, as funções DAX Data e Hora são baseadas no tipo de dados datetime do DAX.

A seguir estão as funções DAX Data e Hora -

A seguir, está a lista de funções de data e hora DAX.

• DATA (<ano>, <mês>, <dia>)
• DATEVALUE(date_text)
• DAY(<date>)
• EDATE (<start_date>, <months>)
• EOMONTH (<start_date>, <months>)
• HOUR(<datetime>)
• MINUTE(<datetime>)
• MONTH(<datetime>)
• NOW()
• SECOND(<time>)
• TIME (hora, minuto, segundo)
• TIMEVALUE(time_text)
• TODAY()
• WEEKDAY (<data>, <return_type>)
• WEEKNUM (<data>, <return_type>)
• YEAR(<date>)
• YEARFRAC (<start_date>, <end_date>, <basis>)

Funções de informação DAX

As funções de informações DAX examinam a célula ou linha fornecida como argumento e informam se o valor corresponde ao tipo esperado.

A seguir estão algumas funções de informações DAX -

A seguir está a lista de funções de informações DAX.

• CONTAINS (<table>, <columnName>, <value>, [<columnName>, <value>]…)

• CUSTOMDATA()

• ISBLANK(<value>)

• ISERROR(<value>)

• ISEVEN(number)

• ISLOGICAL(<value>)

• ISNONTEXT(<value>)

• ISNUMBER(<value>)

• ISONORAFTER (<expressão_escalar>, <expressão_escalar>, [ordem_dentro], [<expressão_escalar>, <expressão_escalar>, [ordem_de_classificação] ...)

• ISTEXT(<value>)

• LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…)

• USERNAME()

Funções lógicas DAX

As funções lógicas DAX retornam informações sobre os valores em uma expressão. Por exemplo, a função DAX TRUE permite saber se uma expressão que você está avaliando retorna um valor TRUE.

A seguir estão as funções lógicas DAX -

A seguir está a lista de funções lógicas DAX.

• AND(<logical1>,<logical2>)
• FALSE()
• IF (teste_lógico>, <valor_se_verdadeiro>, valor_se_falso)
• IFERROR (valor, valor_se_erro)
• NOT(<logical>)
• OR(<logical1>,<logical2>)
• SWITCH (<expressão>, <valor>, <resultado>, [<valor>, <resultado>]…, [<else>])
• TRUE()

Funções DAX Math e Trig

As funções matemáticas e trigonométricas do DAX são muito semelhantes às funções matemáticas e trigonométricas do Excel.

A seguir estão algumas funções DAX Math e Trig -

A seguir está a lista de funções DAX Math e Trig.

• ABS(<number>)
• ACOS(number)
• ACOSH(number)
• ASIN(number)
• ASINH(number)
• ATAN(number)
• ATANH(number)
• TETO (<número>, <significância>)
• COMBIN (número, número_escolhido)
• COMBINA (número, número_escolhido)
• COS(number)
• COSH(number)
• CURRENCY(<value>)
• DEGREES(angle)
• DIVIDE (<numerador>, <denominador>, [<alternateresult>])
• EVEN(number)
• EXP(<number>)
• FACT(<number>)
• PISO (<número>, <significância>)
• GCD (número1, [número2], ...)
• INT(<number>)
• ISO.CEILING (<number>, [<significance>])
• LCM (número1, [número2], ...)
• LN(<number>)
• LOG(<number>,<base>)
• LOG10(<number>)
• INT(<number>)
• MROUND (<número>, <múltiplo>)
• ODD(number)
• PI()
• POWER (<número>, <força>)
• PRODUCT(<column>)
• PRODUCTX (<table>, <expression>)
• QUOTIENT (<numerador>, <denominador>)
• RADIANS(angle)
• RAND()
• RANDBETWEEN(<bottom>,<top>)
• ROUND (<number>, <num_digits>)
• ROUNDDOWN (<number>, <num_digits>)
• ROUNDUP (<number>, <num_digits>)
• SIN(number)
• SINH(number)
• SIGN(<number>)
• SQRT(<number>)
• SUM(<column>)
• SUMX (<tabela>, <expressão>)
• TAN(number)
• TANH(number)
• TRUNC(<number>,<num_digits>)

Outras funções DAX

Essas funções DAX executam ações exclusivas que não podem ser definidas por nenhuma das categorias às quais a maioria das outras funções pertence.

A seguir estão algumas outras funções do DAX -

A seguir, está a lista de outras funções DAX.

• EXCEPT (<table_expression1>, <table_expression2>

• GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>]…)

• INTERSECT (<table_expression1>, <table_expression2>)

• ISEMPTY(<table_expression>)

• NATURALINNERJOIN (<leftJoinTable>, <rightJoinTable>)

• NATURALLEFTOUTERJOIN (<leftJoinTable>, <rightJoinTable>)

• SUMMARIZECOLUMNS (<groupBy_columnName>, [<groupBy_columnName>]…, [<filterTable>]…, [<name>, <expression>]…)

• UNION (<table_expression1>, <table_expression2>, [<table_expression>]…)

• VAR <nome> = <expressão>

Funções DAX pai e filho

As funções DAX pai e filho são úteis no gerenciamento de dados que são apresentados como uma hierarquia pai / filho no modelo de dados.

A seguir estão algumas funções pai e filho DAX -

A seguir, está a lista de funções pai e filho DAX.

• PATH (<ID_columnName>, <parent_columnName>)
• PATHCONTAINS (<caminho>, <item>)
• PATHITEM (<caminho>, <posição>, [<tipo>])
• PATHITEMREVERSE (<caminho>, <posição>, [<tipo>])
• PATHLENGTH(<path>)

Funções estatísticas DAX

As funções estatísticas do DAX são muito semelhantes às funções estatísticas do Excel.

A seguir estão algumas funções estatísticas DAX -

A seguir, está a lista de funções estatísticas DAX.

• BETA.DIST (x, alfa, beta, cumulativo, [A], [B])

• BETA.INV (probabilidade, alfa, beta, [A], [B])

• CHISQ.INV (probabilidade, grau_liberdade)

• CHISQ.INV.RT (probabilidade, grau_liberdade)

• CONFIDENCE.NORM (alpha, standard_dev, size)

• CONFIDENCE.T (alpha, standard_dev, size)

• DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2 ..., {{Value1, Value2 ...}, {ValueN, ValueN + 1 ...} ...})

• EXPON.DIST (x, lambda, cumulativo)

• GEOMEAN(<column>)

• GEOMEANX (<table>, <expression>)

• MEDIAN(<column>)

• MEDIANX (<tabela>, <expressão>)

• PERCENTIL.EXC (<coluna>, <k>)

• PERCENTIL.INC (<coluna>, <k>)

• PERCENTILEX.EXC (<tabela>, <expressão>, k)

• PERCENTILEX.EXC (<tabela>, <expressão>, k)

• POISSON.DIST (x, média, cumulativa)

• RANK.EQ (<valor>, <columnName> [, <order>])

• RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

• SAMPLE (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

• STDEV.P(<ColumnName>)

• STDEV.S(<ColumnName>)

• STDEVX.P(<table>, <expression>)

• STDEVX.S(<table>, <expression>)

• SQRTPI(number)

• VAR.P(<columnName>)

• VAR.S(<columnName>)

• VARX.P(<table>, <expression>)

• VARX.S(<table>, <expression>)

• XIRR(<table>, <values>, <dates>, [guess])

• XNPV(<table>, <values>, <dates>, <rate>)

DAX Text Functions

DAX Text functions work with tables and columns. With DAX Text functions you can return the part of a string, search for text within a string or concatenate string values. You can also control the formats for dates, times, and numbers.

Following are some DAX Text functions −

Given below is the list of DAX Text functions.

• BLANK()
• CODE(text)
• CONCATENATE(<text1>, <text2>)
• CONCATENATEX(<table>, <expression>, [delimiter])
• EXACT(<text1>,<text2>)
• FIND(<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
• FIXED(<number>, <decimals>, <no_commas>)
• FORMAT(<value>, <format_string>)
• LEFT(<text>, <num_chars>)
• LEN(<text>)
• LOWER(<text>)
• MID(<text>, <start_num>, <num_chars>)
• REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
• REPT(<text>, <num_times>)
• RIGHT(<text>, <num_chars>)
• SEARCH(<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
• SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)
• TRIM(<text>)
• UPPER (<text>)
• VALUE(<text>)

Excel DAX - Understanding DAX Functions

In Excel 2013, DAX has 246 functions. You have already learnt about the different types of DAX functions in the chapter – DAX Functions. However, if you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc.

If you are writing a DAX formula, it is suggested to use the DAX functions where applicable. For this, you can refer to the tutorial – DAX Functions in this tutorials library for an indepth understanding of where and how to use each of the 246 DAX functions. You can always go back and forth between this tutorial and DAX Functions tutorial to obtain mastery on DAX.

In this chapter, you will understand how to read and interpret the DAX functions in the DAX Functions tutorial.

DAX Function – Explanation Structure

In the DAX functions tutorial, each DAX function is explained in a standard structure, comprising of the following sections −

• Description
• Syntax
• Parameters
• Return Value
• Remarks
• Example

You will learn about each of these in the following sections.

Description

In the Description section, you will learn what the DAX function is about and where it can be used.

Syntax

In the Syntax section, you will learn the exact function name and the respective parameters.

Parameters

In the Parameters section, you will learn about each of the parameters of the specific DAX function, whether a parameter is an input or an output and if there are any options. As seen in the chapter - DAX Standard Parameters, only the standard parameter names will be used.

Return Value

In the Return Value section, you will learn about what value the DAX function will return and its data type.

Remarks

In the Remarks section, you will learn about any extra information that you need to know about the usage of the DAX function.

Example

The DAX function description will be ended with an example of the usage of the function.

Excel DAX - Evaluation Context

In DAX, context is an important term that you should be aware of, while writing DAX formulas. Also referred to as evaluation context, DAX context is used to determine the evaluation of a DAX formula and the corresponding result. This means, the results of a DAX formula can vary according to the context. You should clearly understand how a specific DAX context is used and how the results can be different.

Evaluation context enables you to perform dynamic analysis, in which the results of a DAX formula can change to reflect the current row or a cell selection and also any related data. Understanding context and using context effectively are very important to build powerful DAX formulas, perform dynamic data analysis, and troubleshoot problems in DAX formulas. Evaluation contexts are the basis of all of the advanced features of DAX that you need to master to create complex data analysis reports.

As you keep referencing to DAX functions for relevant usage in DAX formulas, you need to refer to this chapter on DAX context to obtain clarity on the results.

Types of Context in DAX

DAX supports the following evaluation contexts −

• Row Context
• Filter Context

When a DAX formula is evaluated, all the contexts will be taken into account and are applied as relevant. The contexts exist together and the result of the formula will be different based on the context that is used while calculating a value. For example, when you select fields for rows, columns, and filters in a PivotTable, the subtotals are dynamically calculated based on which row and which column the subtotal/total is associated with and the values in the rows and columns are determined by the filters used.

Row Context

Row context means that the DAX formula or the DAX function knows which row of the table it is referencing at any point in time. You can consider row context as the current row. The formula will get calculated row-by-row with the row context.

Some DAX functions (e.g., the X-functions, FILTER ()) and all calculated columns have a row context. For example, if you create a calculated column Year with the DAX formula = YEAR ([Date]), the values of the calculated column are obtained by applying the given DAX formula on the given column in the table, row by row.

This means that if you have created a calculated column, the row context consists of the values in each individual row and the values in the columns that are related to the current row, as determined by the DAX formula used. Though the DAX formula does not contain the reference to a row, DAX implicitly understands the row context while calculating values.

DAX creates a row context automatically when you define a calculated column and all the calculated values with the DAX formula used will appear in the calculated column.

In contrast, when you have a DAX function such as SUMX, the values calculated row by row get summed up and only the final result will be displayed. That is, the intermediate values are discarded.

When you have related tables, the row context determines which rows in the related table are associated with the current row. However, the row context does not propagate through relationships automatically. You have to use the DAX functions - RELATED and RELATEDTABLE for this.

Multiple Row Context

DAX has iterator functions like SUMX. You can use these functions to nest row contexts. With this, programmatically you can have a recursion over an inner loop and an outer loop, where you can have multiple current rows and current row contexts.

For example, you can use the DAX function Earlier () that stores the row context from the operation that preceded the current operation. This function stores two sets of context in memory - one set of context represents the current row for the inner loop of the formula, and another set of context represents the current row for the outer loop of the formula. DAX automatically feeds the values between the two loops so that you can create complex aggregates.

For an example, refer to the scenario - Creating a DAX Formula that Dynamically Ranks Values in the chapter Scenarios - Ranking and Comparing Values.

Filter Context

Filter context refers to any filtering that is applied to the Data Model in DAX. Filter context is created by a PivotTable and also by the DAX functions.

Filter Context Created by a PivotTable

Filter Context created by a PivotTable is the natural filtering that is applied by the selections made on the PivotTable fields from the following −

• Rows
• Columns
• Filters
• Slicers

The filter context created by a PivotTable, filters the underlying tables in the Data Model. If the tables are related, then the filters flow down from the lookup tables to data tables. That means, you can filter the data tables based on the results from the lookup tables. The filter propagation does not happen the other way round. However, you can use DAX formulas to filter the lookup tables based on the results from the data tables.

Filter Context Created by DAX Functions

You can use DAX Filter functions to define calculated fields and calculated columns, containing filter expressions that control the values used by the DAX formula. These calculated fields and calculated columns then become part of the PivotTable fields list and you can add them to the PivotTable. You can also selectively clear the filters on particular columns with these DAX Filter functions. An example of a powerful DAX Filter function to create Filter Context is CALCULATE (). For an example, refer to the chapter Scenarios - Performing Complex Calculations.

Filter Context as an Addition to Row Context

Row context does not automatically create a filter context. You can achieve the same with the DAX formulas containing DAX Filter functions.

Excel DAX - Formulas

DAX is a formula language for creating custom calculations in Power PivotTables. You can use the DAX functions that are designed to work with relational data and perform dynamic aggregation in DAX formulas.

DAX formulas are very similar to Excel formulas. To create a DAX formula, you type an equal sign, followed by a function name or expression and any required values or arguments.

DAX Functions vs. DAX Formulas

DAX formulas can include DAX functions and leverage their usage. This is where DAX formulas tend to differ from DAX functions in important ways.

• A DAX function always reference a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.

• If you want to customize calculations on a row by row basis, Power Pivot provides functions that let you use the current row value or a related value to perform calculations that vary by context.

• DAX includes a type of function that returns a table as its result, rather than a single value. These functions can be used to provide input to other functions, thus calculating values for entire tables or columns.

• Some DAX functions provide time intelligence, which lets you create calculations using meaningful ranges of dates and compare the results across parallel periods.

Understanding DAX Formula Syntax

Every DAX formula has the following syntax −

• Each formula must begin with an equal sign.

• To the right of the equal sign, you can either type or select a function name, or type an expression. The expression can contain table names and column names connected by DAX operators.

Following are some valid DAX formulas −

• [column_Cost] + [column_Tax]
• = Today ()

Understanding IntelliSense Feature

DAX provides the IntelliSense feature that will enable you to write DAX formulas promptly and correctly. With this feature, you need not type the table, column, and function names completely, but select the relevant names from the dropdown list while writing a DAX formula.

• Begin to type the first few letters of the function name. AutoComplete displays a list of available functions with the names beginning with those letters.

• Place the pointer on any of the function names. IntelliSense tooltip will be displayed giving you the use of the function.

• Click the function name. The function name appears in the formula bar and the syntax is displayed, which will guide you as you select the arguments.

• Type the first letter of the table name that you want. AutoComplete displays a list of available tables and columns with the names beginning with that letter.

• Press TAB or click the name to add an item from the AutoComplete list to the formula.

• Click the Fx button to display a list of available functions. To select a function from the dropdown list, use the arrow keys to highlight the item and click OK to add the function to the formula.

• Supply the arguments to the function by selecting them from a dropdown list of possible tables and columns or by typing in required values.

Usage of this handy IntelliSense feature is highly recommended.

Where to Use DAX Formulas?

You can use DAX formulas in creating calculated columns and calculated fields.

• You can use DAX formulas in calculated columns, by adding a column and then typing an expression in the formula bar. You create these formulas in the PowerPivot window.

• You can use DAX formulas in calculated fields. You create these formulas −

  • In the Excel window in the Calculated Field dialog box, or

  • In the Power Pivot window in the calculation area of a table.

The same formula can behave differently depending on whether the formula is used in a calculated column or a calculated field.

• In a calculated column, the formula is always applied to every row in the column, throughout the table. Depending on the row context, the value might change.

• In a calculated field, however, the calculation of results is strongly dependent on the context. That is, the design of the PivotTable and the choice of row and column headings affects the values that are used in calculations.

It is important to understand the concept of context in DAX to write DAX formulas. This can be a bit difficult in the beginning of your DAX journey, but once you get a grasp on it, you can write effective DAX formulas that are required for complex and dynamic data analysis. For details, refer to the chapter – DAX Context.

Creating a DAX Formula

You have already learnt about the IntelliSense feature in a previous section. Remember to use it while creating any DAX formula.

To create a DAX formula, use the following steps −

• Type an equal sign.

• To the right of the equal sign, type the following −

  • Type the first letter of a function or table name and select the complete name from the dropdown list.

  • If you have chosen a function name, type parenthesis ‘(‘.

  • If you have chosen the table name, type bracket ‘[‘. Type the first letter of the column name and select the complete name from the dropdown list.

  • Close the column names with ‘]’ and function names with ‘)’.

  • Type a DAX operator between expressions or type ‘,’ to separate function arguments.

  • Repeat steps 1 - 5 till the DAX formula is complete.

For example, you want to find the total sales amount in the East region. You can write a DAX formula as shown below. East_Sales is the name of the table. Amount is a column in the table.

SUM ([East_Sales[Amount])

As already discussed in the chapter – DAX Syntax, it is a recommended practice to use the table name along with the column name in every reference to any column name. This is termed as – “the fully qualified name”.

The DAX formula can vary based on whether it is for a calculated field or calculated column. Refer to the sections below for details.

Creating a DAX Formula for a Calculated Column

You can create a DAX formula for a calculated column in the Power Pivot window.

• Click the tab of the table in which you want to add the calculated column.
• Click the Design tab on the Ribbon.
• Click Add.
• Type the DAX formula for the calculated column in the formula bar.

= DIVIDE (East_Sales[Amount], East_Sales[Units])

This DAX formula does the following for every row in the table East_Sales −

• Divides the value in Amount column of a row by the value in Units column in the same row.

• Places the result in the new added column in the same row.

• Repeats steps 1 and 2 iteratively till it completes all the rows in the table.

You have added a column for Unit Price at which those units are sold with the above formula.

• As you can observe, calculated columns require computation and storage space as well. Hence, use calculated columns only if necessary. Use calculated fields where possible and sufficient.

Refer to the chapter - Calculated Columns for details.

Creating a DAX Formula for a Calculated Field

You can create a DAX formula for a calculated field either in the Excel window or in the Power Pivot window. In the case of calculated field, you need to provide the name beforehand.

• To create a DAX formula for a calculated field in the Excel window, use the Calculated Field dialog box.

• To create a DAX formula for a calculated field in the Power Pivot window, click a cell in the calculation area in the relevant table. Start the DAX formula with CalculatedFieldName:=.

For example, Total East Sales Amount:=SUM ([East_Sales[Amount])

If you use Calculated Field dialog box in the Excel window, you can check the formula before you save it and make it as a mandatory habit to ensure the use of correct formulas.

For more details on these options, refer to the chapter – Calculated Fields.

Creating DAX Formulas Using the Formula Bar

Power Pivot window also has a formula bar that is like Excel window formula bar. Formula bar makes it easier to create and edit formulas, using the AutoComplete functionality so as to minimize syntax errors.

• To enter the name of a table, begin typing the name of the table. Formula AutoComplete provides a dropdown list containing valid table names that begin with those letters. You can start with one letter and type more letters to narrow down the list if required.

• To enter the name of a column, you can select it from the list of column names in the selected table. Type a bracket ‘[‘, to the right of the table name, and then choose the column from the list of columns in the selected table.

Tips for Using AutoComplete

Following are some tips for using AutoComplete −

• You can nest functions and formulas in a DAX formula. In such a case, you can use Formula AutoComplete in the middle of an existing formula with nested functions. The text immediately before the insertion point is used to display values in the dropdown list and all of the text after the insertion point remains unchanged.

• Defined names that you create for constants do not get displayed in the AutoComplete dropdown list, but you can still type them.

• The closing parenthesis of functions is not automatically added. You need to do it by yourself.

• You must make sure that each function is syntactically correct.

Understanding Insert Function Feature

You can find the Insert Function button labelled as fx, both in the Power Pivot window and Excel window.

• The Insert Function button in the Power Pivot window is to the left of formula bar.

• The Insert Function button in the Excel window is in the Calculated Field dialog box to the right of Formula.

When you click on the fx button, Insert Function dialog box appears. The Insert Function dialog box is the easiest way to find a DAX function that is relevant to your DAX formula.

The Insert Function dialog box helps you select functions by category and provides short descriptions for each function.

Using Insert Function in a DAX Formula

Suppose you want to create the following calculated field −

Medal Count: = COUNTA (]Medal])

You can use Insert Function dialog box using the following steps −

• Click the calculation area of the Results table.
• Type the following in the formula bar −

Medal Count: =

• Click the Insert Function button (fx).

Insert Function dialog box appears.

• Select Statistical in the Select a category box as shown in the following screenshot.

• Select COUNTA in the Select a function box as shown in the following screenshot.

As you can observe, the selected DAX function syntax and the function description are displayed. This enables you to make sure that it is the function that you want to insert.

• Click OK. Medal Count:=COUNTA( appears in the formula bar and a tooltip displaying the function syntax also appears.

• Type [. This means you are about to type a column name. The names of all the columns and the calculated fields in the current table will be displayed in the dropdown list. You can use IntelliSense to complete the formula.

• Type M. The displayed names in the dropdown list will be limited to those starting with ‘M’.

• Click Medal.

• Double-click Medal. Medal Count: = COUNTA([Medal] will be displayed in the formula bar. Close the parenthesis.

• Press Enter. You are done. You can use the same procedure to create a calculated column also. You can also follow the same steps to insert a function in the Calculated Field dialog box in the Excel window using the Insert Function feature.

• Click the Insert Function (fx) button to the right of Formula.

Insert Function dialog box appears. The rest of the steps are the same as above.

Using Multiple Functions in a DAX Formula

DAX formulas can contain up to 64 nested functions. But, it is unlikely that a DAX formula contains so many nested functions.

If a DAX formula has many nested functions, it has the following disadvantages −

• The formula would be very difficult to create.
• If the formula has errors, it would be very difficult to debug.
• The formula evaluation would not be very fast.

In such cases, you can split the formula into smaller manageable formulas and build the large formula incrementally.

Creating a DAX Formula Using Standard Aggregations

When you perform data analysis, you will perform calculations on aggregated data. There are several DAX aggregation functions, such as SUM, COUNT, MIN, MAX, DISTINCTCOUNT, etc. that you can use in DAX formulas.

You can automatically create formulas using standard aggregations by using the AutoSum feature in the Power Pivot window.

• Click the Results tab in the Power Pivot window. Results table will be displayed.
• Click the Medal column. The entire column – Medal will be selected.
• Click the Home tab on the Ribbon.
• Click the down arrow next to AutoSum in the Calculations group.

• Click COUNT in the dropdown list.

As you can observe, the calculated field Count of Medal appears in the calculation area below the column – Medal. The DAX formula also appears in the formula bar −

Count of Medal: = COUNTA([Medal])

The AutoSum feature has done the work for you – created the calculated field for data aggregation. Further, AutoSum has taken the appropriate variant of the DAX function COUNT, i.e. COUNTA (DAX has COUNT, COUNTA, COUNTAX functions).

A word of caution – To use AutoSum feature, you need to click the down arrow next to AutoSum on the Ribbon. If you click on the AutoSum itself instead, you will get −

Sum of Medal: = SUM([Medal])

And an error is flagged as Medal is not a numeric data column and the text in the column cannot be converted to numbers.

You can refer to the chapter - DAX Error Reference for details on DAX errors.

DAX Formulas and the Relational Model

As you are aware, in the Data Model of Power Pivot, you can work with multiple tables of data and connect the tables by defining relationships. This will enable you to create interesting DAX formulas that use the correlations of the columns among the related tables for calculations.

When you create a relationship between two tables, you are expected to make sure that the two columns used as keys have values that match, at least for most of the rows, if not completely. In the Power Pivot Data Model, it is possible to have non-matching values in a key column and still create a relationship, because Power Pivot does not enforce referential integrity (look at the next section for details). However, the presence of blank or non-matching values in a key column might affect the results of the DAX formulas and the appearance of PivotTables.

Referential Integrity

Establishing referential integrity involves building a set of rules to preserve the defined relationships between tables when you enter or delete data. If you do not exclusively ensure this, as Power Pivot does not enforce it, you might not get correct results with the DAX formulas created before data changes are made.

If you enforce referential integrity, you can prevent the following pitfalls −

• Adding rows to a related table when there is no associated row in the primary table (i.e. with matching values in the key columns).

• Changing data in a primary table that would result in orphan rows in a related table (i.e. rows with a data value in the key column that does not have a matching value in the primary table key column).

• Deleting rows from a primary table when there are matching data values in the rows of the related table.

Updating the Results of DAX Formulas

DAX formulas are used in calculations involving large data, including data from external data sources. The data can be subjected to changes from time to time as the DAX calculations are meant for live data as well.

The results of DAX formulas need to get updated on two occasions −

• Data Refresh − When the data is refreshed.

• Recalculation − When there are changes in the DAX formula.

Understanding Data Refresh vs. Recalculation

Data refresh and recalculation are two separate but related operations.

• Data refresh is the process of updating the data in the Data Model in your workbook obtaining up-to-date data from external data sources.

• Recalculation is the process of updating all the columns, tables, and PivotTables in your workbook that contain DAX formulas, to reflect the changes in the underlying data that result from the changes to DAX formulas themselves.

You should not save or publish the workbook until the DAX formulas in it have been recalculated.

Different Ways to Update Data in Data Model

Power Pivot does not automatically detect changes in external data sources.

• You can refresh data manually from the Power Pivot window at intervals that you can specify.

• You can schedule an automatic data refresh from external sources, if you have published the workbook to a SharePoint site.

For details on these, refer to the chapter – Updating Data in Data Model.

Recalculation of DAX Formulas

Recalculation of a DAX formula is an important task, because during recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid, or if an error has appeared in a DAX formula that used to work.

Recalculation can affect performance in the following ways −

• For a calculated column, the result of DAX formula should always be recalculated for the entire column, whenever you change the DAX formula.

• For a calculated field, the result of DAX formula is not calculated until the calculated field is placed in the context of a PivotTable or a PivotChart. The DAX formula will be recalculated when you change any row or column heading that affects the filters on the data or when you manually refresh the PivotTable.

In DAX, recalculating formulas can be done automatically or manually.

To learn more about recalculation, refer to the chapter – Recalculating DAX Formulas.

Excel DAX - Updating Data in the Data Model

DAX is used for calculations on the data in the Data Model in Excel Power Pivot. DAX enables data modeling and reporting activities to be handled in an effective way. However, this requires updating the data in the Data Model from time to time so as to reflect the current data.

You can import data from an external data source into the Data Model of your workbook by establishing a data connection. You can update the data from the source whenever you choose. This option is handy if you are getting data from relational databases that contain live sales information or data feeds that are updated several times a day.

Different Ways of Updating Data in the Data Model

You can update the data in the Data Model in the following ways −

• Refreshing data in the Data Model from time to time.
• Making changes to data sources, such as connection properties.
• Updating the data in the Data Model after the source data has changed.
• Filtering the data to selectively load rows from a table in the data source.

Refreshing Data in the Data Model

In addition to getting updated data from an existing source, you will need to refresh data in your workbook whenever you make changes to the schema of the source data. These changes can include adding columns or tables, or changing the rows that are imported.

Note that addition of data, changing data, or editing filters always triggers recalculation of DAX formulas that depend on that data source. Refer to the chapter – Recalculating DAX Formulas for details.

You have two types of data refresh in Data Model −

Manual Refresh

If you choose manual refresh option, you can refresh the data in the Data Model manually at any time. You can refresh all data, which is the default, or you can manually choose the tables and columns to refresh for individual data sources.

Automatic or Scheduled Refresh

If you have published your workbook to a PowerPivot Gallery or SharePoint site that supports PowerPivot, you or the SharePoint administrator can create a schedule for automatically updating the data in the workbook. In such a case, you can schedule unattended data refresh on the server.

Manually Refreshing an Existing Data Source

You can manually refresh your data any time, if you need to update the data from an existing data source or get the recent data for designing new DAX formulas. You can refresh a single table, all tables that share the same data connection or all tables in the Data Model.

If you have imported data from a relational data source, such as SQL Server and Oracle, you can update all the related tables in one operation. The operation of loading new or updated data into the Data Model often triggers recalculation of DAX formulas, both of which might require some time to complete. Hence, you should be aware of the potential impact before you change data sources or refresh the data that is obtained from the data source.

To refresh data for a single table or all tables in a Data Model, do the following −

• Click the Home tab on the Ribbon in the Power Pivot window.
• Click Refresh.
• Click Refresh in the dropdown list for refreshing the selected table.
• Click Refresh All in the dropdown list for refreshing all the tables.

To refresh data for all tables that use the same connection in a Data Model, do the following −

• Click the Home tab on the Ribbon in Power Pivot window.
• Click the Existing Connections in the Get External Data group.

Existing Connections dialog box appears.

• Select a connection.
• Click the Refresh button.

Data Refresh dialog box appears and data refresh progress information is displayed as the PowerPivot engine reloads data from the selected table or from all tables from the data source.

There are three possible outcomes −

• Success − Reports on the number of rows imported into each table.

• Error − An error can occur if the database is offline, you no longer have permissions. A table or column is deleted or renamed in the source.

• Cancelled − This means Excel did not issue the refresh request, probably because refresh is disabled on the connection.

Click the Close button.

Changing a Data Source

To change the data in your Data Model, you can edit the connection information or update the definition of the tables and columns used in your Data Model in the Power Pivot window.

You can make the following changes to the existing data sources −

Connections

• Edit the database name or the server name.
• Change the name of the source text file, spreadsheet, or data feed.
• Change the location of the data source.
• For relational data sources, change the default catalog or initial catalog.
• Change the authentication method or the credentials used to access the data.
• Edit advanced properties on the data source.

Tables

• Add or remove a filter on the data.
• Change the filter criteria.
• Add or remove tables.
• Change the table names.
• Edit mappings between tables in the data source and tables in the Data Model.
• Select different columns from the data source.

Columns

• Change the column names.
• Add new columns.
• Delete columns from the Data Model (does not affect the data source).

You can edit the properties of an existing data source in the following ways −

• You can change the connection information, including the file, feed, or database used as a source, its properties or other provider specific connection options.

• You can change the table and column mappings and remove references to columns that are no longer used.

• You can change the tables, views, or columns that you get from the external data source.

Modifying a Connection to an Existing Data Source

You can modify the connection that you have created to an external data source by changing the external data source used by the current connection. However, the procedure to be followed depends on the data source type.

• Click the Home tab on the Ribbon in the PowerPivot window.
• Click the Existing Connections in the Get External Data group.

Existing Connections dialog box appears. Select the connection that you want to modify.

Depending on the type of the data source you are changing, the provider might be different. Also the properties that are available may require change. Consider a simple example of a connection to an Excel workbook that contains the data.

• Click the Edit button. Edit Connection dialog box appears.

• Click the Browse button to locate another database of the same type (Excel workbook in this example), but with a different name or location.

• Click the Open button.

The new file will get selected. A message appears stating that you have modified connection information and you need to save and refresh the tables to verify the connection.

• Click the Save button. You will be back in the Existing Connections dialog box.

• Click the Refresh button. Data Refresh dialog box appears displaying the data refresh progress. The status of data refresh will be displayed. Refer to the section - Manually Refreshing an Existing Data Source for details.

• Click Close, once the data refresh is a success.

• Click Close in the Existing Connections dialog box.

Editing Table and Column Mappings (Bindings)

To edit the column mappings when a data source changes, do the following −

• Click the tab that contains the table you want to modify in the Power Pivot window.

• Click the Design tab on the Ribbon.

• Click the Table Properties.

Edit Table Properties dialog box appears.

You can observe the following −

• The name of the selected table in the Data Model is displayed in the Table Name box.

• The name of the corresponding table in the external data source is displayed in the Source Name box.

• There are two options for column names from – Source and Modal.

• If the columns are named differently in the data source and in the Data Model, you can toggle between the two sets of column names by selecting these options.

• Preview of the selected table appears in the dialog box.

You can edit the following −

• To change the table that is used as a data source, select a different table than the selected one in the Source Name dropdown list.

• Change the column mappings if needed −

  • To add a column that is present in the source but not in the Data Model, select the checkbox beside the column name. Repeat for all the columns that are to be added. The actual data will be loaded into the Data Model, the next time you refresh.

  • If some columns in the Data Model are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You do not need to do anything.

• Click the Save button.

When you save the current set of table properties, you will get a message – Please wait. Then the number of rows retrieved will be displayed.

In the table in the Data Model, any invalid columns are automatically removed and new columns are added.

Changing a Column Name and Data Type

You can change the name of a column in a table in the Data Model as follows −

• Double-click on the header of the column. The name of the column in the header will get highlighted.

• Type the new column name, overwriting the old name. Alternatively, you can change the name of a column in a table in the Data Model as follows:

• Select the column by clicking on its header.

• Right-click the column.

• Click Rename Column in the dropdown list.

The name of the column in the header will get highlighted. Type the new column name, overwriting the old name.

As you have learnt, all the values in a column in a table in the Data Model must be of the same data type.

To change the data type of a column, do the following −

• Select the column that you want to change by clicking its header.

• Click the Home tab on the Ribbon.

• Click the controls in the Formatting group to modify the column's data type and format.

Adding / Changing a Filter to a Data Source

You can add a filter to a data source when you import data to restrict the number of rows in the table in the Data Model. Later, you can add more rows or decrease the number of rows in the table in the Data Model by changing the filter that you defined earlier.

Adding a Filter to a Data Source During Import

To add a new filter to a data source during data import, do the following −

• Click the Home tab on the Ribbon in Power Pivot window.
• Click one of the data sources in the Get External Data group.

Table Import Wizard dialog box appears.

• Proceed to the step – Select Tables and Views.
• Select a table and then click Preview & Filter.

Preview Selected Table dialog box appears.

• Click the column on which you want to apply filter.
• Click the down arrow to the right of the column heading.

To add a filter, do one of the following −

• In the list of column values, select or clear one or more values to filter by and then click OK.

  However, if the number of values is extremely large, individual items might not be shown in the list. Instead, you will see the message - "Too many items to show."

• Click Number Filters or Text Filters (depending on the data type of the column).

  • Then, click one of the comparison operator commands (such as Equals), or click Custom Filter. In the Custom Filter dialog box, create the filter and then click OK.

Note − If you make a mistake at any stage, click the Clear Row Filters button and start over.

• Click OK. You will be back to Select Tables and Views page of Table Import Wizard.

As you can observe, in the column – Filter Details, a link Applied Filters appears for the column on which you defined the filter.

You can click the link to view the filter expression that was built by the wizard. But, the syntax for each filter expression depends on the provider and you cannot edit it.

• Click Finish to import the data with filters applied.
• Close the Table Import Wizard.

Changing a Filter to an Existing Data Source

After you have imported the data, you might have to update it from time to time, by either adding more rows or by restricting the existing rows in the table. In such a case, you can change the existing filters on the table or add new filters.

• Click the Home tab on the Ribbon in Power Pivot window.

• Click the Existing Connections in the Get External Data group. Existing Connections dialog box appears.

• Click the connection that contains the table on which you have to change the filter.

• Click the Open button.

You will get into Table Import Wizard dialog box. Repeat the steps in the previous section to filter the columns.

Excel DAX - Recalculating DAX Formulas

Recalculation of a DAX formula is required to reflect changes in the data and changes in the formula itself. However, recalculating a DAX formula involves performance cost.

Even then, to obtain accurate results, recalculation is essential. During recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid or if an error has appeared in a DAX formula that used to work.

Types of Recalculation

You have two options for recalculating DAX formulas −

• Automatic Recalculation Mode (default)
• Manual Recalculation Mode

By default, Power Pivot automatically recalculates as required while optimizing the time required for processing. However, you can choose to update calculations manually, if you are working with complex formulas or very large data sets and want to control the timing of updates.

Both automatic and manual modes of recalculating DAX formulas have advantages. However, the recommended way is to use automatic recalculation mode. This way you can keep the Power Pivot data in sync and prevent problems caused by deletion of data, changes in names or data types or missing dependencies.

Recalculating DAX Formulas Automatically

If you choose the default mode of recalculating DAX formulas, i.e. recalculating automatically, any changes to data that would cause the result of any DAX formula to change will trigger recalculation of the entire column that contains the DAX formula.

The following changes always require recalculation of DAX formulas −

• Values from an external data source have been refreshed.

• The DAX formula itself is changed.

• Names of tables or columns that are referenced in the DAX formula have been changed.

• Relationships between tables have been added, modified or deleted.

• New calculated fields or calculated columns have been added.

• Changes have been made to other DAX formulas within the workbook, so columns or calculations that depend on those DAX formulas need to be recalculated.

• Rows have been inserted or deleted in the table.

• You applied a filter that requires execution of a query to update the data set. The filter could have been applied either in a DAX formula or as part of a PivotTable or PivotChart.

When to Use Manual Recalculation Mode?

You can use manual recalculation mode until you are ready with all your required DAX formulas in your workbook. This way, you can avoid incurring the cost of computing formula results on the workbook that is still in the draft state.

You can use manual recalculation of DAX formulas in the following conditions −

• You are designing a DAX formula by using a template and want to change the names of the columns and tables used in the DAX formula before you validate it.

• You know that some data in the workbook has changed but you are working with a different column that has not changed so you want to postpone a recalculation.

• You are working in a workbook that has many dependencies and want to defer recalculation till you are sure that all the necessary changes have been made.

But, you should be aware that as long as the workbook is configured to manual recalculation mode, any validation or checking of formulas is not performed. This will result in the following −

• Any new formulas that you add to the workbook will be flagged as containing an error.

• No results will appear in the new calculated columns.

Configuring the Workbook for Manual Recalculation

As you have learnt, automatic recalculation is the default mode in the Data Model of any workbook. To configure a workbook for manual recalculation, do the following −

• Click the Design tab on the Ribbon in the Power Pivot window.
• Click the Calculation Options in the Calculations group.
• Click the Manual Calculation Mode in the dropdown list.

Recalculating DAX Formulas Manually

To recalculate the DAX formulas manually, do the following −

• Click the Design tab on the Ribbon in the Power Pivot window.
• Click the Calculation Options field in the Calculations group.
• Click the Calculate Now field in the dropdown list.

Troubleshooting DAX Formula Recalculation

Whenever changes occur in the Data Model of your workbook, Power Pivot performs an analysis of the existing data to determine whether recalculation is required and performs the update in the most efficient way possible.

Power Pivot handles the following, during recalculation of DAX formulas −

• Dependencies
• Sequence of recalculation for dependent columns
• Transactions
• Recalculation of volatile functions

Dependencies

When a column depends on another column, and the contents of that other column change in any way, all related columns might need to be recalculated.

Power Pivot always performs a complete recalculation for a table, because a complete recalculation is more efficient than checking for changed values. The changes that trigger recalculation might include deleting a column, changing the numeric data type of a column or adding a new column. These changes are considered as major changes. However, seemingly trivial changes, such as changing the name of a column might also trigger recalculation. This is because the names of the columns are used as identifiers in the DAX formulas.

In some cases, Power Pivot may determine that columns can be excluded from recalculation.

Sequence of Recalculation for Dependent Columns

Dependencies are calculated prior to any recalculation. If there are multiple columns that depend on each other, Power Pivot follows the sequence of dependencies. This ensures that the columns are processed in the right order at the maximum speed.

Transactions

Operations that recalculate or refresh data take place as a transaction. This means that if any part of the refresh operation fails, the remaining operations are rolled back. This is to ensure that data is not left in a partially processed state. However, you cannot manage the transactions as you do in a relational database or create checkpoints.

Recalculation of Volatile Functions

DAX functions such as NOW, RAND, or TODAY do not have fixed values and are referred to as volatile functions. If such DAX functions are used in a calculated column, the execution of a query or filtering will usually not cause them to be re-evaluated to avoid performance problems.

The results for these DAX functions are only recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of DAX formulas that contain these functions.

However, such functions will always be recalculated if the functions are used in the definition of a Calculated Field.

Excel DAX - Formula Errors

You can get errors when you write DAX formulas with wrong syntax. Calculated fields and calculated columns can contain DAX functions that require a specific type of arguments. Arguments of DAX functions can be tables, columns, or other DAX functions (nested DAX functions). As DAX functions can return tables and columns, care should be taken to check that the right type of arguments are passed to the DAX functions.

DAX formula errors can be either syntax errors or semantic errors. The errors can occur either at design time or at run time.

In this chapter, you will learn about some common DAX errors, their causes, and how to fix those errors.

DAX Error: Calculation Aborted

The following error can occur when attempting to create (design-time) or use (run-time) a calculated field with a DAX time-intelligence function. In each case, a noncontiguous date range is being passed to the time intelligence function.

“DAX Error: CALCULATION ABORTED: MdxScript (instance) (00, 0) Function ‘DATEADD’ only works with contiguous date selections.”

Cause at Run-time

This error can be displayed when a calculated field with a DAX time intelligence function is placed in the VALUES area of a PivotTable and date fields such as the month or the quarter are selected as slicers or filters before selecting a year. For example, if you have data of three years – 2014, 2015, and 2016 and you try to use only the month March without selecting the Year field, then the values are not contiguous data values and you will get an error.

How to Fix the Error at Run-time?

In the above example,

• First add Year as a slicer or a filter and select a year.

• Then, add Month or Quarter as a slicer or a filter.

• Then, select one or more months or quarters to slice or filter on for the year selected.

Cause at Design-time

DAX time intelligence functions require a date column specified for the date argument. The date column must have a contiguous range of dates. This error can be returned, if there is a date value in one or more rows in the date column that is not contiguous with the data values in the previous and successive rows.

If you imported your table containing dates from a data source, remember that many organizations run special processes that scan tables in databases for invalid values and replace those with a particular value. That is, if an invalid date is found, it is assigned a particular date value that may not be contiguous with other data values in the column.

How to Fix This Error at Design-time?

Do the following to fix the error at design time −

• If your date table is imported from a data source, use Refresh in Power Pivot window to reimport any changes found at the source.

• Check the values in your date column to make sure they are in a contiguous order. If any value is found not to be in place, it will have to be corrected at the source and the date table will have to be refreshed.

• Create a separate date table and date column in your Data Model. Specify the new date column as the date argument in the formula causing the error. Date tables are easy to create and add to a Data Model.

DAX Semantic Error - An Example

The following DAX error is a semantic error −

“A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed.”

Cause

This error can appear when one or more filter expressions cannot be used in context of the calculated field or calculated column expression.

In most of the cases, this error is caused by a filter expression specified as an argument to the DAX CALCULATE function. The CALCULATE function requires filters defined as a Boolean expression or a table expression.

How to Fix Such Errors?

You can fix such errors by using the DAX FILTER function to define filters as a table expression, which can then be used as an argument to the DAX CALCULATE function.

Excel DAX - Time Intelligence

DAX has an important and powerful feature, referred to as Time Intelligence. Time intelligence enables you to write DAX formulas that refer to the time periods for use in the PivotTables.

DAX has 35 time-intelligence functions specifically for aggregating and comparing data over time. However, these DAX functions have some constraints on the data that you need to understand and work with caution to avoid errors.

Why Time Intelligence Makes DAX Powerful?

The time intelligence functions work with data that is constantly changing, depending on the context you select in PivotTables and Power View visualizations. As you are aware, most of the data analysis involves summarization of data over time periods, comparing data values across the time periods, understanding the trends and making decisions based on future projections.

For example, you might want to sum sales amounts for the past month product-wise and compare the totals with those of other months in the fiscal year. This means, you have to use the dates as a way to group and aggregate sales transactions for a particular period in time.

This is where you can observe the power of DAX. You can use DAX time intelligence functions to define calculated fields that help you in analyzing the data over time, without having to change the date selections in the pivot tables. This makes your job easier. Moreover, you can build PivotTables that would not be possible any other way.

Requirements for DAX Time Intelligence Functions

DAX time intelligence functions have certain requirements. If these requirements are not met, you might get errors or they may not work properly. Hence, you can refer to these requirements as rules or constraints as well. Following are certain DAX time intelligence functions requirements/rules/constraints −

• You need to have a date table in your Data Model.

• The date table must include a column considered to be the Date column by DAX. You can name the column the way you want, but it should comply with the following conditions: o The date column should contain a contiguous set of dates that covers every day in the time period you are analyzing the data.

  • Every date must exist once and only once in the date column.

  • You cannot skip any dates (For e.g. you cannot skip weekend dates).

• DAX time intelligence functions work only on a standard calendar and assume the start of the year as January 1 and the end of the year as December 31, with the months in the year and days in each month as of a calendar year.

However, you can customize a standard calendar for different financial years. It is a good practice to verify the above requirements before any time intelligence function is used.

For more details on date tables and their usage in DAX formulas, refer to the tutorial = Data Modeling with DAX in this tutorials library.

DAX Time Intelligence Functions – Categories

DAX Time Intelligence functions can be categorized as follows −

• DAX functions that return a single date.
• DAX functions that return a table of dates.
• DAX functions that evaluate expressions over a time period.

DAX Functions That Return a Single Date

DAX functions in this category return a single date.

There are 10 DAX functions in this category −

Sr.No.	DAX Function & Return Value
1	FIRSTDATE (Date_Column) Returns the first date in the Date_Column in the current context.
2	LASTDATE (Date_Column) Returns the last date in the Date_Column in the current context.
3	FIRSTNONBLANK (Date_Column, Expression) Returns the first date where an expression has a non-blank value.
4	LASTNONBLANK (Date_Column, Expression) Returns the last date where an expression has a non-blank value.
5	STARTOFMONTH (Date_Column) Returns the first date of a month in the current context.
6	ENDOFMONTH (Date_Column) Returns the last date of a month in the current context.
7	STARTOFQUARTER (Date_Column) Returns the first date of a quarter in the current context.
8	ENDOFQUARTER (Date_Column) Returns the last date of a quarter in the current context.
9	STARTOFYEAR (Date_Column, [YE_Date]) Returns the first date of a year in the current context.
10	ENDOFYEAR (Date_Column, [YE_Date]) Returns the last date of a year in the current context.

DAX Functions That Return a Table of Dates

DAX Functions in this category return a table of dates. These functions will be mostly used as a SetFilter argument to the DAX function - CALCULATE.

There are 16 DAX functions in this category. Eight (8) of these DAX functions are the “previous” and “next” functions.

• The “previous” and “next” functions start with a date column in the current context and calculate the previous or next day, month, quarter or year.

• The “previous” functions work backward from the first date in the current context and the “next” functions move forward from the last date in the current context.

• The “previous” and “next” functions return the resulting dates in the form of a single column table.

Sr.No.	DAX Function & Return Value
1	PREVIOUSDAY (Date_Column) Returns a table that contains a column of all dates representing the day that is previous to the first date in the Date_Column in the current context.
2	NEXTDAY (Date_Column) Returns a table that contains a column of all dates from the next day, based on the first date specified in the Date_Column in the current context.
3	PREVIOUSMONTH (Date_Column) Returns a table that contains a column of all dates from the previous month, based on the first date in the Date_Column in the current context.
4	NEXTMONTH (Date_Column) Returns a table that contains a column of all dates from the next month, based on the first date in the Date_Column in the current context.
5	PREVIOUSQUARTER (Date_Column) Returns a table that contains a column of all dates from the previous quarter, based on the first date in the Date_Column in the current context.
6	NEXTQUARTER (Date_Column) Returns a table that contains a column of all dates in the next quarter, based on the first date specified in the Date_Column in the current context.
7	PREVIOUSYEAR (Date_Column, [YE_Date]) Returns a table that contains a column of all dates from the previous year, given the last date in the Date_Column in the current context.
8	NEXTYEAR (Date_Column, [YE_Date]) Returns a table that contains a column of all dates in the next year, based on the first date in the Date_Column in the current context.

Four (4) DAX functions calculate a set of dates in a period. These functions perform the calculations using the last date in the current context.

Sr.No.	DAX Function & Return Value
1	DATESMTD (Date_Column) Returns a table that contains a column of the dates for the month to date, in the current context.
2	DATESQTD (Date_Column) Returns a table that contains a column of the dates for the quarter to date, in the current context.
3	DATESYTD (Date_Column, [YE_Date]) Returns a table that contains a column of the dates for the year to date, in the current context.
4	SAMEPERIODLASTYEAR (Date_Column) Returns a table that contains a column of dates shifted one year back in time from the dates in the specified Date_Column, in the current context. Note− SAMEPERIODLASTYEAR requires that the current context contains a contiguous set of dates. If the current context is not a contiguous set of dates, then SAMEPERIODLASTYEAR will return an error.

• Four (4) DAX functions are used to shift from the set of dates that are in the current context to a new set of dates.

  These DAX functions are more powerful than the previous ones.

  • DAX functions – DATEADD, DATESINPERIOD and PARALLELPERIOD shift some number of time intervals from the current context. The interval can be day, month, quarter or year, represented by the key words – DAY, MONTH, QUARTER and YEAR respectively.

    For example:

• Shift backward by 2 days.

• Move forward by 5 months.

• Move forward by one month from today.

• Go back to same quarter in the last year.

  If the function argument - number of intervals (integer value) is positive, shift is forward and if it is negative, shift is backward.

  • DAX function – DATESBETWEEN calculates the set of dates between the specified start date and the end date.

Sr. Não.	Função DAX e valor de retorno
1	DATEADD (Date_Column, Number_of_Intervals, Interval) Retorna uma tabela que contém uma coluna de datas, deslocada para frente ou para trás no tempo pelo número especificado de intervalos das datas no contexto atual.
2	DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval) Retorna uma tabela que contém uma coluna de datas que começa com a data_início e continua para o número_de_intervalos especificado.
3	PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval) Retorna uma tabela que contém uma coluna de datas que representa um período paralelo às datas na Date_Column especificada no contexto atual, com as datas deslocadas em vários intervalos para frente ou para trás no tempo.
4	DATESBETWEEN (Date_Column, Start_Date, End_Date) Retorna uma tabela que contém uma coluna de datas que começa com start_date e continua até end_date.

Funções DAX que avaliam expressões ao longo de um período de tempo

As funções DAX nesta categoria avaliam uma expressão em um período de tempo especificado.

Existem nove (9) funções DAX nesta categoria -

• Três (3) funções DAX nesta categoria podem ser usadas para avaliar qualquer expressão em um período de tempo especificado.

Sr. Não.	Função DAX e valor de retorno
1	TOTALMTD (Expression, Date_Column, [SetFilter]) Avalia o valor da expressão para as datas do mês até a data, no contexto atual.
2	TOTALQTD (Expression, Date_Column, [SetFilter]) Avalia o valor da expressão para as datas do trimestre até a data, no contexto atual.
3	TOTALYTD (Expression, Date_Column, [SetFilter], [YE_Date]) Avalia o valor da expressão para as datas do ano até a data, no contexto atual

• Seis (6) funções DAX nesta categoria podem ser usadas para calcular os saldos de abertura e fechamento.

  • O saldo inicial de qualquer período é igual ao saldo final do período anterior.

  • O saldo final inclui todos os dados até o final do período, enquanto o saldo inicial não inclui quaisquer dados do período atual.

  • Essas funções DAX sempre retornam o valor de uma expressão avaliada para um ponto específico no tempo.

• O momento com o qual nos importamos é sempre o último valor de data possível em um período do calendário.

• O saldo inicial é baseado na última data do período anterior, enquanto o saldo final é baseado na última data do período atual.

• O período atual é sempre determinado pela última data no contexto da data atual.

Sr. Não.	Função DAX e valor de retorno
1	OPENINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Avalia a expressão na primeira data do mês no contexto atual.
2	CLOSINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Avalia a expressão na última data do mês no contexto atual.
3	OPENINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Avalia a expressão na primeira data do trimestre, no contexto atual.
4	CLOSINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Avalia a expressão na última data do trimestre no contexto atual.
5	OPENINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Avalia a expressão na primeira data do ano no contexto atual.
6	CLOSINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Avalia a expressão na última data do ano no contexto atual.

DAX tem um poderoso filter functionsque são bastante diferentes das funções do Excel. As funções de pesquisa funcionam usando tabelas e relacionamentos, como um banco de dados. As funções de filtragem permitem manipular o contexto de dados para criar cálculos dinâmicos.

Note- As funções de filtro DAX que retornam uma tabela não adicionam a tabela ao modelo de dados. A tabela resultante é usada como um argumento em outra função DAX. Ou seja, essas funções DAX são usadas como funções aninhadas com outras funções DAX.

Na próxima seção, você aprenderá quais funções de filtro DAX podem ser usadas. Para obter mais detalhes sobre essas funções, consulte o tutorial - Funções DAX nesta biblioteca de tutoriais.

Funções de filtro DAX

A seguir estão as funções do filtro DAX -

Sr. Não.	Função DAX e o que a função faz?
1	ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>] …, <table>, <groupingColumn>, [<groupingColumn>] …, [filterTable] …) Adiciona combinações de itens de várias colunas a uma tabela, se ainda não existirem. A determinação de quais combinações de itens adicionar é baseada na referência de colunas de origem que contêm todos os valores possíveis para as colunas. Para determinar as combinações de itens de diferentes colunas para avaliar - AutoExist é aplicado a colunas na mesma tabela. CrossJoin é aplicado em diferentes tabelas.
2	ALL ( {<table> | <column>, [<column>], [<column>], … }) Retorna todas as linhas na tabela fornecida ou todos os valores nas colunas especificadas em uma tabela, ignorando quaisquer filtros que possam ter sido aplicados. Esta função é útil para limpar filtros e criar cálculos em todas as linhas de uma tabela.
3	ALLEXCEPT (<table>, <column>, [<column>], …) Remove todos os filtros de contexto da tabela, exceto filtros que foram aplicados às colunas especificadas como argumentos. Ao contrário de TODOS, você pode usar esta função quando quiser remover os filtros em muitas, mas não em todas as colunas de uma tabela.
4	ALLNOBLANKROW (<table>|<column>) Da tabela pai de um relacionamento, retorna - todas as linhas, exceto as linhas em branco, ou todos os valores distintos de uma coluna, mas as linhas em branco A função desconsidera quaisquer filtros de contexto que possam existir.
5	ALLSELECTED ( [<tableName>|<columnName>] ) Remove filtros de contexto de colunas e linhas na consulta atual, enquanto retém todos os outros filtros de contexto ou filtros explícitos.
6	CALCULATE (<expression>, [<filter1>, <filter2> …)] Avalia uma expressão em um contexto que é modificado pelos filtros especificados. Retorna o valor que é o resultado da expressão.
7	CALCULATETABLE (<expression>, <filter1>, <filter2>, …) Avalia uma expressão de tabela em um contexto modificado pelos filtros fornecidos. Retorna uma tabela de valores.
8	CROSSFILTER (<columnName1>, <columnName2>, <direction>) Especifica a direção da filtragem cruzada a ser usada em um cálculo para um relacionamento que existe entre duas colunas. Não retorna nenhum valor.
9	DISTINCT (<column>) Retorna uma tabela de uma coluna que contém os valores distintos da coluna especificada. Em outras palavras, os valores duplicados são removidos e apenas os valores exclusivos são retornados. A coluna resultante é usada como um argumento em outra função DAX.
10	EARLIER (<column>, <number>) Retorna o valor atual da coluna especificada em uma passagem de avaliação externa da coluna mencionada especificada pelo número.
11	EARLIEST (<column>) Retorna o valor atual da coluna especificada em uma passagem de avaliação externa da coluna especificada.
12	FILTER (<table>, <filter>) Retorna uma tabela que contém apenas as linhas filtradas. FILTER é usado apenas como uma função embutida em outras funções que requerem uma tabela como argumento.
13	FILTERS (<columnName>) Retorna os valores que são aplicados diretamente como filtros a columnName. FILTERS é usado apenas como uma função embutida em outras funções que requerem uma tabela como argumento.
14	HASONEFILTER (<columnName>) Retorna TRUE quando o número de valores filtrados diretamente em columnName é um. Caso contrário, retorna FALSE.
15	HASONEVALUE (<columnName>) Retorna TRUE quando o contexto para columnName foi filtrado para apenas um valor distinto. Caso contrário, retorna FALSE.
16	ISCROSSFILTERED (<columnName>) Retorna TRUE quando columnName ou outra coluna na mesma tabela ou relacionada está sendo filtrada.
17	ISFILTERED (<columnName>) Retorna TRUE quando columnName está sendo filtrado diretamente. Se não houver filtro na coluna ou se a filtragem ocorrer porque uma coluna diferente na mesma tabela ou em uma tabela relacionada está sendo filtrada, a função retornará FALSE.
18	KEEPFILTERS (<expression>) Modifica como os filtros são aplicados ao avaliar uma função CALCULATE ou CALCULATETABLE.
19	RELATED (<column>) Retorna um valor relacionado de outra tabela.
20	RELATEDTABLE (<tableName>) Avalia uma expressão de tabela em um contexto modificado pelos filtros fornecidos.
21	SUBSTITUTEWITHINDEX (<table>, <indexColumnName>, <indexColumnTable>, <orderBy_expression>, [<order>]) Retorna uma tabela que representa uma semi-junção à esquerda das duas tabelas fornecidas como argumentos. O semijoin é executado usando colunas comuns, determinadas por nomes de colunas comuns e tipos de dados comuns. As colunas sendo unidas são substituídas por uma única coluna na tabela retornada que é do tipo inteiro e contém um índice. O índice é uma referência na tabela de junção certa, dada uma ordem de classificação especificada.
22	USERELATIONSHIP ( <columnName1>,<columnName2>) Especifica o relacionamento a ser usado em um cálculo específico como aquele que existe entre columnName1 e columnName2.
23	VALUES (<TableNameOrColumnName>) Retorna uma tabela de uma coluna que contém os valores distintos da tabela ou coluna especificada. Em outras palavras, os valores duplicados são removidos e apenas os valores exclusivos são retornados.

Você aprendeu a sintaxe DAX, o uso de operadores DAX e funções DAX nos capítulos anteriores. Como você sabe, DAX é uma linguagem de fórmula usada para modelagem e análise de dados.

O DAX pode ser usado em vários cenários. Com base nos cenários DAX, o DAX otimiza o desempenho e produz resultados precisos e eficazes. Neste capítulo, você conhecerá alguns dos cenários DAX.

Executando Cálculos Complexos

As fórmulas DAX podem realizar cálculos complexos que envolvem agregações personalizadas, filtragem e o uso de valores condicionais. Você pode fazer o seguinte com DAX

• Crie cálculos personalizados para uma tabela dinâmica.
• Aplique um filtro a uma fórmula.
• Remova filtros seletivamente para criar uma proporção dinâmica.
• Use um valor de um loop externo.

Para obter detalhes, consulte o capítulo Cenários - Executando cálculos complexos.

Trabalho com texto e datas

O DAX pode ser usado em cenários que envolvem trabalhar junto com texto, extração e composição de valores de data e hora ou criação de valores com base em uma condição. Você pode fazer o seguinte com DAX -

• Crie uma coluna-chave por concatenação.
• Componha uma data com base nas partes da data extraídas de uma data de texto.
• Defina uma data personalizada.
• Altere os tipos de dados usando uma fórmula.
  • Converta números reais em inteiros.
  • Converta números reais, inteiros ou datas em strings.
  • Converta strings em números reais ou datas.

Para obter detalhes, consulte o capítulo Cenários - Trabalhando com texto e datas.

Valores condicionais e teste de erros

As funções DAX permitem testar valores nos dados e retornar um valor diferente com base em uma condição. As funções DAX que testam valores também são úteis para verificar o intervalo ou tipo de valores, para evitar que erros de dados inesperados interrompam os cálculos. Você pode fazer o seguinte com DAX -

• Crie um valor com base em uma condição.
• Teste os erros em uma fórmula.

Para obter detalhes, consulte o capítulo Cenários - Valores condicionais e teste de erros.

Usando Time Intelligence

Você aprendeu sobre as funções de inteligência de tempo DAX no capítulo - Compreendendo a inteligência de tempo DAX.

As funções de inteligência de tempo DAX incluem funções para ajudá-lo a recuperar datas ou intervalos de datas de seus dados. Você pode então usar essas datas ou intervalos de datas para calcular valores em períodos semelhantes. As funções de inteligência de tempo também incluem funções que funcionam com intervalos de data padrão, para permitir que você compare valores entre meses, anos ou trimestres. Você também pode criar uma fórmula DAX que compare os valores da primeira e da última data de um período especificado.

Você pode aprender mais sobre as funções de inteligência DAX e o que elas podem fazer para o seguinte -

• Calcule as vendas cumulativas.
• Compare os valores ao longo do tempo.
• Calcule um valor em um intervalo de datas personalizado.

Para obter detalhes, consulte o capítulo Cenários - Usando Inteligência de Dados Temporais.

Classificação e comparação de valores

Se você deseja mostrar apenas o número n superior de itens em uma coluna ou uma tabela dinâmica, você tem as seguintes opções -

• Aplique um filtro para mostrar apenas os itens superiores ou inferiores.
• Crie uma fórmula DAX que classifique os valores dinamicamente e aplique um filtro.

Cada uma dessas opções tem prós e contras.

Para obter detalhes, consulte o capítulo Cenários - Classificação e comparação de valores.

As fórmulas DAX podem realizar cálculos complexos que envolvem agregações personalizadas, filtragem e o uso de valores condicionais. Você pode fazer o seguinte com DAX -

• Crie cálculos personalizados para uma tabela dinâmica.
• Aplique um filtro a uma fórmula DAX.
• Remova filtros seletivamente para criar uma proporção dinâmica.
• Use um valor de um loop externo.

Criação de cálculos personalizados para uma tabela dinâmica

As funções DAX CALCULATE e CALCULATETABLE são poderosas e flexíveis. Eles são úteis para definir campos calculados. Essas funções DAX permitem que você altere o contexto no qual o cálculo será executado. Você também pode personalizar o tipo de agregação ou operação matemática a ser executada.

Função CALCULATE

CALCULAR (<expressão>, [<filtro1>], [<filtro2>] ...)

A função CALCULATE avalia a expressão dada em um contexto que é modificado por zero ou mais filtros especificados.

Se seus dados foram filtrados, a função CALCULATE altera o contexto no qual os dados são filtrados e avalia a expressão no novo contexto que você especifica pelos filtros. Isso significa que todos os filtros existentes na coluna especificada são removidos e o filtro usado no argumento do filtro é aplicado.

Exemplo

Suponha que você deseja exibir a porcentagem de medalhas filtradas por nomes de países. Seu cálculo deve obter o valor da porcentagem substituindo o filtro que você aplica ao país na tabela dinâmica.

• Defina um campo calculado - Porcentagem da contagem de medalhas, conforme mostrado na captura de tela a seguir.

Com esta fórmula DAX, todas as linhas da tabela Resultados são consideradas na função CALCULAR com o filtro que contém a função TODOS. Dessa forma, você tem a contagem total no denominador.

Sua Tabela Dinâmica será mostrada na imagem a seguir.

Na captura de tela acima, País é filtrado para EUA e os 18 principais valores são exibidos na Tabela Dinâmica. Em seguida, você pode filtrar dinamicamente os valores na Tabela Dinâmica. No entanto, os cálculos serão corretos pela fórmula DAX personalizada que você usou.

A função CALCULATETABLE pega uma tabela de valores e executa a mesma ação que a função CALCULATE.

Filtrando dados em fórmulas

Você pode criar filtros nas fórmulas DAX para selecionar os valores dos dados de origem para uso em cálculos. Você pode fazer isso definindo uma expressão de filtro e usando-a junto com a tabela que é uma entrada para a fórmula DAX.

A expressão de filtro permite que você obtenha um subconjunto dos dados de origem. O filtro é aplicado dinamicamente cada vez que você atualiza os resultados da fórmula DAX, dependendo do contexto atual de seus dados e você pode ter certeza de resultados precisos e esperados.

A expressão de filtro normalmente contém uma função de filtro DAX que retorna apenas linhas selecionadas da tabela, que então podem ser usadas como um argumento para outra função DAX que você está usando para agregação de dados.

Exemplo

A captura de tela a seguir mostra a definição de um campo calculado que fornece a contagem de medalhas apenas para esportes de verão.

Com este campo calculado, a tabela dinâmica tem a aparência mostrada na captura de tela a seguir.

Como você pode observar, os valores da Tabela Dinâmica do lado direito com o novo campo calculado correspondem aos que estão na Tabela Dinâmica do lado esquerdo com o filtro no campo Temporada aplicado explicitamente.

Note - As funções de filtro e valor DAX retornam uma tabela, mas nunca retornam a tabela ou as linhas diretamente para o Modelo de Dados e, portanto, estão sempre incorporadas em outra função DAX.

Para obter detalhes sobre essas funções DAX, consulte o capítulo - Funções de filtro DAX.

Adicionar e remover filtros dinamicamente

As fórmulas DAX que você usa em uma tabela dinâmica podem ser afetadas pelo contexto da tabela dinâmica. No entanto, você pode alterar seletivamente o contexto adicionando ou removendo filtros. Você pode usar as funções DAX ALL e ALLEXCEPT para selecionar dinamicamente as linhas, independentemente do contexto da tabela dinâmica.

Além disso, você pode usar as funções DAX DISTINCT e VALUES para retornar valores distintos.

Usando um valor de um loop externo

Você pode usar um valor de um loop anterior na criação de um conjunto de cálculos relacionados com a função DAX EARLIER. Esta função DAX suporta até dois níveis de loops aninhados.

O DAX pode ser usado em cenários que envolvem trabalhar junto com texto, extração e composição de valores de data e hora ou criação de valores com base em uma condição. Você pode fazer o seguinte com DAX -

• Crie uma coluna-chave em uma tabela por concatenação.
• Componha uma data com base nas partes da data extraídas de uma data de texto.
• Defina um formato de data personalizado.
• Altere os tipos de dados usando uma fórmula.
  • Converta números reais em inteiros.
  • Converta números reais, inteiros ou datas em strings.
  • Converta strings em números reais ou datas.

Criação de uma coluna chave por concatenação

O modelo de dados no PowerPivot permite apenas uma única coluna de chave. Ele não oferece suporte a chaves compostas que você possa encontrar nas fontes de dados externas. Portanto, se houver chaves compostas em uma tabela na fonte de dados, será necessário combiná-las em uma única coluna de chave para a tabela no Modelo de Dados.

Você pode usar a função DAX CONCATENATE para combinar duas colunas em uma única coluna em uma tabela no Modelo de Dados. A função DAX CONCATENATE une duas strings de texto em uma string de texto. Os itens unidos podem ser texto, números ou valores booleanos representados como texto ou uma combinação desses itens. Você também pode usar uma referência de coluna se a coluna contiver valores apropriados.

= CONCATENATE ([Column1], [Column2])

A função DAX CONCATENATE aceita apenas dois argumentos. Se algum dos argumentos não for do tipo de dados de texto, ele será convertido em texto. A função DAX CONCATENATE retorna a string concatenada.

Data com base nas partes da data extraídas de uma data de texto

O Modelo de Dados no Power Pivot oferece suporte a um tipo de dados datetime para valores de data e hora. As funções DAX que funcionam em valores de data e / ou hora requerem o tipo de dados datetime para os argumentos.

Se sua fonte de dados contiver datas em um formato diferente, você precisará primeiro extrair as partes da data usando uma fórmula DAX e combinar essas partes para constituir um tipo de dados datetime válido do DAX.

Você pode usar as seguintes funções DAX para extrair e compor datas -

DATE - Retorna a data especificada no formato datetime.

DATEVALUE - Converte uma data na forma de texto em uma data no formato datetime.

TIMEVALUE - Converte uma hora no formato de texto em uma hora no formato datetime.

Definindo um formato de data personalizado

Suponha que as datas em sua fonte de dados não sejam representadas por um formato padrão. Você pode definir um formato de data personalizado para garantir que os valores sejam tratados corretamente. A função DAX FORMAT permite converter um valor em texto de acordo com o formato especificado.

FORMAT (<value>, <format_string>)

A função FORMAT retorna uma string contendo um valor formatado conforme definido por format_string.

Você pode usar formatos de data e hora predefinidos ou pode criar formatos de data e hora definidos pelo usuário para o argumento format_string da função FORMAT.

A seguir estão os nomes de formato de data e hora predefinidos. Se você usar strings diferentes dessas strings predefinidas, elas serão interpretadas como um formato de data e hora personalizado.

S. No.	Format_String e Descrição
1	"General Date" Exibe uma data e / ou hora. Por exemplo, 2/10/2015 10:10:32 AM
2	"Long Date" or "Medium Date" Exibe uma data de acordo com o formato de data longa. Por exemplo, quarta-feira, 7 de março de 2016
3	"Short Date" Exibe uma data usando o formato de data abreviada. Por exemplo, 2/03/2016
4	"Long Time" Exibe uma hora usando o formato de hora longa. Normalmente inclui horas, minutos e segundos. Por exemplo, 10:10:32 AM
5	"Medium Time" Exibe uma hora no formato de 12 horas. Por exemplo, 21:30
6	"Short Time" Exibe uma hora no formato de 24 horas. Por exemplo, 14h15

Como alternativa, você pode usar os caracteres da tabela a seguir para criar formatos de data / hora definidos pelo usuário.

S. No.	Descrição do personagem
1	: Separador de hora. Separador de hora. Separa horas, minutos e segundos quando os valores de hora são formatados.
2	/ Separador de data. Separa o dia, mês e ano quando os valores de data são formatados.
3	% Usado para indicar que o caractere a seguir deve ser lido como um formato de uma única letra, sem levar em consideração as letras finais. Também usado para indicar que um formato de letra única é lido como um formato definido pelo usuário.

A seguir estão os detalhes dos vários personagens.

• %d - Exibe o dia como um número sem um zero à esquerda (por exemplo, 5).

• %dd - Exibe o dia como um número com um zero à esquerda (por exemplo, 05).

• %ddd - Exibe o dia como uma abreviatura (por exemplo, Sol).

• %dddd - Exibe o dia como um nome completo (por exemplo, domingo).

• %M - Exibe o mês como um número sem um zero à esquerda (por exemplo, janeiro é representado como 1).

• %MM - Exibe o mês como um número com um zero à esquerda (por exemplo, janeiro é representado como 01).

• %MMM - Exibe o mês como uma abreviatura (por exemplo, janeiro é representado como janeiro).

• %MMMM - Exibe o mês como um nome de mês completo (por exemplo, janeiro).

• %gg - Exibe a seqüência de período / era (por exemplo, AD).

• %h- Exibe a hora como um número sem zeros à esquerda usando o relógio de 12 horas (por exemplo, 1:15:15 PM). Usar%h se este for o único caractere em seu formato numérico definido pelo usuário.

• %hh - Exibe a hora como um número com zeros à esquerda usando o relógio de 12 horas (por exemplo, 01:15:15 PM).

• %H- Exibe a hora como um número sem zeros à esquerda usando o relógio de 24 horas (por exemplo, 13:15:15, 1:15:15). Use% H se este for o único caractere em seu formato numérico definido pelo usuário.

• %HH - Exibe a hora como um número com zeros à esquerda usando o relógio de 24 horas (por exemplo, 13:15:15, 1:15:15).

• %m- Exibe o minuto como um número sem zeros à esquerda (por exemplo, 2: 1: 15). Use% m se este for o único caractere em seu formato numérico definido pelo usuário.

• %mm - Exibe o minuto como um número com zeros à esquerda (por exemplo, 2:01:15).

• %s- Exibe o segundo como um número sem zeros à esquerda (por exemplo, 2: 15: 5). Use% s se este for o único caractere em seu formato numérico definido pelo usuário.

• %ss - Exibe o segundo como um número com zeros à esquerda (por exemplo, 2:15:05).

• %f- Exibe frações de segundos. Por exemploff exibe centésimos de segundos, enquanto ffffexibe dez milésimos de segundos. Você pode usar até setefsímbolos em seu formato definido pelo usuário. Usar%f se este for o único caractere em seu formato numérico definido pelo usuário.

• %t- Usa o relógio de 12 horas e exibe um A maiúsculo para qualquer hora antes do meio-dia; exibe um P maiúsculo para qualquer hora entre meio-dia e 23:59. Use% t se este for o único caractere em seu formato numérico definido pelo usuário.

• %tt- Para localidades que usam um relógio de 12 horas, exibe uma AM maiúscula com qualquer hora antes do meio-dia; exibe PM em maiúsculas com qualquer hora entre meio-dia e 23:59. Para locais que usam um relógio de 24 horas, não exibe nada.

• %y- Exibe o número do ano (0-9) sem zeros à esquerda. Usar%y se este for o único caractere em seu formato numérico definido pelo usuário.

• %yy - Exibe o ano em formato numérico de dois dígitos com um zero à esquerda, se aplicável.

• %yyy - Exibe o ano em formato numérico de quatro dígitos.

• %yyyy - Exibe o ano em formato numérico de quatro dígitos.

• %z- Exibe a diferença de fuso horário sem um zero à esquerda (por exemplo, -8). Usar%z se este for o único caractere em seu formato numérico definido pelo usuário.

• %zz- Exibe o. deslocamento de fuso horário com um zero à esquerda (por exemplo, -08)

• %zzz - Exibe a diferença de fuso horário completo (por exemplo, -08: 00).

Como você pode observar, as strings de formatação diferenciam maiúsculas de minúsculas. Uma formatação diferente pode ser obtida usando um caso diferente.

Alterar tipos de dados de saídas de fórmula DAX

Em fórmulas DAX, o tipo de dados da saída é determinado pelas colunas de origem e você não pode especificar explicitamente o tipo de dados do resultado. Isso ocorre porque o tipo de dados ideal é determinado pelo Power Pivot. No entanto, você pode usar as conversões de tipo de dados implícitas realizadas pelo Power Pivot para manipular o tipo de dados de saída. Caso contrário, você pode usar certas funções DAX para converter o tipo de dados de saída.

Using the Implicit Data Type Conversions

• Para converter uma data ou uma string numérica em um número, multiplique por 1,0. Por exemplo, = (TODAY () + 5) * 1.0. Esta fórmula calcula a data atual mais 5 dias e converte o resultado em um valor inteiro.

  • Para converter uma data, número ou valor monetário em uma string, concatene o valor com uma string vazia. Por exemplo, = Hoje () & “”

Using the DAX Functions for Data Type Conversions

Você pode usar funções DAX para o seguinte -

• Conversão de números reais em inteiros.
• Conversão de números reais, inteiros ou datas em strings.
• Convertendo Strings em Números ou Datas Reais.

Você aprenderá isso nas seções a seguir.

Conversão de números reais em inteiros

Você pode usar as seguintes funções DAX para converter números reais em inteiros -

ROUND (<number>, <num_digits>) - Arredonda um número para o número especificado de dígitos e retorna um número decimal.

CEILING (<number>, <significance>) - Arredonda um número para cima, para o inteiro mais próximo ou para o múltiplo de significância mais próximo e retorna um número decimal.

FLOOR (<number>, <significance>) - Arredonda um número para baixo, em direção a zero, para o múltiplo de significância mais próximo e retorna um número decimal.

Conversão de números reais, inteiros ou datas em strings

Você pode usar as seguintes funções DAX para converter números reais, inteiros ou datas em strings -

FIXED (<number>, [<decimals>], [<no_comma>])- Arredonda um número e retorna o resultado como texto. O número de dígitos à direita da casa decimal é 2 ou o número especificado de decimais. O resultado é com vírgulas ou, opcionalmente, sem vírgulas.

FORMAT (<value>, <format_string>) - Converte um valor em texto de acordo com o formato especificado.

Você já aprendeu como usar a função Format para converter datas em strings.

Convertendo Strings em Números ou Datas Reais

Você pode usar as seguintes funções DAX para converter strings em números reais ou datas -

VALUE (<text>) - Converte uma string de texto que representa um número em um número.

DATEVALUE (date_text) - Converte uma data na forma de texto em uma data no formato datetime.

TIMEVALUE (time_text) - Converte uma hora no formato de texto em uma hora no formato datetime.

Você pode usar funções DAX para testar os valores nos dados que resultam em valores diferentes com base em uma condição. Por exemplo, você pode testar o valor das vendas anuais e, com base no resultado, rotular os revendedores como Preferencial ou Valor.

Você também pode usar funções DAX para verificar o intervalo ou o tipo de valores, para evitar que erros de dados inesperados quebrem os cálculos.

Criando um valor com base em uma condição

Você pode usar condições IF aninhadas para testar valores e gerar novos valores condicionalmente. As seguintes funções DAX são úteis para processamento condicional e valores condicionais -

IF (<logical_test>,<value_if_true>, [<value_if_false>])- Verifica se uma condição é atendida. Retorna um valor se a condição for TRUE e retorna outro valor se a condição for FALSE. Valor_se_falso é opcional, e se omitido e a condição for FALSO, a função retorna BLANK ().

OR (<logical1>,<logical2>)- Verifica se um dos argumentos é TRUE para retornar TRUE. A função retorna FALSE se ambos os argumentos forem FALSE.

CONCATENATE (<text1>, <text2>)- Une duas strings de texto em uma string de texto. Os itens unidos podem ser texto, números ou valores booleanos representados como texto ou uma combinação desses itens. Você também pode usar uma referência de coluna, se a coluna contiver valores apropriados.

Teste de erros em uma fórmula DAX

No DAX, você não pode ter valores válidos em uma linha de uma coluna calculada e valores inválidos em outra linha. Ou seja, se houver um erro em qualquer parte de uma coluna calculada, a coluna inteira será sinalizada com um erro e você deverá corrigir a fórmula DAX para remover os erros que resultam em valores inválidos.

Alguns erros comuns em fórmulas DAX são -

• Divisão por zero.
• O argumento para uma função está em branco, enquanto o argumento esperado é um valor numérico.

Você pode usar uma combinação de funções lógicas e de informação para testar os erros e sempre retornar valores válidos para evitar o retorno de erros em uma coluna calculada. As funções DAX a seguir o ajudam nisso.

ISBLANK (<value>) - Verifica se um valor está em branco e retorna TRUE ou FALSE.

IFERROR (value, value_if_error)- Retorna value_if_error se a expressão no primeiro argumento resultar em um erro. Caso contrário, retorna o valor da própria expressão.

Ambos os valores de retorno da expressão e value_if_error devem ser do mesmo tipo de dados. Caso contrário, você obterá um erro.

Você aprendeu sobre o poderoso recurso Data Intelligence do DAX no capítulo - Compreendendo o Time Intelligence. Neste capítulo, você aprenderá como usar as funções de inteligência de tempo DAX em vários cenários.

As funções de inteligência de tempo DAX incluem -

• Funções que ajudam a recuperar datas ou intervalos de datas de seus dados, que são usados ​​para calcular valores em períodos semelhantes.

• Funções que funcionam com intervalos de data padrão, para permitir que você compare valores entre meses, anos ou trimestres.

• Funções que recuperam a primeira e a última data de um período especificado.

• Funções que o ajudam a trabalhar nos saldos iniciais e finais.

Calculando Vendas Cumulativas

Você pode usar funções de inteligência de tempo DAX para criar fórmulas para calcular vendas cumulativas. As seguintes funções DAX podem ser usadas para calcular saldos de fechamento e abertura -

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - Avalia a expressão na última data do mês no contexto atual.

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - Avalia a expressão na primeira data do mês no contexto atual.

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - Avalia a expressão na última data do trimestre no contexto atual.

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - Avalia a expressão na primeira data do trimestre, no contexto atual.

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) - Avalia a expressão na última data do ano no contexto atual.

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) - Avalia a expressão na primeira data do ano no contexto atual.

Você pode criar os seguintes campos calculados para o estoque de produtos em um momento especificado usando as seguintes funções DAX -

Month Start Inventory Value: = OPENINGBALANCEMONTH ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Month End Inventory Value: = CLOSINGBALANCEMONTH ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Quarter Start Inventory Value: = OPENINGBALANCEQUARTER ( 
   SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Quarter End Inventory Value: = CLOSINGBALANCEQUARTER ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Year Start Inventory Value: = OPENINGBALANCEYEAR ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Year End Inventory Value: = CLOSINGBALANCEYEAR ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Comparando valores em diferentes períodos de tempo

Os períodos de tempo padrão suportados pelo DAX são meses, trimestres e anos.

Você pode usar as seguintes funções de inteligência de tempo DAX para comparar as somas em diferentes períodos de tempo.

• PREVIOUSMONTH (<dates>) - Retorna uma tabela que contém uma coluna de todas as datas do mês anterior, com base na primeira data da coluna de datas, no contexto atual.

• PREVIOUSQUARTER (<dates>) - Retorna uma tabela que contém uma coluna de todas as datas do trimestre anterior, com base na primeira data da coluna de datas, no contexto atual.

• PREVIOUSYEAR (<dates>, <year_end_date>]) - Retorna uma tabela que contém uma coluna de todas as datas do ano anterior, considerando a última data na coluna de datas, no contexto atual.

Você pode criar os seguintes campos calculados para calcular a soma das vendas na região oeste nos períodos de tempo especificados para comparação, usando as funções DAX -

Previous Month Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
)

Previous Quarter Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
)

Previous Year Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
)

Comparando Valores em Períodos de Tempo Paralelos

Você pode usar a função de inteligência de tempo DAX PARALLELPERIOD para comparar as somas em um período paralelo ao período de tempo especificado.

PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>)

Esta função DAX retorna uma tabela que contém uma coluna de datas que representam um período paralelo às datas na coluna de datas especificadas, no contexto atual, com as datas deslocadas em vários intervalos para frente ou para trás no tempo.

Você pode criar o seguinte campo calculado para calcular as vendas do ano anterior na região oeste -

Previous Year Sales: = CALCULATE ( 
   SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year)
)

Calculando Totais Correntes

Você pode usar as seguintes funções de inteligência de tempo DAX para calcular totais ou somas correntes.

• TOTALMTD (<expression>,<dates>, [<filter>]) - Avalia o valor da expressão para o mês até a data no contexto atual.

• TOTALQTD (<expression>,<dates>, <filter>]) - Avalia o valor da expressão para as datas do trimestre em curso, no contexto atual.

• TOTALYTD (<expression>,<dates>, [<filter>], [<year_end_date>]) - Avalia o valor acumulado do ano da expressão no contexto atual.

Você pode criar os seguintes campos calculados para calcular a soma contínua das vendas na região oeste em períodos de tempo especificados, usando as funções DAX -

Soma do mês corrente: = TOTALMTD (SUM (West_Sales [SalesAmount]), DateTime [DateKey])

Soma do trimestre corrente: = TOTALQTD (SUM (WestSales [SalesAmount]), DateTime [DateKey])

Soma do ano corrente: = TOTALYTD (SUM (WestSales [SalesAmount]), DateTime [DateKey])

Calculando um valor em um intervalo de datas personalizado

Você pode usar funções de inteligência de tempo DAX para recuperar um conjunto personalizado de datas, que pode ser usado como uma entrada para uma função DAX que executa cálculos, para criar agregados personalizados em períodos de tempo.

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) - Retorna uma tabela que contém uma coluna de datas que começa com a data_início e continua para o número_de_intervalos especificado.

DATESBETWEEN (<dates>, <start_date>, ) - Retorna uma tabela que contém uma coluna de datas que começa com start_date e continua até end_date.

DATEADD (<dates>,<number_of_intervals>,<interval>) - Retorna uma tabela que contém uma coluna de datas, deslocadas para frente ou para trás no tempo pelo número especificado de intervalos das datas no contexto atual.

FIRSTDATE (<dates>) - Retorna a primeira data no contexto atual para a coluna de datas especificada.

LASTDATE (<dates>) - Retorna a última data no contexto atual para a coluna de datas especificada.

Você pode criar as seguintes fórmulas DAX para calcular a soma das vendas na região oeste ao longo de um intervalo de datas especificado, usando as funções DAX -

• Fórmula DAX para calcular as vendas dos 15 dias anteriores a 17 de julho de 2016.

CALCULATE ( 
   SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day)
)

• Fórmula DAX para criar um campo calculado que calcula as vendas do primeiro trimestre de 2016.

= CALCULATE (
   SUM (WestSales[SalesAmount]),DATESBETWEEN (DateTime[DateKey], DATE (2016,1,1), DATE (2016,3,31))
)

• Fórmula DAX para criar um campo calculado que obtém a primeira data em que uma venda foi feita na região Oeste para o contexto atual.

= FIRSTDATE (WestSales [SaleDateKey])

• Fórmula DAX para criar um campo calculado que obtém a última data em que foi feita uma venda na região Oeste para o contexto atual.

= LASTDATE (WestSales [SaleDateKey])

• Fórmula DAX para calcular as datas que são um ano antes das datas no contexto atual.

= DATEADD (DateTime[DateKey],-1,year)

Se você quiser mostrar apenas o topo n número de itens em uma coluna ou tabela dinâmica, você tem as duas opções a seguir -

• Você pode selecionar n número dos principais valores na tabela dinâmica.

• Você pode criar uma fórmula DAX que classifique valores dinamicamente e depois use os valores de classificação em um Slicer.

Aplicação de um filtro para mostrar apenas os primeiros itens

Selecionar n número de valores principais para exibição na Tabela Dinâmica, faça o seguinte -

• Clique na seta para baixo no cabeçalho dos rótulos das linhas na Tabela Dinâmica.
• Clique em Filtros de valor na lista suspensa e clique em 10 principais.

A caixa de diálogo Top 10 Filter (<nome da coluna>) é exibida.

• Em Mostrar, selecione o seguinte nas caixas da esquerda para a direita.
  • Top
  • 18 (O número dos principais valores que você deseja exibir. O padrão é 10.)
  • Items.
  • Na caixa por, selecione Contagem de medalhas.

• Clique OK. Os 18 valores principais serão exibidos na Tabela Dinâmica.

Vantagens e desvantagens da aplicação de filtro

Vantagens

• É simples e fácil de usar.
• Adequado para tabelas com grande número de linhas.

Desvantagens

• O filtro é apenas para fins de exibição.

• Se os dados subjacentes à tabela dinâmica forem alterados, você deverá atualizar manualmente a tabela dinâmica para ver as alterações.

Criação de uma fórmula DAX que classifica valores dinamicamente

Você pode criar uma coluna calculada usando uma fórmula DAX que contém os valores classificados. Você pode então usar um divisor na coluna calculada resultante para selecionar os valores a serem exibidos.

Você pode obter um valor de classificação para um determinado valor em uma linha contando o número de linhas na mesma tabela com um valor maior do que aquele que está sendo comparado. Este método retorna o seguinte -

• Um valor zero para o valor mais alto da tabela.

• Valores iguais terão o mesmo valor de classificação. E sen número de valores são iguais, o próximo valor após os valores iguais terá um valor de classificação não consecutivo somando o número n.

Por exemplo, se você tiver uma tabela 'Vendas' com dados de vendas, poderá criar uma coluna calculada com as classificações dos valores de Valor de Vendas da seguinte forma -

= COUNTROWS (FILTER (Sales,  
   EARLIER (Sales [Sales Amount]) < Sales [Sales Amount])
) + 1

Em seguida, você pode inserir um Slicer na nova coluna calculada e exibir seletivamente os valores por classificações.

Vantagens e desvantagens das classificações dinâmicas

Vantagens

• A classificação é feita na tabela e não em uma tabela dinâmica. Portanto, pode ser usado em qualquer número de tabelas dinâmicas.

• As fórmulas DAX são calculadas dinamicamente. Portanto, você sempre pode ter certeza de que a classificação está correta, mesmo que os dados subjacentes tenham mudado.

• Como a fórmula DAX é usada em uma coluna calculada, você pode usar a classificação em um Slicer.

• Adequado para tabelas com grande número de linhas.

Desvantagens

Como os cálculos DAX são caros do ponto de vista computacional, esse método pode não ser adequado para tabelas com grande número de linhas.