Lab – Introdução ao DAX no Power BI Desktop

Neste laboratório, você criará tabelas e colunas calculadas e medidas simples usando DAX (Data Analysis Expressions).

Neste laboratório, você aprenderá a:

  • Criar tabelas calculadas

  • Criar colunas calculadas

  • Criar medidas

Exercício 1: criar tabelas calculadas

Neste exercício, você criará duas tabelas calculadas. A primeira será a tabela Vendedor, para permitir um relacionamento direto entre ela e a tabela Vendas. O segundo será a tabela Data.

Abra o arquivo inicial Sales Analysis.pbix, encontrado na pasta D:\DA100\Lab06A\Starter.

Tarefa 1: criar a tabela Vendedor

Nesta tarefa, você criará a tabela Vendedor (relacionamento direto com Vendas).

  1. No Power BI Desktop, na exibição Relatório, na faixa de opções Modelagem dentro do grupo Cálculos, clique em Nova Tabela.

    Nova Tabela

  2. Na barra de fórmulas (que é aberta diretamente abaixo da faixa de opções ao criar ou editar cálculos), digite Vendedor =, pressione Shift+Enter, digite "Vendedor (Desempenho)" e pressione Enter.

    Vendedor =, Shift+Enter, digite Vendedor (Desempenho)

    Para sua conveniência, todas as definições DAX neste laboratório podem ser copiadas do arquivo D:\DA100\Lab06A\Assets\Snippets.txt.

    Uma tabela calculada é criada inserindo primeiro o nome da tabela, seguido pelo símbolo de igual (=), seguido por uma fórmula DAX que retorna uma tabela. O nome da tabela não pode existir no modelo de dados.

    A barra de fórmulas dá suporte à inserção de uma fórmula DAX válida. Ela inclui recursos como preenchimento automático, IntelliSense e codificação por cores, permitindo que você insira a fórmula com rapidez e precisão.

    Esta definição de tabela cria uma cópia da tabela Vendedor (Desempenho). Ela copia somente os dados, no entanto, propriedades como visibilidade, formatação etc. não são copiadas.

    Dica

    Recomendamos inserir "espaço em branco" (ou seja, retornos de carro e tabulações) no layout das fórmulas em um formato intuitivo e de fácil leitura — especialmente quando as fórmulas são longas e complexas. Para inserir um retorno de carro, pressione Shift+Enter. O "espaço em branco" é opcional.

  3. No painel Campos, observe que o ícone de tabela tem um tom de azul (indicando uma tabela calculada).

    o ícone de tabela tem um tom de azul

    As tabelas calculadas são definidas usando uma fórmula DAX que retorna uma tabela. É importante entender que as tabelas calculadas aumentam o tamanho do modelo de dados, pois materializam e armazenam valores. Elas são recalculadas sempre que as dependências de fórmulas são atualizadas, como será o caso neste modelo de dados quando novos valores de data (futuros) forem carregados nas tabelas.

    Ao contrário das tabelas provenientes do Power Query, as tabelas calculadas não podem ser usadas para carregar dados de fontes de dados externas. Elas só podem transformar dados com base no que já foi carregado no modelo de dados.

  4. Alterne para a exibição Modelo.

  5. Observe que a tabela Vendedor está disponível (tome cuidado, ela pode estar oculta na exibição – role horizontalmente para localizá-la).

  6. Crie um relacionamento da coluna Vendedor | ChaveFuncionário com a coluna Vendas | ChaveFuncionário.

  7. Clique com o botão direito do mouse no relacionamento inativo entre as tabelas Vendedor (Desempenho) e Vendas e selecione Excluir.

    Excluir um relacionamento inativo

  8. Quando você for solicitado a confirmar a exclusão, clique em Excluir.

    confirmar a exclusão

  9. Na tabela Vendedor, faça a multisseleção das seguintes colunas e oculte-as:

    • IDFuncionário

    • ChaveFuncionário

    • UPN

  10. No diagrama, selecione a tabela Vendedor.

  11. No painel Propriedades, na caixa Descrição, digite: Vendedor relacionado às vendas

    Lembre-se de que a descrição aparece como dicas de ferramentas no painel Campos quando o usuário passa o cursor sobre uma tabela ou um campo.

  12. Para a tabela Vendedor (Desempenho), defina a descrição como: Vendedor relacionado às regiões.

    Agora o modelo de dados fornece alternativas durante a análise de vendedores. A tabela Vendedor permite analisar as vendas feitas por um vendedor, enquanto a tabela Vendedor (Desempenho) permite analisar as vendas realizadas nas regiões de vendas atribuídas ao vendedor.

Tarefa 2: criar a tabela Data

Nesta tarefa, você criará a tabela Data.

  1. Alterne para a exibição Dados.

    alternar para a exibição Dados

  2. Na guia da faixa de opções Página Inicial, dentro do grupo Cálculos, clique em Nova Tabela.

    Nova Tabela

  3. Na barra de fórmulas, digite o seguinte e pressione Enter:

    Date =  
    ‎CALENDARAUTO(6)
    

    digitar a fórmula

    A função CALENDARAUTO() retorna uma tabela de coluna única composta pelos valores de datas. O comportamento "automático" examina todas as colunas de data do modelo de dados para determinar os valores de data mais antigos e mais recentes armazenados no modelo de dados. Em seguida, cria uma linha para cada data dentro desse intervalo, estendendo o intervalo em qualquer direção para garantir o armazenamento de anos completos de dados.

    Essa função pode usar um único argumento opcional, que é o número do último mês de um ano. Quando omitido, o valor é 12, o que significa que dezembro é o último mês do ano. Nesse caso, é inserido 6, o que significa que junho é o último mês do ano.

  4. Observe a coluna de valores de data.

    Se a coluna não aparecer, no painel Campos, selecione uma tabela diferente e selecione a tabela Data.

    selecionar tabela Data

    As datas mostradas são formatadas usando as configurações regionais dos EUA (ou seja, mm/dd/aaaa).

  5. No canto inferior esquerdo, na barra de status, observe as estatísticas de tabela, confirmando que foram geradas 1.826 linhas de dados, o que representa cinco anos completos de dados.

    1.826 linhas

Tarefa 3: criar colunas calculadas

Nesta tarefa, você adicionará mais colunas para habilitar a filtragem e o agrupamento por períodos diferentes. Você também criará uma coluna calculada para controlar a ordem de classificação de outras colunas.

  1. Na faixa de opções contextual Ferramentas da Tabela, dentro do grupo Cálculos, clique em Nova Coluna.

    Nova Coluna

  2. Na barra de fórmulas, digite o seguinte e pressione Enter:

    Year =
    
    "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)
    

    Uma coluna calculada é criada inserindo primeiro o nome da coluna, seguido pelo símbolo de igual (=), seguido por uma fórmula DAX que retorna um resultado de valor único. O nome da coluna não pode existir na tabela.

    A fórmula usa o valor de ano da data, mas adiciona um ao valor de ano quando o mês é posterior a junho. É assim que são calculados os anos fiscais do Adventure Works.

  3. Verifique se a nova coluna foi adicionada.

    verificar se a coluna foi adicionada

  4. Use as definições de arquivo de snippets para criar estas duas colunas calculadas para a tabela Data:

    • Trimestre
    • Mês

    colunas de trimestre e mês

  5. Para validar os cálculos, alterne para a exibição Relatório.

  6. Para criar uma página de relatório, na parte inferior esquerda, clique no ícone de adição.

    criar página de relatório

  7. Para adicionar um visual da matriz à nova página de relatório, no painel Visualizações, selecione o tipo de visual da matriz.

    Dica

    Você pode passar o cursor sobre cada ícone para revelar uma dica de ferramenta que descreve o tipo de visual.

    descrição da dica de ferramenta

  8. No painel Campos, dentro da tabela Data, arraste o campo Ano para a caixa Linhas.

    arrastar ano para linhas

  9. Arraste o campo Mês até a caixa Linhas, diretamente abaixo do campo Ano.

    arrastar mês para linhas

  10. Ao lado do visual da matriz, clique no ícone de seta dupla bifurcada (que expandirá todos os anos em um nível).

    clicar na seta dupla bifurcada

  11. Observe que os anos se expandem para meses e que os meses são classificados em ordem alfabética, em vez de cronologicamente.

    meses classificados em ordem alfabética

    Por padrão, os valores de texto são classificados em ordem alfabética, os números são classificados do menor para o maior, e as datas são classificadas da mais antiga para a mais recente.

  12. Para personalizar a ordem de classificação do campo Mês, alterne para a exibição Dados.

  13. Adicione a coluna ChaveMês à tabela Data.

    MonthKey =
    
    (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])
    
    This formula computes a numeric value for each year/month combination.
    
    
  14. In Data view, verify that the new column contains numeric values (e.g. 201707 for July 2017, etc.).

    column contains numeric values

  15. In the Fields pane, ensure that the Month field is selected (when selected, it will have a dark gray background).

  16. On the Column Tools contextual ribbon, from inside the Sort group, click Sort by Column, and then select MonthKey.

    select MonthKey

  17. In the matrix visual, notice that the months are now chronologically sorted.

    months chronologically sorted

Task 4: Complete the Date table

In this task, you will complete the design of the Date table by hiding a column and creating a hierarchy. You will then create relationships to the Sales and Targets tables.

  1. Switch to Model view.

  2. In the Date table, hide the MonthKey column.

  3. In the Date table, create a hierarchy named Fiscal, with the following three levels:

    • Year
    • Quarter
    • Month

    year, quarter, month levels

  4. Create the follow two model relationships:

    • Date | Date to Sales | OrderDate
    • Date | Date to Targets | TargetMonth
  5. Hide the following two columns:

    • Sales | OrderDate
    • Targets | TargetMonth

Task 5: Mark the Date table

In this task, you will mark the Date table as a date table.

  1. Switch to Report view.

  2. In the Fields pane, select the Date table (not field).

  3. On the Table Tools contextual ribbon, from inside the Calendars group, click Mark as Date Table, and then select Mark as Date Table.

    select Mark as Data Table

  4. In the Mark as Date Table window, in the Date Column dropdown list, select Date.

    select Date

  5. Click OK.

    Click OK

  6. Save the Power BI Desktop file.

    Power BI Desktop now understands that this table defines date (time). This is important when relying on time intelligence calculations. You will work with time intelligence calculations in Lab 06B.

    Observação

    This design approach for a date table is suitable when you don’t have a date table in your data source. If you have access to a data warehouse, it would be appropriate to load date data from its date dimension table rather than “redefining” date logic in your data model.

Exercise 2: Create Measures

In this exercise, you will create and format several measures.

Task 1: Create simple measures

In this task, you will create simple measures. Simple measures aggregate a single column or table.

  1. In Report view, on Page 2, in the Fields pane, drag the Sales | Unit Price field into the Values section in matrix visual.

    drag Sales | Unit Price to matrix visual

    Recall that in Lab 05A, you set the Unit Price column to summarize by Average. The result you see in the matrix visual is the monthly average unit price.

  2. In the visual fields pane (located beneath the Visualizations pane), in the Values well, notice that Unit Price is listed.

    Unit Price is listed

  3. Click the down-arrow for Unit Price, and then notice the available menu options.

    available menu options

    Visible numeric columns allow report authors to decide at report design time how a column will summarize (or not). This can result in inappropriate reporting. Some data modelers do not like leaving things to chance, however, and choose to hide these columns and instead expose aggregation logic defined by measures. This is the approach you will now take in this lab.

  4. To create a measure, in the Fields pane, right-click the Sales table, and then select New Measure.

    New Measure

  5. In the formula bar, add the following measure definition:

    Avg Price = AVERAGE(Sales[Unit Price])
    
  6. Adicione a medida Preço Médio à seção Valores no visual da matriz.

  7. Observe que ela produz o mesmo resultado que a coluna Preço Unitário (mas com formatação diferente).

  8. Na caixa Valores, abra o menu de contexto do campo Preço Médio e observe que não é possível alterar a técnica de agregação.

    não é possível alterar a técnica de agregação

  9. Use as definições de arquivo de snippets para criar as cinco medidas a seguir para a tabela Vendas:

    • Preço Mediano
    • Preço Mín.
    • Preço Máx.
    • Pedidos
    • Linhas de Pedidos

    A função DISTINCTCOUNT() usada na medida Pedidos contará os pedidos apenas uma vez (ignorando duplicatas). A função COUNTROWS() usada na medida Linhas de Pedidos funciona em uma tabela. Nesse caso, o número de pedidos é calculado contando os valores distintos da coluna NúmeroPedidosVendas, enquanto o número de linhas do pedido é simplesmente o número de linhas da tabela (cada linha é uma linha de um pedido).

  10. Alterne para a exibição Modelo e selecione as quatro medidas de preço: Preço Médio, Preço Máximo, Preço Mediano e Preço Mínimo.

  11. Para a seleção de várias medidas, configure os seguintes requisitos:

    • Definir o formato para duas casas decimais
    • Atribuir uma pasta de exibição chamada Preços

    Pasta Preços

  12. Oculte a coluna Preço Unitário.

    Agora, a coluna Preço Unitário não está disponível para autores de relatório. Eles devem usar a medida que você adicionou ao modelo. Essa abordagem de design garante que os autores de relatório não agreguem preços de forma inadequada, por exemplo, somando-os.

  13. Faça a seleção múltipla de medidas de Pedidos e Linhas de pedidos e configure os seguintes requisitos:

    • Definir o formato para usar o separador de milhar
    • Atribuir a uma pasta de exibição chamada Contagens

    Pasta Contagens

  14. Na exibição Relatório, na caixa Valores do visual da matriz, clique no X do campo Preço Unitário para removê-lo.

    remover campo Preço Unitário

  15. Aumente o tamanho do visual da matriz para preencher a largura e a altura da página.

  16. Adicione estas cinco novas medidas ao visual da matriz:

    • Preço Mediano
    • Preço Mín.
    • Preço Máx.
    • Pedidos
    • Linhas de Pedidos
  17. Verifique se os resultados parecem razoáveis e se estão formatados corretamente.

    verificar resultados

Tarefa 2: criar medidas adicionais

Nesta tarefa, você criará medidas adicionais que usam expressões mais complexas.

  1. Na exibição Relatório, selecione Página 1.

    selecionar Página 1

  2. Examine o visual da tabela, observando o total da coluna Destino.

    Total de destino

    Somar os valores de destino não faz sentido, pois esses valores são definidos para cada vendedor com base em suas atribuições da região de vendas. Um valor de destino só deve ser mostrado quando um único vendedor é filtrado. Agora, você implementará uma medida para fazer exatamente isso.

  3. No visual da tabela, remova o campo Destino.

    remover campo Destino

  4. Renomear a coluna Destinos | Destino como Destinos | ValorDestino.

    Dica

    Há várias maneiras de renomear a coluna na exibição Relatório: No painel Campos, você pode clicar com o botão direito do mouse na coluna e, em seguida, selecionar Renomear – ou clicar duas vezes na coluna ou pressionar F2.

    Você está prestes a criar uma medida chamada Destino. Não é possível ter uma coluna e uma medida na mesma tabela com o mesmo nome.

  5. Crie a seguinte medida na tabela Destinos:

    Target =
    
    IF(
    
    HASONEVALUE('Salesperson (Performance)'[Salesperson]),
    
    SUM(Targets[TargetAmount])
    
    )
    

    A função HASONEVALUE() testa se é filtrado um único valor na coluna Vendedor. Se ela for true, a expressão retornará a soma dos valores de destino (apenas para esse vendedor). Se for false, será retornado EM BRANCO.

  6. Formate a medida de Destino para zero casas decimais.

    Dica

    Você pode usar a faixa de opções contextual Ferramentas de Medida.

  7. Oculte a coluna ValorDestino.

  8. Adicione a medida Destino ao visual da tabela.

  9. Observe que o total da coluna Destino está EM BRANCO agora.

    O total de destino está em branco

  10. Use as definições de arquivo de snippets para criar as duas medidas a seguir para a tabela s:

    • Variância
    • Margem de variância
  11. Formate a medida de Variância para zero casas decimais.

  12. Formate a medida Margem de variância como uma porcentagem com duas casas decimais.

  13. Adicione as medidas Variância e Margem de variância ao visual da tabela.

  14. Amplie o visual da tabela para que todos os valores sejam exibidos.

    todos os valores são exibidos

    Embora pareça que todos os vendedores não estão atingindo o destino, lembre-se de que as medidas ainda não foram filtradas por um período específico. Você produzirá relatórios de desempenho de vendas que são filtrados por um período selecionado pelo usuário no Lab 07A.

  15. No canto superior direito do painel Campos, recolha e então expanda o painel.

    expandir o painel Campos

    Recolher e reabrir o painel redefine o conteúdo.

  16. Observe que a tabela Destinos agora aparece na parte superior da lista.

    Tabela Destinos na parte superior

    As tabelas que compõem apenas as medidas visíveis são listadas automaticamente na parte superior da lista.

Conclusão

Nesta tarefa, você concluirá o laboratório.

  1. Salve o arquivo do Power BI Desktop.

  2. Deixe o Power BI Desktop aberto.

No próximo laboratório, você aprimorará o modelo de dados com cálculos mais avançados usando DAX.